Howdy, Stranger!

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

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.