Running Sum based on two dates in SQL-CodePudding

I have a table which has three columns shown in the picture.

Ord_dt - Date when the order was placed.

first_order- Date when the first order was placed.(Calculated based on last 52 weeks)

cnt_orders - Total Orders placed on Order Date.

ORD_DT  first_order cnt_orders
6/19/2020   6/19/2020   2
6/22/2020   6/19/2020   1
10/8/2020   6/19/2020   2
11/20/2020  6/19/2020   1
12/1/2020   6/19/2020   1
2/4/2021    6/19/2020   1
2/12/2021   6/19/2020   1
3/7/2021    6/19/2020   1
3/30/2021   6/19/2020   1
4/7/2021    6/19/2020   1
4/30/2021   6/19/2020   1
5/11/2021   6/19/2020   1
5/31/2021   6/19/2020   2
7/28/2021   10/8/2020   2

The Final Output should be something like this based on First_order Date. Running_Sum column is a running sum of cnt_orders based on first_order.In the below example row 3 Ord_dt = first_order in row 14, so it should do a sum of all orders for row14 from row3 to row 14.

ORD_DT  first_order cnt_orders  Running_sum
6/19/2020   6/19/2020   2          2
6/22/2020   6/19/2020   1          3
10/8/2020   6/19/2020   2          5
11/20/2020  6/19/2020   1          6
12/1/2020   6/19/2020   1          7
2/4/2021    6/19/2020   1          8
2/12/2021   6/19/2020   1          9
3/7/2021    6/19/2020   1          10
3/30/2021   6/19/2020   1          11
4/7/2021    6/19/2020   1          12
4/30/2021   6/19/2020   1          13
5/11/2021   6/19/2020   1          14
5/31/2021   6/19/2020   2          16
7/28/2021   10/8/2020   2          15

I have tried with SUM and Partition but it doesn't give me the correct last row data since first_order has been changed. It should give me 15 instead of 18.

How can I achieve this in SQL Server?

Sample Table which required Running Sum

CodePudding user response：

create table t
(ORD_DT date,  first_order date, cnt_orders int);
go
insert into t values
('6/19/2020' ,  '6/19/2020'  , 2),
('6/22/2020' ,  '6/19/2020'  , 1),
('10/8/2020' ,  '6/19/2020'  , 2),
('11/20/2020',  '6/19/2020'  , 1),
('12/1/2020' ,  '6/19/2020'  , 1),
('2/4/2021'  ,  '6/19/2020'  , 1),
('2/12/2021' ,  '6/19/2020'  , 1),
('3/7/2021'  ,  '6/19/2020'  , 1),
('3/30/2021' ,  '6/19/2020'  , 1),
('4/7/2021'  ,  '6/19/2020'  , 1),
('4/30/2021' ,  '6/19/2020'  , 1),
('5/11/2021' ,  '6/19/2020'  , 1),
('5/31/2021' ,  '6/19/2020'  , 2),
('7/28/2021' ,  '10/8/2020'  , 2);

go


select * ,
    (select sum(t1.cnt_orders) 
    from t t1 
    where  t1.ord_dt >= t.first_order and
            t1.ord_dt <= t.ORD_DT
    ) cumsum
from t 
order by ord_dt;

ORD_DT     first_order cnt_orders  cumsum
---------- ----------- ----------- -----------
2020-06-19 2020-06-19  2           2
2020-06-22 2020-06-19  1           3
2020-10-08 2020-06-19  2           5
2020-11-20 2020-06-19  1           6
2020-12-01 2020-06-19  1           7
2021-02-04 2020-06-19  1           8
2021-02-12 2020-06-19  1           9
2021-03-07 2020-06-19  1           10
2021-03-30 2020-06-19  1           11
2021-04-07 2020-06-19  1           12
2021-04-30 2020-06-19  1           13
2021-05-11 2020-06-19  1           14
2021-05-31 2020-06-19  2           16
2021-07-28 2020-10-08  2           15

(14 row(s) affected)