Need help with "SUM-IF" query - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Need help with "SUM-IF" query

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?

Sign In or Register to comment.