Help With Query - SQL Server Forums

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)

Help With Query

Author

Topic

gfrenkel1
Starting Member

1 Post

Posted - 2011-08-09 : 23:09:42

Consider the following scenario: Tracking cases by doctor's username - two types of cases: OBCases GynCases. All cases happen in a hospitlal and a doctor can work in many hospitals

Hospitals OBCases GynCases
HospID CaseID CaseID
HospName HospID HospID
UserName UserName 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.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-08-10 : 05:49:58

Try the scripts,

CREATE TABLE #Hospitals(HospID INT,HospName VARCHAR(100),UserName VARCHAR(100))
CREATE TABLE #OBCases(CaseID INT,HospID INT,UserName VARCHAR(100))
CREATE TABLE #GynCases(CaseID INT,HospID INT,UserName VARCHAR(100))

INSERT INTO #Hospitals VALUES(1,'H1','U1')
INSERT INTO #Hospitals VALUES(2,'H2','U1')
INSERT INTO #Hospitals VALUES(3,'H3','U1')
INSERT INTO #Hospitals VALUES(4,'H4','U1')

INSERT INTO #OBCases VALUES(1,1,'U1')
INSERT INTO #OBCases VALUES(2,1,'U1')
INSERT INTO #OBCases VALUES(3,2,'U1')

INSERT INTO #GynCases VALUES(1,2,'U1')
INSERT INTO #GynCases VALUES(2,2,'U1')
INSERT INTO #GynCases VALUES(3,2,'U1')
INSERT INTO #GynCases VALUES(4,3,'U1')
SELECT * FROM #OBCases
SELECT * FROM #GynCases

SELECT #Hospitals.HospID, COUNT(#OBCases.CaseID) AS OBCase INTO #TEMP1
From #Hospitals LEFT JOIN #OBCases ON #Hospitals.HospID = #OBCases.HospID
group by #Hospitals.HospID

SELECT #Hospitals.HospID, COUNT(#GynCases.CaseID) AS GynCases INTO #TEMP2
From #Hospitals LEFT JOIN #GynCases ON #Hospitals.HospID = #GynCases.HospID
group by #Hospitals.HospID

SELECT #Hospitals.HospName,OBCase,GynCases FROM #Hospitals INNER JOIN #TEMP1 ON #TEMP1.HospID = #Hospitals.HospID
INNER JOIN #TEMP2 ON #TEMP2.HospID = #Hospitals.HospID

DROP TABLE #TEMP1
DROP TABLE #TEMP2

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-10 : 08:19:55

where's doctor information in the table structure above? i can see only hospital info and also case info

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources