Returning no results as 0 - 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

General SQL Server Forums

New to SQL Server Programming

Returning no results as 0

Author

Topic

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-15 : 09:10:00

Hi My first Post.

Basically I am trying to get my report to show dates where there are no counted results on days, aside the days where there are results.

I messed it up, left it, and now I come back to it and can't see where I have gone wrong.

My query looks like this.

Set transaction Isolation level Read Uncommitted

Declare @beginDate as DateTime
Declare @endDate as DateTime
Declare @FromDate as DateTime
Declare @ToDate as DateTime

Set @beginDate = <%FromDate|Start Date%>
Set @endDate = <%ToDate|End Date%>
Set @FromDate = dbo.fUniversalTime(@beginDate) -- 10
Set @ToDate = dbo.fUniversalTime(@endDate)

Declare @StartDate as DateTime
Set @StartDate = @FromDate

Declare @Dates Table
(
Dates DateTime
)
-- 20
While @StartDate < GETDATE()
Begin
insert into @Dates (Dates) values (@StartDate)
Set @StartDate = (Select DateAdd(dd, 1, @StartDate))
End

Select Convert(DateTime, convert(VarChar(8), @StartDate, 112)) as 'Dates',
isNull(callback.TotalPerDate, 0) as 'TotalPerDate',

From @Dates d
-- 30
left Join (Select Convert(DateTime, Convert(VarChar(8), dbo.fLocalTime(c.ActiveDate), 112)) as 'Dates',
COUNT(*) as TotalPerDate
From [case] c
Join CaseEvents ce on ce.CaseRef = c.CaseRef
Join OrganisationGroup org on org.OrganisationgroupRef = c.OrganisationGroupRef
Join CaseType ct on ct.CaseTypeRef = c.CaseTypeRef
Where ce.EntryDate between @FromDate and @ToDate and c.Cancelled = 0 and c.TestCall = 0
and ce.EventType = 'CALLBACK'
Group By Convert(DateTime, Convert(VarChar(8), dbo.fLocalTime(c.ActiveDate), 112)) -- 40
) callback on callback.dates = d.Dates

I understand that I have to get all the dates between 2 dates in my temp table first, then get the results, then join the results to the days..

I am stuck like a newb.

Any help would be appreciated (Any alternative methods of doing this), we use SQL Server 2000 So I cant use 'WITH' :(

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-15 : 10:12:57

What is the problem? syntax error? wrong results? what?

I see two things wrong - one is this stuff: "<%FromDate|Start Date%>" but you may have put that in for our benefit.
The other thing is it looks like you've got an extra comma <,> in there. (at the end of this line):

isNull(callback.TotalPerDate, 0) as 'TotalPerDate',

Be One with the Optimizer
TG

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-16 : 06:12:50

thanks, new it would be something simple.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources