sqlt-sqlsql-server-2008

How to fill all the months of the year in order using TSQL


I have the following table:

id   month   cost
------------------
1    Jan      200
1    Mar      204
1    May      200
1    Dec      201

I need an output like (order by month including the other months of a year-displaying all 12 months):

to   month   cost
------------------
1    Jan      200
NULL Feb      NULL
1    Mar      204
....
....
....
1    Dec      201

any idea or solution how to do this in TSQL?

edit:: month is extracted from a datetime value. in real world I'll have to show previous 12 months from last month in a DESC order! Any suggestion for that?


Solution

  • How about this? The result contains month and year, but you can strip it as you want.

    ;with months
    as
    (
    select dateadd(month, -1, dateadd(day, datediff(day, 0, getdate()), 0)) as m
    union all
    select dateadd(month, -1, m)
    from months
    where   m > dateadd(month, -12, getdate())
    )
    
    -- Testdata
    ,yourTable(id,somedate,cost)
    as
    (
        select 1, '2011-01-03', 200
        union all
        select 1, '2011-03-06', 204
        union all
        select 1, '2010-05-09', 200
        union all
        select 1, '2010-05-19', 201
        union all
        select 1, '2010-12-02', 201
    )
    -- end testdata
    
    select  yt.id
            ,datename(month,coalesce(yt.somedate, m.m)) as [month]
            ,datename(year,coalesce(yt.somedate, m.m)) as [year]
            --,yt.cost
            ,sum(yt.cost) as cost
    from    months m
    left join yourTable yt
        on  datepart(year, yt.someDate) = DATEPART(year, m.m)
        and datepart(month, yt.someDate) = DATEPART(month, m.m)
    group by
        yt.id
        ,datename(month,coalesce(yt.somedate, m.m))
        ,datename(year,coalesce(yt.somedate, m.m))
        ,m.m
    order by m.m desc
    

    Edit: Altered solution to support sum. Remove the group by-section and alter the comment of cost, to get the old solution.