June 30, 2008 at 3:36 am
Hi,
I'm having a problem with missing months in one of my queries;
select month(a.date) as month, year(a.date) as year, count(a.ID) as count, a.specification
from example as a
where a.date between (getdate() - 365) and getdate()
group by month(a.date), year(a.date), a.specification
order by year(a.date), a.specification, month(a.date)
If count(a.id) is 0 for any specific month, the script won't show that month. For example if there are no IDs that satisfies a.specification (in my case a.specification can be any one number between 1 and 4) for the month of January, then January will be left out.
Does anyone know a way of making my query show all months between the two specified dates, no matter if count > 0 or not?
Thanks,
Jonatan
June 30, 2008 at 6:08 am
Perhaps something like?
declare @example table
( id int,
specification int,
date datetime
)
insert into @example
select 1, 14, '1/1/8' union all
select 1, 14, '1/5/8' union all
select 1, 15, '2/1/8' union all
select 2, 17, '3/1/8'
--select * from @example
select
month(dateadd(mm, m.mm -13, getdate())) as month,
year(dateadd(mm, m.mm -13, getdate())) as year,
count (a.ID) as count,
a.specification
from
( select 1 as mm 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 union all
select 13
) m
left join @example a
on month(a.date) = month(dateadd(mm, m.mm -13, getdate()))
and year (a.date) = year(dateadd(mm, m.mm -13, getdate()))
group by
month(dateadd(mm, m.mm -13, getdate())),
year(dateadd(mm, m.mm -13, getdate())),
a.specification
order by
year(dateadd(mm, m.mm -13, getdate())),
a.specification,
month(dateadd(mm, m.mm -13, getdate()))
/* -------- Sample Output: --------
month year count specification
----------- ----------- ----------- -------------
6 2007 0 NULL
7 2007 0 NULL
8 2007 0 NULL
9 2007 0 NULL
10 2007 0 NULL
11 2007 0 NULL
12 2007 0 NULL
4 2008 0 NULL
5 2008 0 NULL
6 2008 0 NULL
1 2008 2 14
2 2008 1 15
3 2008 1 17
*/
June 30, 2008 at 7:34 am
Thanks a lot, that really helped. Now I do get another problem though. I'm using the query in Reporting Services for a chart and because the specification value is NULL in some rows I get a fifth (and empty) series in the chart, not sure if or how I can get rid of it.
Jonatan
June 30, 2008 at 9:56 am
Look at IsNull in Books Online. That'll give you the data on how to turn Null into something else.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply