clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591659 fiddles created (45713 in the last week).

CREATE TABLE dbo.t ( id integer NOT NULL, [date] date NOT NULL, frequency integer NOT NULL, PRIMARY KEY (id, [date]) ); INSERT dbo.t (id, [date], frequency) VALUES (1, '2012-04-30', 5), (1, '2012-06-30', 4), (1, '2013-07-31', 25), (2, '2012-04-30', 7), (2, '2012-05-31', 4), (2, '2012-06-30', 1), (2, '2012-07-31', 6);
7 rows affected
 hidden batch(es)


SELECT T1.id, Expanded.[date], Expanded.frequency FROM ( -- Add next date for the current id SELECT T.*, next_date = LEAD(T.[date], 1) OVER ( PARTITION BY T.id ORDER BY T.[date]) FROM dbo.t AS T ) AS T1 CROSS APPLY ( -- All month ends >= the current date and < next date SELECT [date] = EOMONTH(T1.[date], SV.number), frequency = IIF(SV.number = 0, T1.frequency, 0), SV.number FROM master.dbo.spt_values AS SV WHERE SV.[type] = N'P' AND SV.number < ISNULL(DATEDIFF(MONTH, T1.[date], T1.next_date), 1) ) AS Expanded ORDER BY T1.id, T1.[date], Expanded.number;
id date frequency
1 2012-04-30 5
1 2012-05-31 0
1 2012-06-30 4
1 2012-07-31 0
1 2012-08-31 0
1 2012-09-30 0
1 2012-10-31 0
1 2012-11-30 0
1 2012-12-31 0
1 2013-01-31 0
1 2013-02-28 0
1 2013-03-31 0
1 2013-04-30 0
1 2013-05-31 0
1 2013-06-30 0
1 2013-07-31 25
2 2012-04-30 7
2 2012-05-31 4
2 2012-06-30 1
2 2012-07-31 6
 hidden batch(es)