CODEWITHSUNDEEP
sqlsql-server
Zyx Sun
Zyx Sun

Reputation: 439

Check if a month is skipped then add values dynamically?

I have a set of data from a table that would only be populated if a user has data for a certain month just like this:

Month | MonthName | Value
3     | March     | 136.00
4     | April     | 306.00
7     | July      | 476.00
12    | December  | 510.48

But what I need is to check if a month is skipped then adding the value the month before so the end result would be like this:

Month | MonthName | Value
3     | March     | 136.00
4     | April     | 306.00
5     | May       | 306.00 -- added data
6     | June      | 306.00 -- added data
7     | July      | 476.00
8     | August    | 476.00 -- added data
9     | September | 476.00 -- added data
10    | October   | 476.00 -- added data
11    | November  | 476.00 -- added data
12    | December  | 510.48

How can I do this dynamically on SQL Server?

Upvotes: 1

Views: 213

Answers (2)

Wei Lin
Wei Lin

Reputation: 3811

you can use spt_values to get continuous number 1-12, and then left join your table by max(month)

select t1.month
    ,datename(month,datefromparts(2020, t1.month, 1)) monthname
    ,t2.value 
from (
    select top 12 number + 1 as month from master..spt_values 
    where type = 'p'
) t1
left join t t2 on t2.month = (select max(month) from t tmp where tmp.month < = t1.month)
where t2.month is not null

CREATE TABLE T
    ([Month] int, [MonthName] varchar(8), [Value] numeric)
;

INSERT INTO T
    ([Month], [MonthName], [Value])
VALUES
    (3, 'March', 136.00),
    (4, 'April', 306.00),
    (7, 'July', 476.00),
    (12, 'December', 510.48)
;

Demo Link SQL Server 2012 | db<>fiddle

note

if you have year column then you need to fix the script.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

One method is a recursive CTE:

with cte as (
      select month, value, lead(month) over (order by month) as next_month
      from t
      union all
      select month + 1, value, next_month
      from cte
      where month + 1 < next_month
     )
select month, datename(month, datefromparts(2020, month, 1)) as monthname, value
from cte
order by month;

Here is a db<>fiddle.

Upvotes: 3

Related Questions