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?
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.