CASE Statement Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CASE Statement Help

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-01-03 : 10:29:12

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) < 2 and day(pp.birthdate) > 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
WHERE pp.PatientProfileID = @PatientProfileId[/CODE]

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-01-03 : 11:39:41

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'

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-03 : 11:42:09

Is [400.PatientAge] name of a column in either PatientProfile or Guarantor table?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-01-03 : 11:45:41

No, it is not in either one. I had to code it into the Stored Procedure.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-03 : 11:55:14

You cannot use a new column like that. Try something like this:

SELECT
	*, -- Do not use '*'; this for example purposes only
	CASE 
		WHEN [400.PatientAge] > 18 AND [301.PatientSex] = 'M' THEN 'E'
		WHEN [400.PatientAge] > 18 AND [301.PatientSex] = 'F' THEN 'D'
		WHEN [400.PatientAge] BETWEEN 7 AND 18 AND [301.PatientSex] = 'M' THEN 'C'
		WHEN [400.PatientAge] BETWEEN 7 AND 18 AND [301.PatientSex] = 'F' THEN 'B'
		WHEN [400.PatientAge] BETWEEN 0 AND 6 THEN 'A'
		ELSE 'UNKNOWN'
	END AS [401.PatientAgeGroup] 
FROM
(
	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) < 2 and day(pp.birthdate) > 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
	WHERE	pp.PatientProfileID = @PatientProfileId
) AS Temp

Ahh crap, I jumped the gun. I thought '400.PatientAge' was a number. You can use my example to build on you or can use the current logic in your '400.PatientAge' case statement to build another case statement to get create '401.PatientAgeGroup'.

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-01-03 : 12:05:17

Yea ... It gives me a

Msg 245, Level 16, State 1, Line 9
Conversion failed when converting the varchar value '8 wks' to data type int.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources