Adding "Filler" Months to a View

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding "Filler" Months to a View

Author

Topic

cableca
Starting Member

6 Posts

Posted - 2008-02-27 : 14:46:38

How can I add "filler" months to a query? I have a view that has data for January but I want the report that is linked via a pivot table to show february-december with blank data. so the report has a full 12month view.

I hope this makes sense. any help would be greatly appreciated.

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-27 : 14:50:14

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-02-27 : 21:18:40

You need to create a sub query of your date range and outer join your query to it.

select * from
(
select 1 m union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12
) months
left outer join
(
"Your Query goes here"
) x on
months.m=
datepart(m, monthInYourQuery)
order by m

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources