Join Question - 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 2005 Forums

Transact-SQL (2005)

Join Question

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2008-11-24 : 10:05:00

I am having a total brain lapse today

In the code below, I want to get TotalNumber of episodes from PatientEpisode, as well as the Total UnitCount from PatientTransfusions and I want it to be grouped by DRG. I know I dont need a sub select to do it, but when I just INNER JOIN the tables, the numbers dont match up...Any help please?

[CODE]
SELECT Count(pe.ID) as TotalEpisodes
, AVG(LOS) as AverageLOS
, FinalDrg
,(
SELECT SUM(UnitCount) FROM PatientTransfusions pt2
INNER JOIN PatientEpisode ON pt2.AccountNumber = PatientEpisode.AccountNumber
WHERE FinalDRG = pe.FinalDRG
) as TotalUnitsTransfused
FROM PatientEpisode pe
WHERE PrimaryProcedure Is Not Null
GROUP BY FinalDrg
ORDER BY Count(pe.ID) DESC, FinalDrg
[/CODE]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 10:36:18

Something similar to this?

SELECT DISTINCT	pe.FinalDrg,
		AVG(pe.LOS) OVER (PARTITION BY pe.FinalDrg),
		COUNT(*)  OVER (PARTITION BY pe.FinalDrg),
		SUM(pt.UnitCount) OVER (PARTITION BY pe.FinalDrg, pe.AccountNumber)
FROM		patientTransfusions AS pt
INNER JOIN	PatientEpisode AS pe ON pe.AccountNumber = pt.AccountNumber

E 12°55'05.63"
N 56°04'39.26"

Harry C
Posting Yak Master

148 Posts

Posted - 2008-11-24 : 10:50:19

No, I am getting duplicate FinalDRG rows. I need the FINALDrg to be in one row along with the SUMS, Counts etc... Thanks for helping

HC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 10:54:59

is it this what you want?

SELECT pe.FinalDrg,
		AVG(pe.LOS),
		COUNT(*),
		SUM(pt.UnitCount) 
FROM		patientTransfusions AS pt
INNER JOIN	PatientEpisode AS pe ON pe.AccountNumber = pt.AccountNumber
GROUP BY pe.FinalDrg

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 11:01:04

[code]SELECT COUNT(pe.ID) AS TotalEpisodes,
AVG(pe.LOS) AS AverageLOS,
pe.FinalDRG,
MIN(w.UnitCnt) AS TotalUnitsTransfused
FROM PatientEpisode AS pe
INNER JOIN (
SELECT pe.FinalDRG,
SUM(pt.UnitCount) AS UnitCnt
FROM PatientTransfusions AS pt
INNER JOIN PatientEpisode AS pe ON pe.AccountNumber = pt.AccountNumber
GROUP BY pe.FinalDRG
) AS w ON w.FinalDRG = pe.FinalDRG
WHERE pe.PrimaryProcedure IS NOT NULL
GROUP BY pe.FinalDRG
ORDER BY COUNT(PE.ID) DESC,
pe.FinalDrg[/code]

E 12°55'05.63"
N 56°04'39.26"

Harry C
Posting Yak Master

148 Posts

Posted - 2008-11-24 : 12:54:30

quote:

Originally posted by visakh16

is it this what you want?

SELECT pe.FinalDrg,
		AVG(pe.LOS),
		COUNT(*),
		SUM(pt.UnitCount) 
FROM		patientTransfusions AS pt
INNER JOIN	PatientEpisode AS pe ON pe.AccountNumber = pt.AccountNumber
GROUP BY pe.FinalDrg

No, because that gives me the total amount of transfusions per DRG with the Count(*). I need the total number of just patient episodes. When I join on the patient transfusion table that is when my results get skewed, because there are multiple transfusions per episode.. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 13:04:50

is it this then?


SELECT pe.FinalDrg,
		AVG(pe.LOS),
		COUNT(DISTINCT pe.AccountNumber),
		SUM(pt.UnitCount) 
FROM		patientTransfusions AS pt
INNER JOIN	PatientEpisode AS pe ON pe.AccountNumber = pt.AccountNumber
GROUP BY pe.FinalDrg

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 15:04:39

Did you test 11/24/2008 : 11:01:04 ?

E 12°55'05.63"
N 56°04'39.26"

Harry C
Posting Yak Master

148 Posts

Posted - 2008-12-02 : 13:42:30

Yes Peso, sorry for such a late reply. That does work. Thanks alot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources