Add 0 if count doesn't find anything in subselect

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

Add 0 if count doesn't find anything in subselect

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2010-01-20 : 17:08:46

I've got a query where I count the number of records per month for the last 90 days. A subquery returns all records for the last 90 days, and the query itself counts the results per month in the subquery. Currently, if there are no records in the subselect for a month in the past 90 days, the query doesn't return a row for that month. I need it to return a 0. Here is what I have...


SELECT   ISNULL(Count(* ),0) AS monthlycount, 
         user, 
         Datename(MONTH,transdate) AS MONTH, 
         Datepart(MONTH,transdate) AS monthnum, 
         Datename(YEAR,transdate)  AS YEAR 
FROM     ( 

...
subselect that returns all records in past 90 days
transdate is timestamp that's used to split count per month. 
no records in a month returns nothing for that month. 
i need select to return a 0 for a month with no records.
...

) AS tmp 
GROUP BY user, 
         Datename(MONTH,transdate), 
         Datepart(MONTH,transdate), 
         Datename(YEAR,transdate) 
ORDER BY user, 
         YEAR DESC, 
         monthnum DESC

example of current output (notice no value for Oct for username1):


monthlycount    user      month      monthnum      year
    10        username1    Jan           1          2010
    5         username1    Dec           12         2009
    1         username1    Nov           11         2009
    15        username2    Jan           1          2010
    7         username2    Dec           12         2009
    6         username2    Nov           11         2009
    4         username2    Oct           10         2009

example of what I need (notice the 0 value for Oct for username1):


monthlycount    user      month      monthnum      year
    10        username1    Jan           1          2010
    5         username1    Dec           12         2009
    1         username1    Nov           11         2009
    0         username1    Oct           10         2009
    15        username2    Jan           1          2010
    7         username2    Dec           12         2009
    6         username2    Nov           11         2009
    4         username2    Oct           10         2009

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-01-21 : 03:10:58

You can use the below script

SELECT DISTINCT Datename(MONTH,transdate) as MonthN,Datepart(MONTH,transdate) AS monthnumN,
Datename(YEAR,transdate) AS YEARN into #temp from dbo.tbltmp
where transdate<=GETDATE() AND transdate>=DATEADD(day, -90, GETDATE())

select distinct MonthN,monthnumN,YEARN,username into #temp1
from #temp ,tbltmp
where transdate<=GETDATE() AND transdate>=DATEADD(day, -90, GETDATE())

SELECT Count(*) AS monthlycount,
[user],
Datename(MONTH,transdate) as Month,
Datepart(MONTH,transdate) AS monthnum,
Datename(YEAR,transdate) AS [YEAR] into #temp2
FROM (
select username as [user],transdate
from dbo.tbltmp
where transdate<=GETDATE() AND transdate>=DATEADD(day, -90, GETDATE())

) AS tmp
GROUP BY [user],
Datename(MONTH,transdate),
Datepart(MONTH,transdate),
Datename(YEAR,transdate)
ORDER BY [user],
[YEAR] DESC,
monthnum DESC

select isnull(monthlycount,0) as monthlycount,username as [user],
monthN as Month,monthnumN as monthnum,YEARN as year
from #temp1 left join #temp2 on #temp1.username=#temp2.[user]
and #temp1.MonthN=#temp2.Month
ORDER BY user
YEAR DESC,
monthnum DESC

drop table #temp2

drop table #temp

drop table #temp1

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources