Showing Zero records - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Showing Zero records

Author

Topic

alexmarshuk
Starting Member

12 Posts

Posted - 2008-10-07 : 06:21:54

I'm trying to write a report with the following SQL
declare @date smalldatetime

set @date = '2008-10-01'

--case length ytd

select
datediff(day,start_date,end_date)
from
cases
where
start_date <= end_date
and end_date is not null
and end_date < dateadd(month,1,@date)
--and datepart(year,start_date) = datepart(year,@date)

--select count(case_id) from cases where datepart(year,start_date) = datepart(year,@date) and start_date < dateadd(month,1,@date) and end_date is null

when the :date = 2008-10-01 results are returned with no problem.

when the date is changed to 2008-08-01 then no results are displayed... (i expect a zero to be displayed.. nothing is at present).

how do i get the statement to display a zero instead of nothing when there are no results..

many thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 06:29:30

when there's no data in table for period you will get only empty resultset not 0 value.may be wht you're looking for is this

select 
sum(datediff(day,start_date,end_date))
from 
cases 
where 
start_date <= end_date
and end_date is not null 
and end_date < dateadd(month,1,@date)
--and datepart(year,start_date) = datepart(year,@date)