August 9, 2011 at 9:13 pm
Consider the following scenario: Tracking cases by doctor's username - two types of cases: OBCases GynCases. All cases happen in a hospilal and a doctor can work in many hospitals
Hospitals OBCases GynCases
HospID CaseID CaseID
HospNameHospID HospID
UserNameUserName UserName
I need a query that will give me the Hospital Name, Total GynCase , Total OBcases for a given doctor e.g
Hospital Totalgyn TotalOB
hosp1 3 5
hosp2 4 9
Along the lines of:
SELECT HospName, COUNT(OBCases.CaseID) AS Expr1, COUNT(GYNCases.CaseID) AS Expr2
From Hospitals, OBCases, GynCases
Order by Hospname
I've tried all sorts of joins and wheres and just can't get it. Any Help will be greatly appreciated.
August 9, 2011 at 9:41 pm
In order to help those who can help you with a tested T-SQL statement, please, please post the table definition(s), provide some sample data and the desired results from the sample data.
To do this please click on the first link in my signature block. The article contains sample T-SQL code to do this rapidly and easily.
August 9, 2011 at 9:56 pm
Hello,
This may be helpful to you. One way to resolve your query, you can also use CTEs for the same.
DECLARE @hospitals TABLE
(HospId INT,
HospName NVARCHAR(10),
UserName NVARCHAR(10))
DECLARE @OBCases TABLE
(
CaseId INT,
HospId INT,
UserName NVARCHAR(10))
DECLARE @GynCases TABLE
(
CaseId INT,
HospId INT,
UserName NVARCHAR(10))
INSERT INTO @hospitals VALUES(1, 'Hospital 1', 'HD'),(2, 'Hospital 2', 'HD 2'), (3, 'Hospital 3', 'HD 3')
INSERT INTO @OBCases VALUES(1, 1, 'HD'),(2, 1, 'HD'),(3, 1, 'HD') ,(4, 2, 'HD'),(5, 2, 'HD')
INSERT INTO @GynCases VALUES(1, 1, 'HD'),(2, 1, 'HD'),(3, 1, 'HD') ,(4, 2, 'HD'),(5, 2, 'HD'),(6, 2, 'HD'),(7, 2, 'HD')
SELECT h.HospName, COALESCE(o.OBCaseCount,0) OBCaseCount, COALESCE(g.GynCaseCount, 0) GynCaseCount
FROM @hospitals h
LEFT OUTER JOIN
(SELECT COUNT(o.CaseId) OBCaseCount, o.HospId
FROM @OBCases o
GROUP BY o.HospId) o
ON o.HospId = h.HospId
LEFT OUTER JOIN
(SELECT COUNT(g.CaseId) GynCaseCount, g.HospId
FROM @GynCases g
GROUP BY g.HospId) g
ON o.HospId = g.HospId
Thanks
August 10, 2011 at 10:27 am
That did it - Thanks so much. It looksl ike I need to read up on coallesce.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply