Clever Geek Handbook

Get the cumulative number of records for a day

I am trying to get the cumulative sum of records over a period of time. Below is an example 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 the number of records until the day changes in another column. My desired result 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 you have any ideas on how to get the cumulative column?

+3


source to share


3 answers


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
+4


source


You can do it with a window SUM()

:



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


source


For older version, use CROSS APPLY

or correlated subquery

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)
+2


source






More articles:


All Articles