How to return a Zero instead of no Results in SQL Query

Sometimes you want to query to see if an item exists but if it doesn’t you usually get an empty result in SQL.  It would be more useful if it would return a 0.

I often want to ask the question: Does x exist? and get back a yes or no.  Yes or No often translates to boolean as a 1 or 0.  Let say I query to see if a Date is a Holiday.  I have a table of Holidays.

Holidays Table

I want to see if today is a holiday so i enter the following:

Select case when count(HolidayKey) > 0 then 1 else 0 end as IsHoliday
 from dbo.Holidays
 where date = DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

The case statement returns the results I am looking for.   If the date is Holiday then the count of Holidaykey is greater than or equal to 1, which returns a 1.  If the date doesn’t exist in the table the count function returns a  zero.

count holidaykey

Without the count, the case statement fails to return the proper result. See below.

 

case HolidayKey

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s