February 20, 2009 at 8:36 am
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
February 20, 2009 at 8:53 am
you are probably experiencing "integer" division.
Change
COUNT(Spells.Spell_Episode_ID35)/GPP.PRACTICE_POPULATION * 1000
To
1.0*COUNT(Spells.Spell_Episode_ID35)/GPP.PRACTICE_POPULATION * 1000
* Noel
February 20, 2009 at 9:11 am
Thanks. It is working now.
February 21, 2009 at 2:39 am
Here is the reason
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx
Failing to plan is Planning to fail
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply