By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t1(
d date
, m int
);
INSERT INTO
t1(d, m)
VALUES
('2020-09-01', 100)
, ('2020-09-02', 100)
, ('2020-10-01', 500)
, ('2020-12-01', 1000)
DECLARE @t TABLE(
StartDate DATETIME
, EndDate DATETIME
);
INSERT INTO @t
(StartDate, EndDate)
VALUES
('2020/01/01', '2021/01/01');
;WITH CTE (Dates,EndDate) AS
(
SELECT
StartDate Dates
, EndDate EndDate
FROM
@t
UNION ALL
SELECT
DATEADD(MONTH, 1, Dates)
, EndDate
FROM
CTE
| Month | Total |
|---|---|
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 0 |
| 8 | 0 |
| 9 | 200 |
| 10 | 500 |
| 11 | 0 |
| 12 | 1000 |
Warning: Null value is eliminated by an aggregate or other SET operation.