I have a query that looks for and counts orders that have been written in a medical record that have a specific display group SID. I have 8 medical centers and I want a count for all of them even if it's zero. My query return counts fine when there are instances, but it will not list a facility with a zero count. I tried the LEFT JOIN as mentioned in other threads.
SELECT DISTINCT * INTO #T FROM (SELECT p. [PatientSID] ,p.[TestPatientFlag] ,co.[CPRSOrderSID] ,co.[DisplayGroupSID] ,co.[Sta3n] ,[EnteredDateTime] FROM [CPRSOrder].[CPRSOrder] co LEFT JOIN [SPatient].[SPatient] p ON co.[PatientSID] = p.[PatientSID] WHERE [TestPatientFlag] IS NULL AND [EnteredDateTime] >'2018-01-01' ) AS X SELECT [Sta3n] ,COUNT([DisplayGroupSID]) AS '# Of RTC Requests' FROM #T WHERE [DisplayGroupSID] IN (1200003559 ,1200003561 ,1200003558 ,1200003557 ,1200003560 ,1200003562 ,1200003556 ,1200003519) GROUP BY [Sta3n]
Here's the output (It's missing 4 sites who have no instance of the type of order I am looking for:
Sta3n # Of RTC Requests ----- ----------------- 679 755 544 974 521 2 534 54