Setting column to 0 when no rows are returned

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

Setting column to 0 when no rows are returned

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-03-02 : 07:50:49

Hello there.

I have a statement with a case statement.

see below.

select datepart(year,registrationDate)as 'YEAR',
datepart(month,registrationDate)as 'MONTH',
1 as 'Mapping',
datename(month,registrationDate)as 'MonthName',

ActualMonth = case count(QualificationRegistrationID)
when null else count(QualificationRegistrationID) end

from [83.138.168.94].[Parnassus].[dbo].QualificationRegistration qualificationRegistration
where qualificationRegistration.Deleted = 0
and datepart(month,registrationDate) = DATEPART(month,getdate())
and datepart(year,registrationDate) = DATEPART(year,getdate())
group by datepart(year,registrationDate),datepart(month,registrationDate),datename(month,registrationDate)
order by datepart(year,registrationDate),datepart(month,registrationDate),datename(month,registrationDate)

I want to set Actual month to 0 when no rows are returned so i have at least one row instead of nothing.

Could someone point me in the right direction please

Regards

Rob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 12:13:44

you need to have a calendar table for that. use the below function to generate one and do a left join with your table

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html


select datepart(year,f.[Date])as 'YEAR', 
datepart(month,f.[Date])as 'MONTH',
1 as 'Mapping',
datename(month,f.[Date])as 'MonthName',

count(t.QualificationRegistrationID) 

from dbo.CalendarTable(YourStartdate,YourENddate,0,0) f
left join [83.138.168.94].[Parnassus].[dbo].QualificationRegistration qualificationRegistration t
ON t.registrationDate = f.[Date]
and qualificationRegistration.Deleted = 0
and datepart(month,registrationDate) = DATEPART(month,getdate())
and datepart(year,registrationDate) = DATEPART(year,getdate())
group by datepart(year,f.[Date]),datepart(month,f.[Date]),datename(month,f.[Date])
order by datepart(year,f.[Date]),datepart(month,f.[Date]),datename(month,f.[Date])

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources