MSSQL Progressive accumulation function

1. For example, build initial rainfall

2. In order to sort the rainfall, most of the operations can be omitted, with no subsequent rank being included

3. Observation, if we want to accumulate, in fact, the same day plus this day before the sum of rainfall, so you can write the following

Enclose the SQL code as follows:

SELECTRow_number () Over(ORDER  byTime) rank,*   into#t from(SELECT '2016-08-28'Time $RainfallUNION  All        SELECT '2016-08-29'Time0RainfallUNION  All        SELECT '2016-08-30'Time -RainfallUNION  All        SELECT '2016-08-31'Time theRainfallUNION  All        SELECT '2016-09-01'Time -RainfallUNION  All        SELECT '2016-09-02'Time0RainfallUNION  All        SELECT '2016-09-03'Time therainfall) aSELECTA1. Time, A1. Rainfall,sum(A2. Rainfall) Cumulative rainfall from#t A1INNER JOIN#t A2 onA2.rank<=A1.rankGROUP  byA1. Time, A1. RainfallORDER  byA1. TimeDROP TABLE#t

4. It is also possible to use the date as a condition without using row_number () and the effect is the same as 3:

Code:

SELECT *   into#t from(SELECT '2016-08-28'Time $RainfallUNION  All        SELECT '2016-08-29'Time0RainfallUNION  All        SELECT '2016-08-30'Time -RainfallUNION  All        SELECT '2016-08-31'Time theRainfallUNION  All        SELECT '2016-09-01'Time -RainfallUNION  All        SELECT '2016-09-02'Time0RainfallUNION  All        SELECT '2016-09-03'Time therainfall) aSELECTA1. Time, A1. Rainfall,sum(A2. Rainfall) Cumulative rainfall from#t A1INNER JOIN#t A2 onA2. Time<=A1. TimeGROUP  byA1. Time, A1. RainfallORDER  byA1. TimeDROP TABLE#t

Why 3 can find out what we want to look like, we just use the first three days to see

It can be observed that the record less than August 28 is August 28, less than August 29 is 28 and 292 days, we put these data together, we can easily A1 as the date of the group accumulation, and finally get the data we want.

MSSQL Progressive accumulation function