Getting a cumulative number of records within a day

Question

I am trying to get a cumulative sum of records within a time period of a day. Below is a current sample of my data.

DT                      No_of_records
2017-05-01 00:00:00.000     241
2017-05-01 04:00:00.000     601
2017-05-01 08:00:00.000     207
2017-05-01 12:00:00.000     468
2017-05-01 16:00:00.000     110
2017-05-01 20:00:00.000     450
2017-05-02 00:00:00.000     151
2017-05-02 04:00:00.000     621
2017-05-02 08:00:00.000     179
2017-05-02 12:00:00.000     163
2017-05-02 16:00:00.000     579
2017-05-02 20:00:00.000     299

I am trying to sum up the number of records until the day changes in another column. My desired output is below.

DT                      No_of_records    cumulative
2017-05-01 00:00:00.000     241             241
2017-05-01 04:00:00.000     601             842
2017-05-01 08:00:00.000     207             1049
2017-05-01 12:00:00.000     468             1517 
2017-05-01 16:00:00.000     110             1627
2017-05-01 20:00:00.000     450             2077
2017-05-02 00:00:00.000     151             151
2017-05-02 04:00:00.000     621             772
2017-05-02 08:00:00.000     179             951
2017-05-02 12:00:00.000     163             1114
2017-05-02 16:00:00.000     579             1693
2017-05-02 20:00:00.000     299             1992

Do any of you have ideas on how to get the cumulative column?

Possible duplicate of [How to get cumulative sum](https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum) — Tanner, Aug 03 '17 at 14:51

score 4 · Accepted Answer · answered Aug 03 '17 at 14:39

4

If 2012+ you can use with window function sum() over

Select *
       ,cumulative = sum(No_of_records) over (Partition by cast(DT as date) Order by DT)
 From  YourTable

answered Aug 03 '17 at 14:39

John Cappelletti

79,615
7
44
66

@Jamie Happy it helped – John Cappelletti Aug 03 '17 at 14:53
@Jamie Well worth your time to get comfortable with the window functions. They can be INVALUABLE. – John Cappelletti Aug 03 '17 at 14:54

score 2 · Answer 2 · answered Aug 03 '17 at 14:40

2

You can do this with a windowed SUM():

Select  DT, No_of_records, 
        Sum(No_of_records) Over (Partition By Convert(Date, DT) Order By DT) As cumulative
From    YourTable

answered Aug 03 '17 at 14:40

Siyual

16,415
8
44
58

Pரதீப் · Answer 3 · 2017-08-03T14:51:30.150

2

For older version use CROSS APPLY or Correlated sub-query

SELECT DT,
       No_of_records,
       cs.cumulative
FROM   YourTable a
       CROSS apply(SELECT Sum(No_of_records)
                   FROM   YourTable b
                   WHERE  Cast(a.DT AS DATE) = Cast(b.DT AS DATE)
                          AND a.DT >= b.DT) cs (cumulative)

Rextester Demo

edited Aug 03 '17 at 14:51

answered Aug 03 '17 at 14:45

Pரதீப்

91,748
19
131
172

Getting a cumulative number of records within a day

3 Answers3