Count to include SiteName with zero within where

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 2008 Forums

Transact-SQL (2008)

Count to include SiteName with zero within where

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-07-05 : 22:54:08

Count to include SiteName with zero counts within where clause. If I run this it returns the count for entire db include those with zero. OK so far.

Declare @StartDateRange AS DateTime
Declare @EndDateRange AS DateTime

SET @StartDateRange = ('2011-07-06 00:00:00.000')
SET @EndDateRange = ('2011-07-07 00:00:00.000')

SELECT SiteName, TransmitterType, Count(EventID) AS EventCount
FROM SiteDetails
LEFT JOIN EventDetails ON EventDetails.SiteID = SiteDetails.SiteID
GROUP BY SiteName, TransmitterType
ORDER BY EventCount Desc, SiteName Asc

SiteName TransmitterType EventCount
Bravo FastTrace2 275494
Lima FastTrace2 176588
India FastTrace2 133340
Foxtrot FastTrace2 70150
6 FastTrace2 19586
Kilo FastTrace2 339
2 FastTrace2 0
5 FastTrace2 0

But if I run this it returns only those with counts greater than zero within the search.

Declare @StartDateRange AS DateTime
Declare @EndDateRange AS DateTime

SET @StartDateRange = ('2011-07-06 00:00:00.000')
SET @EndDateRange = ('2011-07-07 00:00:00.000')

SELECT SiteName, TransmitterType, Count(EventID) AS EventCount
FROM SiteDetails
LEFT JOIN EventDetails ON EventDetails.SiteID = SiteDetails.SiteID
WHERE LocalStartTime Between @StartDateRange and @EndDateRange
GROUP BY SiteName, TransmitterType
ORDER BY EventCount Desc, SiteName Asc

SiteName TransmitterType EventCount
Lima FastTrace2 151
6 FastTrace2 147
India FastTrace2 147

What I need is this.

Lima FastTrace2 151
6 FastTrace2 147
India FastTrace2 147
2 FastTrace2 0
5 FastTrace2 0
Bravo FastTrace2 0
Foxtrot FastTrace2 0
Kilo FastTrace2 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-05 : 23:00:11

[code]
SELECT SiteName, TransmitterType,
Count(CASE WHEN LocalStartTime Between @StartDateRange and @EndDateRange THEN EventID END) AS EventCount
FROM SiteDetails
LEFT JOIN EventDetails ON EventDetails.SiteID = SiteDetails.SiteID
GROUP BY SiteName, TransmitterType
ORDER BY EventCount Desc, SiteName Asc
[/code]

KH
[spoiler]Time is always against us[/spoiler]

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-07-05 : 23:36:49

Thankyou for your support. This has provided an answer for the question I asked. I now need to add some more filters but cannot move them up into the case can I? With these additional filters it no longer works as expected as it only includes the sitename for the causes which have an event at some point in time. So if sites called Delta, Echo and Bravo and all but Bravo have a event called '1' in the db and Delta is outside the time range and Echo within then you get. Bravo does not get included because it does not have one of these events in the db.

Echo 10
Delta 0

SELECT SiteName, TransmitterType,
Count(CASE WHEN LocalStartTime Between @StartDateRange and @EndDateRange THEN EventID END) AS EventCount
FROM SiteDetails
LEFT JOIN EventDetails ON EventDetails.SiteID = SiteDetails.SiteID
WHERE SiteDetails.SiteID IN (Select Param From fn_MVParam (@SiteID,','))
AND SiteDetails.TransmitterType IN (Select Param From fn_MVParam (@TransmitterType+,','))
AND Cause IN (Select Param From fn_MVParam (@Cause,','))
GROUP BY SiteName, TransmitterType
ORDER BY EventCount Desc, SiteName Asc

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-05 : 23:51:33

quote:
I now need to add some more filters but cannot move them up into the case can I?

It depends what do you want. If you want to count based on the filter / condition than you can place it inside the CASE WHEN.

quote:
With these additional filters it no longer works as expected as it only includes the sitename for the causes which have an event at some point in time. So if sites called Delta, Echo and Bravo and all but Bravo have a event called '1' in the db and Delta is outside the time range and Echo within then you get. Bravo does not get included because it does not have one of these events in the db.

Not very sure what are you trying to achieve here. Perhaps you can post some sample data & expected result to clarify

KH
[spoiler]Time is always against us[/spoiler]

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-07-06 : 00:01:10

What I need is the SiteId and TransmitterType filters where they are but the Cause filter up inside the case statement. When I put the code there I get.

Msg 130, Level 15, State 1, Line 9
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

(1 row(s) affected)

'SELECT SiteName, TransmitterType,
Count(CASE WHEN LocalStartTime Between @StartDateRange and @EndDateRange AND Cause IN (Select Param From fn_MVParam ('''+@Cause+''','','')) THEN EventID END) AS EventCount
FROM [' + @DBName + '].dbo.SiteDetails LEFT JOIN
[' + @DBName + '].dbo.EventDetails ON [' + @DBName + '].dbo.EventDetails.SiteID = [' + @DBName + '].dbo.SiteDetails.SiteID
WHERE SiteDetails.SiteID IN (Select Param From fn_MVParam ('''+@SiteID+''','',''))
AND SiteDetails.TransmitterType IN (Select Param From fn_MVParam ('''+@TransmitterType+''','',''))
AND (SiteDetails.IsDeleted = ''False'')
GROUP BY SiteName, TransmitterType
'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-06 : 00:21:02

try something like this


; with cte as (
SELECT SiteName, TransmitterType,
(CASE WHEN LocalStartTime Between @StartDateRange and @EndDateRange AND Cause IN (Select Param From fn_MVParam ('''+@Cause+''','','')) THEN EventID END) AS EventCount
FROM [' + @DBName + '].dbo.SiteDetails LEFT JOIN
[' + @DBName + '].dbo.EventDetails ON [' + @DBName + '].dbo.EventDetails.SiteID = [' + @DBName + '].dbo.SiteDetails.SiteID
WHERE SiteDetails.SiteID IN (Select Param From fn_MVParam ('''+@SiteID+''','',''))
AND SiteDetails.TransmitterType IN (Select Param From fn_MVParam ('''+@TransmitterType+''','',''))
AND (SiteDetails.IsDeleted = ''False'')
)
select	SiteName, TransmitterType, count(EventCount) as EventCount
from    cte
GROUP BY SiteName, TransmitterType

KH
[spoiler]Time is always against us[/spoiler]

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-07-06 : 00:57:15

Thankyou for your additional support. This works well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources