A Grouping
-
Hello, And thanks in advance for the help. I am using this query to group exams and count: SELECT BPG.[GroupName], COUNT(A.[ProcedureID]) AS [Count] FROM [DB].[dbo].[Accessions] A INNER JOIN [DB].[dbo].[table1] BP ON A.[ProcedureID] = BP.[ProcedureID] INNER JOIN [DB].[dbo].[table2] BPG ON BP.[ProcedureGroup] = BPG.[ProcedureGroupID] INNER JOIN [DB].[dbo].[table3] O ON A.[OrderID] = O.[OrderID] INNER JOIN [DB].[dbo].[table4] F ON O.[OrderingFacilityID] = F.[FacilityID] WHERE --XRAY,US,PICC,EKG (BPG.[GroupName] IN ('XRAY','EKG','US','PICC')) AND (F.[Name] LIKE '%' + @Facility + '%') AND (A.[ExamDate] >= CONVERT(DATETIME, @StartDate + @Year) AND A.[ExamDate] < CONVERT(DATETIME, @EndDate + @Year) AND (A.[Status] != 'Cancelled' AND A.[Status] != 'ADMIN CANCEL' AND A.[Status] != 'Cancelled - Dry Run')) GROUP BY BPG.[GroupName] Let's say the query only returns 'XRAY' and 'EKG' with a count. How can I also return that 'US' and 'PICC' have zero count. :doh: Thanks for the help.
-
Hello, And thanks in advance for the help. I am using this query to group exams and count: SELECT BPG.[GroupName], COUNT(A.[ProcedureID]) AS [Count] FROM [DB].[dbo].[Accessions] A INNER JOIN [DB].[dbo].[table1] BP ON A.[ProcedureID] = BP.[ProcedureID] INNER JOIN [DB].[dbo].[table2] BPG ON BP.[ProcedureGroup] = BPG.[ProcedureGroupID] INNER JOIN [DB].[dbo].[table3] O ON A.[OrderID] = O.[OrderID] INNER JOIN [DB].[dbo].[table4] F ON O.[OrderingFacilityID] = F.[FacilityID] WHERE --XRAY,US,PICC,EKG (BPG.[GroupName] IN ('XRAY','EKG','US','PICC')) AND (F.[Name] LIKE '%' + @Facility + '%') AND (A.[ExamDate] >= CONVERT(DATETIME, @StartDate + @Year) AND A.[ExamDate] < CONVERT(DATETIME, @EndDate + @Year) AND (A.[Status] != 'Cancelled' AND A.[Status] != 'ADMIN CANCEL' AND A.[Status] != 'Cancelled - Dry Run')) GROUP BY BPG.[GroupName] Let's say the query only returns 'XRAY' and 'EKG' with a count. How can I also return that 'US' and 'PICC' have zero count. :doh: Thanks for the help.
Try to change join condition and see if you get result which you need.
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com
-
Try to change join condition and see if you get result which you need.
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com
Thank you. I am not sure what to change and that is why I asked for help.
-
Hello, And thanks in advance for the help. I am using this query to group exams and count: SELECT BPG.[GroupName], COUNT(A.[ProcedureID]) AS [Count] FROM [DB].[dbo].[Accessions] A INNER JOIN [DB].[dbo].[table1] BP ON A.[ProcedureID] = BP.[ProcedureID] INNER JOIN [DB].[dbo].[table2] BPG ON BP.[ProcedureGroup] = BPG.[ProcedureGroupID] INNER JOIN [DB].[dbo].[table3] O ON A.[OrderID] = O.[OrderID] INNER JOIN [DB].[dbo].[table4] F ON O.[OrderingFacilityID] = F.[FacilityID] WHERE --XRAY,US,PICC,EKG (BPG.[GroupName] IN ('XRAY','EKG','US','PICC')) AND (F.[Name] LIKE '%' + @Facility + '%') AND (A.[ExamDate] >= CONVERT(DATETIME, @StartDate + @Year) AND A.[ExamDate] < CONVERT(DATETIME, @EndDate + @Year) AND (A.[Status] != 'Cancelled' AND A.[Status] != 'ADMIN CANCEL' AND A.[Status] != 'Cancelled - Dry Run')) GROUP BY BPG.[GroupName] Let's say the query only returns 'XRAY' and 'EKG' with a count. How can I also return that 'US' and 'PICC' have zero count. :doh: Thanks for the help.
RadioButton wrote:
INNER JOIN [DB].[dbo].[table1] BP ON A.[ProcedureID] = BP.[ProcedureID]
I think this should be
LEFT OUTER JOINinstead. Also you should change the count to becount(nvl(a.ProcedureID,0))
so the NULL values will be counted as zero. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
RadioButton wrote:
INNER JOIN [DB].[dbo].[table1] BP ON A.[ProcedureID] = BP.[ProcedureID]
I think this should be
LEFT OUTER JOINinstead. Also you should change the count to becount(nvl(a.ProcedureID,0))
so the NULL values will be counted as zero. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
I tried your suggestion and that did not change anything. I am using MS SQL 2008 and the NVL function does not appear to be avaible so I tried the ISNULL function. I believe it does the same thing. Thanks again.
-
Hello, And thanks in advance for the help. I am using this query to group exams and count: SELECT BPG.[GroupName], COUNT(A.[ProcedureID]) AS [Count] FROM [DB].[dbo].[Accessions] A INNER JOIN [DB].[dbo].[table1] BP ON A.[ProcedureID] = BP.[ProcedureID] INNER JOIN [DB].[dbo].[table2] BPG ON BP.[ProcedureGroup] = BPG.[ProcedureGroupID] INNER JOIN [DB].[dbo].[table3] O ON A.[OrderID] = O.[OrderID] INNER JOIN [DB].[dbo].[table4] F ON O.[OrderingFacilityID] = F.[FacilityID] WHERE --XRAY,US,PICC,EKG (BPG.[GroupName] IN ('XRAY','EKG','US','PICC')) AND (F.[Name] LIKE '%' + @Facility + '%') AND (A.[ExamDate] >= CONVERT(DATETIME, @StartDate + @Year) AND A.[ExamDate] < CONVERT(DATETIME, @EndDate + @Year) AND (A.[Status] != 'Cancelled' AND A.[Status] != 'ADMIN CANCEL' AND A.[Status] != 'Cancelled - Dry Run')) GROUP BY BPG.[GroupName] Let's say the query only returns 'XRAY' and 'EKG' with a count. How can I also return that 'US' and 'PICC' have zero count. :doh: Thanks for the help.
Connect to the BPG table and all of its inner joins first. Then do a LEFT OUTER JOIN to your Accessions (A) table and it's joins. This will give you all of the group names and the 0's if there is nothing for that group.
SELECT BPG.[GroupName], COUNT(A.[ProcedureID]) AS [Count]
FROM
[DB].[dbo].[table2] BPGLEFT OUTER JOIN
([DB].[dbo].[Accessions] A
INNER JOIN [DB].[dbo].[table1] BP ON A.[ProcedureID] = BP.[ProcedureID]
INNER JOIN [DB].[dbo].[table3] O ON A.[OrderID] = O.[OrderID]
INNER JOIN [DB].[dbo].[table4] F ON O.[OrderingFacilityID] = F.[FacilityID])
ON BP.[ProcedureGroup] = BPG.[ProcedureGroupID]
WHERE
.....GROUP BY BPG.[GroupName]
Brent