07-07-2014 06:21 PM
Could someone help me find the mistake I am doing in this SQL code????
I am getting the below output
my question is that when I group it by HAC which I unique I was expecting the HAC_count as 1 for al the records.
But to my surprise I am getting 2 .
could you please help me figure this????
MEASURE CAL_YR CAL_MO_ODER HospitalAccountID HAC_count
HAC4 2013 5 101 1
HAC4 2013 5 102 2
HAC4 2013 5 103 1
CREATE TABLE HAC4 AS
'HAC4' AS Measure,
acc.HospitalAccountID as Hac,
COUNT (acc.HospitalAccountID) AS HAC_Count
FROM dwprap.FACT_HospitalAccount AS acc,
dwprap.FACT_HospitalAccountDiagnosis AS diag,
dwprap.DIM_Date AS dat,
dwprap.DIM_Diagnosis AS ddiag
where acc.HospitalAccountID = diag.HospitalAccountID
and acc.DateDischargeKey = dat.DateKey
and diag.DiagnosisKey = ddiag.DiagnosisKey
and diag.Line ne 1
AND diag.DX_POA_YNU IN ('N','U')
AND acc.DateDischargeKey >= &BegDate
AND acc.DateDischargeKey <= &EndDate
AND ddiag.ICD9_Code IN (
SELECT hacd.DX_Code FROM dwprap.REF_HACDiagnosis AS hacd
WHERE hacd.HAC_Description = 'HAC4') /*--('707.23','707.24')*/
AND EncounterTypeKey IN (1, 12, 13, 14, 15)
GROUP BY HAC, dat.Cal_Yr, dat.Cal_Mo_Order
ORDER BY HAC, dat.Cal_Yr, dat.Cal_Mo_Order
07-07-2014 06:29 PM
Are you getting any message in the log about cartesian products or such? The way you are joining the tables is likely to create such. So if if HAC is unique in one table each record is being matched agains all the other records in the other table creating duplications in the output.
07-07-2014 09:59 PM
i was with the opinion since acc.hospitalaccountid was aliased as HAC I used it in the groupby and orderby!!!
does it need to be changed to hospitalaccountid itself???????????
07-07-2014 10:57 PM
Were you surprised to find duplicate rows? I would expect that within the same year and month it is very likely to have more than one order for the same account.
Or did you mean to use COUNT (DISTINCT acc.HospitalAccountID) ?
Unless acc.HospitalAccountID has missing values then COUNT (acc.HospitalAccountID) is the same as COUNT(*).