Cumulative totals with a condition

I am trying to get the cumulative totals based on some criteria.below is a dummy sample data set. in this, i would like to get the cumulative time based on the indicator as well as ID. when the indicator is continuously 1 for the same ID, I would like to get the sum of all the duration. If it becomes 0 then i would like to restart. Can someone help me with this? TIA

ID Duration Indicator Cumm_duration
1 30 0 30
1 30 1 60
1 30 0 30
1 30 0 30
1 30 1 60
1 30 0 30
1 30 0 30
1 30 0 30
1 30 0 30
1 30 0 30
1 30 0 30
1 30 0 30
1 30 1 60
1 30 1 90
2 30 1 30
2 30 0 30
2 30 0 30
2 30 0 30
2 30 1 60
2 30 0 30
2 30 1 60
2 30 0 30
2 30 0 30
2 30 0 30
2 30 1 60
2 30 1 90
2 30 0 30

June 20th, 2015 2:11pm

I am assuming that you are using SQL 2012. If that's the case, you can use windowing functions (http://nakulvachhrajani.com/2014/04/03/0330-sql-server-2012-running-total-problem-and-windowing-functions/)
Free Windows Admin Tool Kit Click here and download it now
June 21st, 2015 7:08am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics