I have been trying to solve a problem for a few days now, but I just can't get it solved. Hence my question today. I would like to calculate the running sum in the following table. My result so far looks like this:
| PersonID | Visit_date | Medication_intake | Previous_date | Date_diff | Running_sum |
|---|---|---|---|---|---|
| 1 | 2012-04-26 | 1 | |||
| 1 | 2012-11-16 | 1 | 2012-04-26 | 204 | 204 |
| 1 | 2013-04-11 | 0 | |||
| 1 | 2013-07-19 | 1 | |||
| 1 | 2013-12-05 | 1 | 2013-07-19 | 139 | 343 |
| 1 | 2014-03-18 | 1 | 2013-12-05 | 103 | 585 |
| 1 | 2014-06-24 | 0 | |||
| 2 | 2014-12-01 | 1 | |||
| 2 | 2015-03-09 | 1 | 2014-12-01 | 98 | 98 |
| 2 | 2015-09-28 | 0 |
This is my desired result. So only the running sum over contiguous blocks (Medication_intake=1) should be calculated.
| PersonID | Visit_date | Medication_intake | Previous_date | Date_diff | Running_sum |
|---|---|---|---|---|---|
| 1 | 2012-04-26 | 1 | |||
| 1 | 2012-11-16 | 1 | 2012-04-26 | 204 | 204 |
| 1 | 2013-04-11 | 0 | |||
| 1 | 2013-07-19 | 1 | |||
| 1 | 2013-12-05 | 1 | 2013-07-19 | 139 | 139 |
| 1 | 2014-03-18 | 1 | 2013-12-05 | 103 | 242 |
| 1 | 2014-06-24 | 0 | |||
| 2 | 2014-12-01 | 1 | |||
| 2 | 2015-03-09 | 1 | 2014-12-01 | 98 | 98 |
| 2 | 2015-09-28 | 0 |
I work with Microsoft SQL Server 2019 Express.
Thank you very much for your tips!