January 3, 2008 at 8:57 am
I want to Add in a Case Statement to my Stored Procedure to Identify 5 classifications:
A: Birth to 6 yrs old
B: Female 7 yrs to 18 yrs
C: Male 7 yrs to 18 yrs
D: Female over 18 yrs old
E: Male over 18 yrs old
So Im looking for something like this:
CASE
WHEN [Patient Age] >= 6 THEN 'A'
WHEN [Patient Age] between .....
Any help is appreciated!
CODE >>>>
Declare @ApptDate datetime
Select @ApptDate = a.ApptStart
FROM Appointments a
WHERE a.AppointmentsId = @AppointmentsId
SELECT '290.PatientName'=IsNull(pp.First,'') + ' ' + isnull(pp.Middle,'') + ' ' + isnull(pp.Last,'')+ ' ' + isnull(pp.Suffix,''),
'291.PatLast'=IsNull(pp.Last,''),
'292.PatFirst'=IsNull(pp.First,''),
'293.PatMiddle'=IsNull(pp.Middle,''),
'294.PatientAddr1'=IsNull(pp.Address1,''),
'295.PatientAddr2'=IsNull(pp.Address2,''),
'296.PatientCity'=IsNull(pp.City,''),
'297.PatientState'=IsNull(pp.State,''),
'298.PatientZip'=IsNull(pp.Zip,''),
'299.PatientCountry' = ISNULL(pp.Country,''),
'300.PatientBirthdate' = pp.Birthdate,
'301.PatientSex'=IsNull(pp.Sex,''),
'302.PatientPhone1'=IsNull(pp.Phone1,''),
'303.PatientSSN'=IsNull(pp.SSN,''),
'304.PatOccupation'=IsNull(pp.EmpOccup,''),
'305.PatSchool'=IsNull(pp.MedicalRecordNumber,''),
'306.PatBudget'=IsNull(g.Budget,0),
'307.PatientSameAsGuarantor'=IsNull(pp.PatientSameAsGuarantor,0),
'308.PatSuffix'=IsNull(pp.Suffix,''),
'400.PatientAge' = CASE
WHEN datediff(day, pp.birthdate, @ApptDate) <= 6 THEN cast(datediff(day, pp.birthdate, @ApptDate) as varchar(10)) + ' dys'
WHEN datediff(day, pp.birthdate, @ApptDate) <= 112 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 7) as varchar(10)) + ' wks'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) <= day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) as varchar(10)) + ' mos'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) - 1 as varchar(10)) + ' mos'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) >= 2 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) as varchar(10)) + ' yrs'
ELSE '' END
FROM PatientProfile pp
LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
WHEREpp.PatientProfileID = @PatientProfileId
January 3, 2008 at 9:36 am
You have the basic case statement above in your code.
case when xxx then yyy
when zzz then iiii
else jjjj
end
so
case
when date = 7 and date <= 18) then yyy
etc.
January 3, 2008 at 9:40 am
I have tried using the following:
'401.PatientAgeGroup' =
CASE
WHEN [400.PatientAge] > 18 AND pp.PatientSex = 'M' THEN 'E'
WHEN [400.PatientAge] > 18 AND pp.PatientSex = 'F' THEN 'D'
WHEN [400.PatientAge] BETWEEN 7 AND 18 AND pp.PatientSex = 'M' THEN 'C'
WHEN [400.PatientAge] BETWEEN 7 AND 18 AND pp.PatientSex = 'F' THEN 'B'
WHEN [400.PatientAge] BETWEEN 0 AND 6 THEN 'A'
ELSE 'UNKNOWN'
END
And I get this:
Msg 207, Level 16, State 1, Line 36
Invalid column name '400.PatientAge'.
Msg 207, Level 16, State 1, Line 36
Invalid column name 'PatientSex'.
Msg 207, Level 16, State 1, Line 37
Invalid column name '400.PatientAge'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'PatientSex'.
Msg 207, Level 16, State 1, Line 38
Invalid column name '400.PatientAge'.
Msg 207, Level 16, State 1, Line 38
Invalid column name '400.PatientAge'.
Msg 207, Level 16, State 1, Line 38
Invalid column name 'PatientSex'.
Msg 207, Level 16, State 1, Line 39
Invalid column name '400.PatientAge'.
Msg 207, Level 16, State 1, Line 39
Invalid column name '400.PatientAge'.
Msg 207, Level 16, State 1, Line 39
Invalid column name 'PatientSex'.
Msg 207, Level 16, State 1, Line 40
Invalid column name '400.PatientAge'.
Msg 207, Level 16, State 1, Line 40
Invalid column name '400.PatientAge'
January 3, 2008 at 9:44 am
Please Post your entire query.
Your error message indicates that there is no column named '400.PatientAge'
or column named 'PatientSex'
January 3, 2008 at 9:49 am
My first guess, based on the data given, is that "400" is a table name (or table alias). In that case, what you need is "[400].[PatientAge]", instead of "[400.PatientAge]". You have to have one set of braces around the table name and one around the column name, not one around both together.
If that's not the case, and there actually is a column called "400.PatientAge", then I'll need to see more of the query (at least the From clause).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 3, 2008 at 9:49 am
You cant create a column at run time and use it in the next case statement. You have to reference the below code or house the data in a temp table to call the column names.
'400.PatientAge' = CASE
WHEN datediff(day, pp.birthdate, @ApptDate) <= 6 THEN cast(datediff(day, pp.birthdate, @ApptDate) as varchar(10)) + ' dys'
WHEN datediff(day, pp.birthdate, @ApptDate) <= 112 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 7) as varchar(10)) + ' wks'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) <= day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) as varchar(10)) + ' mos'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) - 1 as varchar(10)) + ' mos'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) >= 2 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) as varchar(10)) + ' yrs'
ELSE '' END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply