Getting Count when "nothing is there"
-
I need to also get a 0 count when ther is no resources for the selected date.
How would I do that?
SELECT ResourceID, COUNT(ScheduleId) AS Count
FROM ptSchedule
WHERE (Status <> 5) AND (MRN IS NOT NULL)
AND (CONVERT(VARCHAR, StartDtTm, 101) = '10/01/2008')
GROUP BY ResourceID
ResourceID links to coApptResources.ResourceID, there are 23 total Resources
but the query above returns only 8 rows for the particular date:
26
42
85
945
1014
115
2014
214
I'd like to see this:
10
26
30
42
50
60
70
85
945
1014
115
120
130
140
150
160
170
180
190
2014
214
220
230
-
MrBaseball34 (12/12/2008)
I need to also get a 0 count when ther is no resources for the selected date.How would I do that?
SELECT ResourceID, COUNT(ScheduleId) AS Count
FROM ptSchedule
WHERE (Status <> 5) AND (MRN IS NOT NULL)
AND (CONVERT(VARCHAR, StartDtTm, 101) = '10/01/2008')
GROUP BY ResourceID
ResourceID links to coApptResources.ResourceID, there are 23 total Resources
but the query above returns only 8 rows for the particular date:
26
42
85
945
1014
115
2014
214
I'd like to see this:
10
26
30
42
50
60
70
85
945
1014
115
120
130
140
150
160
170
180
190
2014
214
220
230
I am assuming that you want to drop the criteria of MRN is not null, correct? If so, try the following:
SELECT ResourceID, isnull(COUNT(ScheduleId), 0) AS Count
FROM ptSchedule
WHERE (Status <> 5) --AND (MRN IS NOT NULL)
AND (CONVERT(VARCHAR, StartDtTm, 101) = '10/01/2008')
GROUP BY ResourceID
Or if you are linking to another table, coApptResources, try this:
SELECT coApptResources.ResourceID, isnull(COUNT(ptSchedule.ScheduleId), 0) AS Count
FROM ptSchedule right outer join join coApptResources on (ptSchedule.ResourceID = coApptResources.ResourceID)
WHERE (Status <> 5) AND (MRN IS NOT NULL)
AND (CONVERT(VARCHAR, StartDtTm, 101) = '10/01/2008')
GROUP BY coApptResources.ResourceID
Let us know if either of these works.
-
Untested due to no sample data, but something like this should work:
SELECT R.ResourceID, PC.RCountFROM ResourceTable R -- This should be a table holding your 23 types if you have one
LEFT JOIN (SELECT ResourceID, COUNT(ScheduleId) RCount
FROM ptSchedule
WHERE (Status <> 5) AND (MRN IS NOT NULL) AND (CONVERT(VARCHAR, StartDtTm, 101) = '10/01/2008')
GROUP BY ResourceID) PC
ON R.ResourceID = PC.ResourceID
-
Lynn Pettis (12/12/2008)
I am assuming that you want to drop the criteria of MRN is not null, correct? If so, try the following:SELECT ResourceID, isnull(COUNT(ScheduleId), 0) AS Count
FROM ptSchedule
WHERE (Status <> 5) --AND (MRN IS NOT NULL)
AND (CONVERT(VARCHAR, StartDtTm, 101) = '10/01/2008')
GROUP BY ResourceID
Or if you are linking to another table, coApptResources, try this:
SELECT coApptResources.ResourceID, isnull(COUNT(ptSchedule.ScheduleId), 0) AS Count
FROM ptSchedule right outer join join coApptResources on (ptSchedule.ResourceID = coApptResources.ResourceID)
WHERE (Status <> 5) AND (MRN IS NOT NULL)
AND (CONVERT(VARCHAR, StartDtTm, 101) = '10/01/2008')
GROUP BY coApptResources.ResourceID
Let us know if either of these works.
Yes, the check for MRN IS NOT NULL is required. And neither of them worked. However, Seth's solution worked with a slight modification. Thanks, Seth..
SELECT R.ResourceID, ISNULL(PC.RCount, 0)
FROM coApptResources R
LEFT JOIN (SELECT ResourceID, COUNT(ScheduleId) RCount
FROM ptSchedule
WHERE (Status <> 5)
AND (MRN IS NOT NULL)
AND (CONVERT(VARCHAR, StartDtTm, 101) = '10/01/2008')
GROUP BY ResourceID) PC
ON R.ResourceID = PC.ResourceID
-
Heh, oops, yeah, forgot the ISNULL as I was walking out the door as I posted this. Thanks for the feedback.
-
Another way is to use GROUP BY ALL, although according to BOL this feature may be removed from future versions of SQL Server
SELECT ResourceID, COUNT(ScheduleId) AS CountFROM ptSchedule
WHERE (Status <> 5) AND (MRN IS NOT NULL)
AND (CONVERT(VARCHAR, StartDtTm, 101) = '10/01/2008')
GROUP BY ALL ResourceID
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
The group by all doesn't return all the rows. Here is the result:
10
26
30
42
50
70
85
945
1014
115
120
130
140
150
160
170
180
190
2014
214
220
And with the code we are currently using:
;WITH PC (ResourceID, RCount)
AS (SELECT ResourceID, COUNT(ScheduleId) AS RCount
FROM ptSchedule
WHERE (Status <> 5)
AND (MRN IS NOT NULL)
AND (CONVERT(VARCHAR, StartDtTm, 101) = '10/01/2008')
GROUP BY ResourceID)
SELECT R.ResourceID, ISNULL(PC.RCount, 0)
FROM coApptResources R
LEFT JOIN PC ON R.ResourceID = PC.ResourceID
results:
10
26
30
42
50
60
70
85
945
1014
115
120
130
140
150
160
170
180
190
2014
214
220
230
-
It is my understanding that 6 and 23 would be returned if they had *ever* been used in that table. (for the GROUP BY ALL method)
-
But they still exist in coApptResources and I need to show 0 if there are no appointments.
-
SELECT ResourceID, sum(Case When (Status <> 5) AND (MRN IS NOT NULL)
AND (CONVERT(VARCHAR, StartDtTm, 101) = '10/01/2008')
Then 1 Else 0 End) AS Count
FROM ptSchedule
GROUP BY ResourceID
-
Here is a method that does not use a CTE - and will perform much better if there is an index on the StartDtTm column:
Select r.ResourceID
,count(s.ScheduleId) As Total
From #coApptResources r
Left Join #ptSchedule s
On s.ResourceID = r.ResourceID
And s.Status <> 5
And s.Mrn Is Not Null
And s.StartDtTm >= '20081001 00:00:00.000'
And s.StartDtTm < '20081002 00:00:00.000'
Group By r.ResourceID;
To be honest, I prefer the CTE as it is a bit easier (for me) to read. I would definitely modify the date filter to remove the CONVERT - as that will definitely eliminate any ability to use an index on that column if one exists.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply