December 10, 2008 at 7:14 am
I am currently producing output that calculates the cost of day spells in hospital. I calculate the number of days using the datediff function. When the patient is in hospital for 1 day i.e. the dates passed to the datediff funcion are the same, the cost comes out as 0 because the datediff function returns 0 when the dates are the same. However the patient should be charged for one day. How can I script this into my SQL. The t-sql I am using is as follows:
CASE dbo.udf_IP_POD(IP.Method_of_Admission_Code, IP.Patient_Classification_Code)
WHEN 'NEL' THEN 225
WHEN 'DC' THEN 676
WHEN 'NELNE' THEN 303
WHEN 'EL' THEN 259 END AS EstimatedPrice,
(datediff(day,Episode_Start_Date,Episode_End_Date)* CASE dbo.udf_IP_POD(IP.Method_of_Admission_Code, IP.Patient_Classification_Code)
WHEN 'NEL' THEN 225
WHEN 'DC' THEN 676
WHEN 'NELNE' THEN 303
WHEN 'EL' THEN 259 END) AS EstimatedEpisodeCost
Any ideas?
December 10, 2008 at 7:25 am
What do you want to see in the following cases?
StartDate: 2008/01/01 07:00:00, EndDate 2008/01/02 18:00:00
StartDate: 2008/01/01 07:00:00, EndDate 2008/01/02 07:00:00
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 10, 2008 at 7:48 am
In both cases they would be classed as 1 day. so a value of 1 should be returned. The time isn't involved in the calculation.
December 10, 2008 at 8:06 am
Maybe wrap the datediff in a case, when 0 then 1 else datediff...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply