Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get zero values for a row Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 3:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 86, Visits: 442
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!

Post #1444290
Posted Friday, April 19, 2013 4:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 7, 2014 3:30 AM
Points: 2,631, Visits: 4,722
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_Name VARCHAR(20),
Month_Num TINYINT,
Year_Num SMALLINT
)

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

SELECT COUNT(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
ON tc.Month_Name = C.[Month Name]
AND tc.Month_Num = C.[Month]
AND tc.Year_Num = C.[Year]
ORDER BY Year_Num, Month_Num




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1444298
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse