I was looking for similar problems but found nothing ...
I have a problem with joining two tables in SQL. The first table is created using the following procedure
DECLARE @Sequence TABLE (n DATETIME NOT NULL)
DECLARE @Index INT
SET @Index = 1
WHILE @Index <= 96 BEGIN
INSERT @Sequence (n) VALUES (DATEADD(Minute, @Index * 5, DATEADD(Hour, -8, '06-25-2010 00:00:00')))
SET @Index = @Index + 1
END
And when I run a regular query like:
SELECT
Sequence.n
FROM
@Sequence as Sequence
I get what I expect - 96 rows with DateTime values located at 5 minute intervals ending 06-25-2010 00:00:00 (this value will be replaced later in the SSRS report, so it may seem strange to indicate "end "range and use double DATEADD).
Now the second table contains the values registered by the PLMC controllers, and they also register one record in 5 minutes (for each point identifier, but not complicating it, we can assume that there is one PointID).
, , , , 0. , 8 , . , , :
SELECT
DataTime, DataValue
FROM
PointValue
WHERE
PointValue.DataTime > DATEADD(Hour, -8, '06-22-2010 00:00:00')
AND PointValue.DataTime < '06-22-2010 00:00:00'
AND PointID = '5284'
56 . , 20:30 .
, . , 5 , , , 96 (8 5 ) 20:30:
SELECT
Sequence.n,
PointValue.DataValue
FROM
@Sequence as Sequence LEFT OUTER JOIN PointValue
ON Sequence.n = PointValue.DataTime
WHERE
PointID = '5280'
, - 56 , , ... Null 3,5 . , , , .
:
, , Blorgbeard. , , x pointID ( ), "FROM" :
(SELECT Sequence.n, dbo.LABELS.theIndex FROM @TimeSequence as Sequence, dbo.LABELS
WHERE LEFT(dbo.LABELS.theLabel,2)='VA') as BaseTable
LEFT OUTER JOIN PointValue ON BaseTable.n = PointValue.DataTime AND BaseTable.theIndex = PointValue.PointID
!