April 19, 2013 at 3:31 am
I have done some and tried to use the ISNULL command but this is not working.
My query is this:
select count (*) as 'Total', datename (mm, date_time) as 'Month Name',MONTH (date_time) as 'Month' ,Year (date_time) as 'Year'
from opencall
left join updatedb
on updatedb.callref = opencall.callref
where
(updatetxt like 'Call assigned to the WIBS group%')
and year(date_time) in ('2013')
and priority not in ('UNIX')
and probcode like ('CL%')
group by datename (mm, date_time), Year (date_time), MONTH (date_time)
order by Year, MONTH
I did try using the ISNULL command as I said, but this did not return zero rows.
It's been a while since I wrote reports, I think my mind has gone blank. Any help is appreciated!
April 19, 2013 at 4:06 am
You can create a table variable or a temporary table holding all the months and then do a LEFT OUTER JOIN with this table
DECLARE@tblCalendar TABLE
(
Month_NameVARCHAR(20),
Month_NumTINYINT,
Year_NumSMALLINT
)
INSERT@tblCalendar
SELECT'January', 1, 2013 UNION ALL
SELECT'February', 2, 2013 UNION ALL
SELECT'March', 3, 2013 UNION ALL
SELECT'April', 4, 2013 UNION ALL
SELECT'May', 5, 2013 UNION ALL
SELECT'June', 6, 2013 UNION ALL
SELECT'July', 7, 2013 UNION ALL
SELECT'August', 8, 2013 UNION ALL
SELECT'September', 9, 2013 UNION ALL
SELECT'October', 10, 2013 UNION ALL
SELECT'November', 11, 2013 UNION ALL
SELECT'December', 12, 2013
SELECTCOUNT(1) AS Total, tc.Month_Name AS [Month Name], tc.Month_Num AS [Month], tc.Year_Num AS [Year]
FROM@tblCalendar AS tc
LEFT OUTER JOIN (
select count (*) as 'Total', datename (mm, date_time) as 'Month Name',MONTH (date_time) as 'Month' ,Year (date_time) as 'Year'
from opencall
left join updatedb
on updatedb.callref = opencall.callref
where
(updatetxt like 'Call assigned to the WIBS group%')
and year(date_time) in ('2013')
and priority not in ('UNIX')
and probcode like ('CL%')
group by datename (mm, date_time), Year (date_time), MONTH (date_time)
) AS C
ONtc.Month_Name = C.[Month Name]
ANDtc.Month_Num = C.[Month]
ANDtc.Year_Num = C.[Year]
ORDER BY Year_Num, Month_Num
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply