SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TEST(
	AccountID char(5) ,
	Date date ,
	AMT int 
) 

insert into test
Values (N'1121 ', CAST(N'2018-01-02' AS Date), 500)
,(N'1121 ', CAST(N'2018-01-05' AS Date), 300)
,(N'1121 ', CAST(N'2018-02-02' AS Date), 200)
,(N'1121 ', CAST(N'2018-03-05' AS Date), 600)
,(N'1121 ', CAST(N'2018-03-11' AS Date), 300)
,(N'1121 ', CAST(N'2018-04-05' AS Date), 500)
,(N'1121 ', CAST(N'2018-04-09' AS Date), 600)
,(N'1121 ', CAST(N'2018-05-01' AS Date), 700)
,(N'1121 ', CAST(N'2018-06-02' AS Date), 800)
,(N'1121 ', CAST(N'2018-07-01' AS Date), 100)
,(N'1121 ', CAST(N'2018-08-02' AS Date), 200)
,(N'1121 ', CAST(N'2018-08-08' AS Date), 300)
,(N'1201 ', CAST(N'2018-01-06' AS Date), 500)
,(N'1201 ', CAST(N'2018-01-09' AS Date), 300)
,(N'1201 ', CAST(N'2018-02-06' AS Date), 200)
,(N'1201 ', CAST(N'2018-03-05' AS Date), 600)
,(N'1201 ', CAST(N'2018-05-05' AS Date), 300)
,(N'1201 ', CAST(N'2018-05-10' AS Date), 500)
,(N'1201 ', CAST(N'2018-06-11' AS Date), 600)
,(N'1201 ', CAST(N'2018-07-12' AS Date), 700)
,(N'1201 ', CAST(N'2018-07-18' AS Date), 800)
,(N'1201 ', CAST(N'2018-08-01' AS Date), 100)
,(N'1201 ', CAST(N'2018-08-06' AS Date), 200)
,(N'1201 ', CAST(N'2018-08-10' AS Date), 300)
,(N'1121 ', CAST(N'2019-01-06' AS Date), 100)
,(N'1121 ', CAST(N'2019-01-10' AS Date), 200)
,(N'1121 ', CAST(N'2019-02-08' AS Date), 500)
,(N'1121 ', CAST(N'2019-02-10' AS Date), 200)
,(N'1121 ', CAST(N'2019-03-12' AS Date), 300)
,(N'1121 ', CAST(N'2019-04-11' AS Date), 500)
,(N'1121 ', CAST(N'2019-05-10' AS Date), 300)
,(N'1121 ', CAST(N'2019-07-12' AS Date), 600)
,(N'1121 ', CAST(N'2019-08-01' AS Date), 700)
,(N'1121 ', CAST(N'2019-08-06' AS Date), 100)
,(N'1121 ', CAST(N'2019-08-12' AS Date), 200)
,(N'1121 ', CAST(N'2019-08-16' AS Date), 200)
,(N'1201 ', CAST(N'2019-01-05' AS Date), 200)
,(N'1201 ', CAST(N'2019-02-08' AS Date), 100)
,(N'1201 ', CAST(N'2019-03-11' AS Date), 300)
,(N'1201 ', CAST(N'2019-03-16' AS Date), 300)
,(N'1201 ', CAST(N'2019-05-12' AS Date), 500)
,(N'1201 ', CAST(N'2019-05-16' AS Date), 100)
,(N'1201 ', CAST(N'2019-06-12' AS Date), 500)
,(N'1201 ', CAST(N'2019-07-10' AS Date), 300)
,(N'1201 ', CAST(N'2019-07-12' AS Date), 600)
,(N'1201 ', CAST(N'2019-08-02' AS Date), 800)
,(N'1201 ', CAST(N'2019-08-12' AS Date), 600)
,(N'1201 ', CAST(N'2019-08-16' AS Date), 800)

48 rows affected

SELECT * FROM TEST


AccountID
Date
AMT


1121
02/01/2018 00:00:00
500

1121
05/01/2018 00:00:00
300

1121
02/02/2018 00:00:00
200

1121
05/03/2018 00:00:00
600

1121
11/03/2018 00:00:00
300

1121
05/04/2018 00:00:00
500

1121
09/04/2018 00:00:00
600

1121
01/05/2018 00:00:00
700

1121
02/06/2018 00:00:00
800

1121
01/07/2018 00:00:00
100

1121
02/08/2018 00:00:00
200

1121
08/08/2018 00:00:00
300

1201
06/01/2018 00:00:00
500

1201
09/01/2018 00:00:00
300

1201
06/02/2018 00:00:00
200

1201
05/03/2018 00:00:00
600

1201
05/05/2018 00:00:00
300

1201
10/05/2018 00:00:00
500

