Prev: Trying to setup Trigger to Update Each record
Next: Field with 6 data points seperated by ~
From: jcollado on 19 Jan 2010 13:06
Hello! I need some help with a Query to solve this requirement:

We have one table (Values) containing a list of values with its timestamps,
and a second table (Flags) containing flag-values with its timestamps. I need
to get a SUM of those values in Values table for which the flag value
satisfies for the timestamp of the value. The flag-value satisfies if, given
a certaing timestamp, the last known value for the flag is 1.

Exaple:
Values table:
RowId Value TimeStamp
1 5 2010-01-11 10:00:00
2 3 2010-01-11 10:30:00
3 6 2010-01-11 12:20:00
4 7 2010-01-11 14:15:00
5 5 2010-01-11 15:00:00
6 1 2010-01-11 18:30:00

Flags table:
RowId Value TimeStamp
1 0 2010-01-11 09:00:00
2 1 2010-01-11 10:10:00
3 1 2010-01-11 12:15:00
4 0 2010-01-11 14:45:00
5 0 2010-01-11 15:30:00
6 1 2010-01-11 18:00:00

If we take a look at the first row in Values table (rowId, value, timeStamp)
is (1, 5, 2010-01-11 10:00:00)
The timestamp is 10AM.
If we take a look at Flags table for 10AM-timestamp, the last-known flag-
value was 0: the row with timeStamp <= to 10AM is row (1, 0, 2010-01-11 09:00:
00), the flag is Off (0), so the value "does not satisfy".

Lets go for the second row in Values table (2, 3, 2010-01-11 10:30:00). If we
get the last-known flag-value in the Flags table for this timestamp
(timestamp <= 10.30AM), we get the second row (2, 1, 2010-01-11 10:10:00).
Now the flag-value is 1 (ON), and that means the value "does satisfy" and
must be taken into the sum.

So the expected result is the SUM of the values in Values table for the
rowIds 2, 3, 4 and 6: that is 3+6+7+1 = 17

Any suggestions on how to filter the rows in Values table according to
"active flag timestamp" in Flags table?

Thanks a lot in advance!

From: Plamen Ratchev on 19 Jan 2010 13:27
Here is one solution:

SELECT SUM(CASE WHEN F.Value = 1 THEN V.Value ELSE 0 END) AS total_value
FROM [Values] AS V
JOIN Flags AS F
ON V.[TimeStamp] >= F.[TimeStamp]
AND NOT EXISTS(SELECT *
FROM Flags AS F2
WHERE F2.[TimeStamp] > F.[TimeStamp]
AND F2.[TimeStamp] <= V.[TimeStamp]);

--
Plamen Ratchev
http://www.SQLStudio.com
From: jcollado via SQLMonster.com on 21 Jan 2010 11:20
Plamen,
Thanks a lot, that works just fine, and I think it is quite intuitive and
easy to understand.

--
Message posted via http://www.sqlmonster.com

 | 
Pages: 1
Prev: Trying to setup Trigger to Update Each record
Next: Field with 6 data points seperated by ~