Arithmetic with Aggregated field

Question

Post reply

Arithmetic with Aggregated field

eseosaoregie

SSCertifiable

Points: 6889
More actions
February 20, 2009 at 8:36 am

#131091

I am currently trying to do create a computed field which uses an aggregated field as one of the elements to produce the computed data. My output in the computed field appears to be incorrect. I am not sure if my syntax is correct. Can anyone shed some light on why this may be. I have added the script below:

SELECT COUNT (Spells.Spell_Episode_ID35) AS [NO. Of Attendances] ,(dbo.udf_IP_POD(Costed.Method_of_Admission_Code, Costed.Patient_Classification_Code)) AS POD ,CM.Practice_Code ,COUNT(Spells.Spell_Episode_ID35)/GPP.PRACTICE_POPULATION * 1000 AS [Attendance by 1000]-- calculation to get rate per 1000 FROM nwcscmdsdata.dbo.Costed_2008_Inpatient_Spells35_xMFF Costed INNER JOIN nwcscmdsdata.dbo.Inpatient_Spells35 Spells ON Costed.Episode_ID = Spells.Spell_Episode_ID35 INNER JOIN PBC_Providers ON dbo.udf_providerCode(Costed.PAS_data_source) = PBC_Providers.providerCode INNER JOIN IP_Tariff ON Costed.HRG35_Code = IP_Tariff.HRG INNER JOIN nwcscmdsdata.dbo.Inpatient_CMDS_Data CM ON CM.Episode_ID = Costed.Episode_ID LEFT OUTER JOIN BRENTREPODB02.Olympus.dbo.GP_PRACTICE_POPULATION GPP ON GPP.GP_PRACTICE_CODE = CM.Practice_Code WHERE (Costed.Purchaser_ID LIKE '5K5%') AND (Spells.month_of_end_of_spell BETWEEN '200804' AND '200903') AND (NOT ISNULL(Costed.Contract_Line_Number,'') = 'NONCHARGE') GROUP BY CM.Practice_Code ,dbo.udf_IP_POD(Costed.Method_of_Admission_Code, Costed.Patient_Classification_Code) ,GPP.PRACTICE_POPULATION