SQL Server 2022

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

-- Create the calendar table, you only need to do this once
with  
   E1(N) As (Select 1 From (Values (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) DT(N)),
   E2(N) As (Select 1 From E1 A Cross Join E1 B),
   E4(N) As (Select 1 From E2 A Cross Join E2 B),
   E6(N) As (Select 1 From E4 A Cross Join E2 B),
   Tally(N) As
   (
        Select    Row_Number() Over (Order By (Select Null)) 
        From    E6
   )
Select   convert(date, DateAdd(Day, N - 1, '20210101')) as [Date],
         datepart(year, DateAdd(Day, N - 1, '20210101')) as Year,  
         datepart(month, DateAdd(Day, N - 1, '20210101')) as Month
into tblMonths   
From     Tally
Where    N <= DateDiff(Day, '20210101', getdate()) + 1

select top 10 * from tblMonths;



Date
Year
Month


2021-01-01
2021
1

2021-01-02
2021
1

2021-01-03
2021
1

2021-01-04
2021
1

2021-01-05
2021
1

2021-01-06
2021
1

2021-01-07
2021
1

2021-01-08
2021
1

2021-01-09
2021
1

2021-01-10
2021
1


-- creating a table with your sample data
create table MyTransactions ([Name] nvarchar(10),
    [Date] datetime2,
    [Debit] decimal,
    [Credit] decimal
 );
 
 INSERT INTO MyTransactions ([Name], [Date], [Debit], [Credit]) 
  VALUES 
   ('A','20210101', 10, 0), 
   ('A','20210101', 9, 0), 
   ('A','20210201', 11, 0),
   ('A','20210301', 0, 50), 
   ('A','20210401', 30, 0), 
   ('B','20210101', 10, 0),
   ('B','20220201', 0, 12), 
   ('B','20220301', 0, 50), 
   ('B','20240401', 3, 0);

select * from MyTransactions;


Name
Date
Debit
Credit


A
2021-01-01 00:00:00.0000000
10
0

A
2021-01-01 00:00:00.0000000
9
0

A
2021-02-01 00:00:00.0000000
11
0

A
2021-03-01 00:00:00.0000000
0
50

A
2021-04-01 00:00:00.0000000
30
0

B
2021-01-01 00:00:00.0000000
10
0

B
2022-02-01 00:00:00.0000000
0
12

B
2022-03-01 00:00:00.0000000
0
50

B
2024-04-01 00:00:00.0000000
3
0


-- This is the actual query
select t.[Name], 
       m.year, m.Month, 
       sum(sum(t.Credit) - sum(t.Debit)) over(partition by name order by m.year, m.month) Balance
from   ( select mm.Year, mm.Month, min(mm.[Date]) as [Date]
         from   tblMonths mm
         where  mm.[Date] >= '20210101' and mm.[Date] < '20240401'
         group by mm.Year, mm.Month
       ) m
  -- use the left join for gapless, and the inner join for having gaps
  left join MyTransactions t on m.[Date] = t.[Date]
  --inner join MyTransactions t on m.[Date] = t.[Date]
  
group by m.year, m.Month, t.[Name]
order by m.Year, m.Month


Name
year
Month
Balance


A
2021
1
-19

B
2021
1
-10

A
2021
2
-30

A
2021
3
20

A
2021
4
-10

null
2021
5
null

null
2021
6
null

null
2021
7
null

null
2021
8
null

null
2021
9
null

null
2021
10
null

null
2021
11
null

null
2021
12
null

null
2022
1
null

B
2022
2
2

B
2022
3
52

null
2022
4
null

null
2022
5
null

null
2022
6
null

null
2022
7
null

null
2022
8
null

null
2022
9
null

null
2022
10
null

null
2022
11
null

null
2022
12
null

null
2023
1
null

null
2023
2
null

null
2023
3
null

null
2023
4
null

null
2023
5
null

null
2023
6
null

null
2023
7
null

null
2023
8
null

null
2023
9
null

null
2023
10
null

null
2023
11
null

null
2023
12
null

null
2024
1
null

null
2024
2
null

null
2024
3
null


Warning: Null value is eliminated by an aggregate or other SET operation.


drop table tblMonths;
drop table MyTransactions;

Name	year	Month	Balance
A	2021	1	-19
B	2021	1	-10
A	2021	2	-30
A	2021	3	20
A	2021	4	-10
null	2021	5	null
null	2021	6	null
null	2021	7	null
null	2021	8	null
null	2021	9	null
null	2021	10	null
null	2021	11	null
null	2021	12	null
null	2022	1	null
B	2022	2	2
B	2022	3	52
null	2022	4	null
null	2022	5	null
null	2022	6	null
null	2022	7	null
null	2022	8	null
null	2022	9	null
null	2022	10	null
null	2022	11	null
null	2022	12	null
null	2023	1	null
null	2023	2	null
null	2023	3	null
null	2023	4	null
null	2023	5	null
null	2023	6	null
null	2023	7	null
null	2023	8	null
null	2023	9	null
null	2023	10	null
null	2023	11	null
null	2023	12	null
null	2024	1	null
null	2024	2	null
null	2024	3	null

Date	Year	Month
2021-01-01	2021	1
2021-01-02	2021	1
2021-01-03	2021	1
2021-01-04	2021	1
2021-01-05	2021	1
2021-01-06	2021	1
2021-01-07	2021	1
2021-01-08	2021	1
2021-01-09	2021	1
2021-01-10	2021	1

Name	Date	Debit	Credit
A	2021-01-01 00:00:00.0000000	10	0
A	2021-01-01 00:00:00.0000000	9	0
A	2021-02-01 00:00:00.0000000	11	0
A	2021-03-01 00:00:00.0000000	0	50
A	2021-04-01 00:00:00.0000000	30	0
B	2021-01-01 00:00:00.0000000	10	0
B	2022-02-01 00:00:00.0000000	0	12
B	2022-03-01 00:00:00.0000000	0	50
B	2024-04-01 00:00:00.0000000	3	0