1201
11/06/2018 00:00:00
600

1201
12/07/2018 00:00:00
700

1201
18/07/2018 00:00:00
800

1201
01/08/2018 00:00:00
100

1201
06/08/2018 00:00:00
200

1201
10/08/2018 00:00:00
300

1121
06/01/2019 00:00:00
100

1121
10/01/2019 00:00:00
200

1121
08/02/2019 00:00:00
500

1121
10/02/2019 00:00:00
200

1121
12/03/2019 00:00:00
300

1121
11/04/2019 00:00:00
500

1121
10/05/2019 00:00:00
300

1121
12/07/2019 00:00:00
600

1121
01/08/2019 00:00:00
700

1121
06/08/2019 00:00:00
100

1121
12/08/2019 00:00:00
200

1121
16/08/2019 00:00:00
200

1201
05/01/2019 00:00:00
200

1201
08/02/2019 00:00:00
100

1201
11/03/2019 00:00:00
300

1201
16/03/2019 00:00:00
300

1201
12/05/2019 00:00:00
500

1201
16/05/2019 00:00:00
100

1201
12/06/2019 00:00:00
500

1201
10/07/2019 00:00:00
300

1201
12/07/2019 00:00:00
600

1201
02/08/2019 00:00:00
800

1201
12/08/2019 00:00:00
600

1201
16/08/2019 00:00:00
800


select accountid,YYYY=substring( CONVERT(nchar(8), DATE, 112) ,1,4),MM=substring( CONVERT(nchar(8), DATE, 112) ,5,2),
       AMT=sum(AMT)
INTO TEST2
from test
group by accountid,substring( CONVERT(nchar(8), DATE, 112) ,1,4),substring( CONVERT(nchar(8), DATE, 112) ,5,2)
order by 1,2




select T.*,
acc=SUM(AMT) OVER (PARTITION BY T.AccountID ,T.YYYY   ORDER BY T.AccountID ,T.YYYY,T.MM    ),
acc2=SUM(AMT2) OVER (PARTITION BY T.AccountID ,T.YYYY   ORDER BY T.AccountID ,T.YYYY,T.MM    )
into TEST3
from(
select a.*,AMT2=isnull(b.AMT,0)
from test2 a left join test2 b
on A.accountid=b.accountid and a.yyyy=b.yyyy+1 and a.mm=b.mm
) T


select * from test3
where YYYY=2019 and mm=08


accountid
YYYY
MM
AMT
AMT2
acc
acc2


1121
2019
08
1200
500
3900
4300

1201
2019
08
2200
600
5100
5100

AccountID	Date	AMT
1121	02/01/2018 00:00:00	500
1121	05/01/2018 00:00:00	300
1121	02/02/2018 00:00:00	200
1121	05/03/2018 00:00:00	600
1121	11/03/2018 00:00:00	300
1121	05/04/2018 00:00:00	500
1121	09/04/2018 00:00:00	600
1121	01/05/2018 00:00:00	700
1121	02/06/2018 00:00:00	800
1121	01/07/2018 00:00:00	100
1121	02/08/2018 00:00:00	200
1121	08/08/2018 00:00:00	300
1201	06/01/2018 00:00:00	500
1201	09/01/2018 00:00:00	300
1201	06/02/2018 00:00:00	200
1201	05/03/2018 00:00:00	600
1201	05/05/2018 00:00:00	300
1201	10/05/2018 00:00:00	500
1201	11/06/2018 00:00:00	600
1201	12/07/2018 00:00:00	700
1201	18/07/2018 00:00:00	800
1201	01/08/2018 00:00:00	100
1201	06/08/2018 00:00:00	200
1201	10/08/2018 00:00:00	300
1121	06/01/2019 00:00:00	100
1121	10/01/2019 00:00:00	200
1121	08/02/2019 00:00:00	500
1121	10/02/2019 00:00:00	200
1121	12/03/2019 00:00:00	300
1121	11/04/2019 00:00:00	500
1121	10/05/2019 00:00:00	300
1121	12/07/2019 00:00:00	600
1121	01/08/2019 00:00:00	700
1121	06/08/2019 00:00:00	100
1121	12/08/2019 00:00:00	200
1121	16/08/2019 00:00:00	200
1201	05/01/2019 00:00:00	200
1201	08/02/2019 00:00:00	100
1201	11/03/2019 00:00:00	300
1201	16/03/2019 00:00:00	300
1201	12/05/2019 00:00:00	500
1201	16/05/2019 00:00:00	100
1201	12/06/2019 00:00:00	500
1201	10/07/2019 00:00:00	300
1201	12/07/2019 00:00:00	600
1201	02/08/2019 00:00:00	800
1201	12/08/2019 00:00:00	600
1201	16/08/2019 00:00:00	800