Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to get zero values for a row


How to get zero values for a row

Author
Message
Kazmerelda
Kazmerelda
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 766
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!
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3001 Visits: 4994
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)
      Wink 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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search