CASE Statement Help 2

[Tech2377]

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

Any help is appreciated!

Declare @ApptDate datetime
Select @ApptDate = a.ApptStart
FROM    Appointments a
WHERE   a.AppointmentsId = 1095

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 = 319

I tried using this:

'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'.

 

[gmmastros]

You're getting that error message because the 400.PatientAge column doesn't exist yet. You have 2 choices to make this work. You can include the big ugly case statement that you use to calculate age in each condition of your patient age group column, OR you can take your existing query and make it a derived table.

SELECT  [290.PatientName],
        [291.PatLast],
        [292.PatFirst],
        [293.PatMiddle],
        [294.PatientAddr1],
        [295.PatientAddr2],
        [296.PatientCity],
        [297.PatientState],
        [298.PatientZip],
		[299.PatientCountry],
        [300.PatientBirthdate],
        [301.PatientSex],
        [302.PatientPhone1],
        [303.PatientSSN],
        [304.PatOccupation],
        [305.PatSchool],
        [306.PatBudget],
        [307.PatientSameAsGuarantor],
        [308.PatSuffix],
        [400.PatientAge],
        [401.PatientAgeGroup] =
        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 
FROM    ([!]Copy/Paste Your Original Query Here[/!]) As SomeAlias

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Rofeu]

Hi,

those labels are only introduced in the query itself, so you can't reference them there. You could wrap the sql you have in anothere select i.e.:

SELECT X.*, '401.PatientAgeGroup' = 
CASE 
WHEN X.[400.PatientAge] > 18 AND X.[301.PatientSex] = 'M' THEN 'E' etc.......
ELSE 'UNKNOWN'
END 

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 = 319) AS X;

Hope this helps.

Cheers,

Roel

ps I didn't compile any of the code

 

[Tech2377]

George,

Your help is deeply appreciated!

My new Code:

Declare @ApptDate datetime
Select @ApptDate = a.ApptStart
FROM    Appointments a
WHERE   a.AppointmentsId = 1095

SELECT  [290.PatientName],
        [291.PatLast],
        [292.PatFirst],
        [293.PatMiddle],
        [294.PatientAddr1],
        [295.PatientAddr2],
        [296.PatientCity],
        [297.PatientState],
        [298.PatientZip],
        [299.PatientCountry],
        [300.PatientBirthdate],
        [301.PatientSex],
        [302.PatientPhone1],
        [303.PatientSSN],
        [304.PatOccupation],
        [305.PatSchool],
        [306.PatBudget],
        [307.PatientSameAsGuarantor],
        [308.PatSuffix],
        [400.PatientAge],
        [401.PatientAgeGroup] =
        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 
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 = 319) AS AgeGrouping

Is now getting this:

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

 

[gmmastros]

You're getting that error because I was not careful enough with my advice.

Take a close look at the outer query. You are treating [400.PatientAge] as though it were an integer. However, in the inner query, you are adding strings to the value, like 'mos', 'yrs', 'wks', etc....

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jdaman]

Since you are doing numeric comparisons in your case statement against [400.PatientAge] I would assume that you have non-numeric values stored in that column.

 

[Tech2377]

George,

How can I step around this? Convert it into a Varchar? If so, how can I do that? Sorry for the ignorance and thankyou all for explaining this so well. Your help is truly appreciated.

 

[jdaman]

I would suggest storing the information in a numeric data type.

Example:
1yr = 1
2mo = (2*30)/365
8wks = (8*7)/365

 

[SQLDenis]

In a properly designed DB you would have date of birth and date of visit, a datediff between the two will give you the age in weeks, moths whatever you want

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jdaman]

Running your [400.PatientAge] in your derived table through a case statement similar to this would pass a numeric value to your case statement in your select statement:

declare @a table ( age varchar(10) )
insert @a ( age )
select '1 yr' union all
select '3 mo' union all
select '10 wks'

select case when right(age, charindex(' ', age)) = 'yr'
then left(age, charindex(' ', age))*1
when right(age, charindex(' ', age)) = 'mo'
then Cast((left(age, charindex(' ', age))*30) as float)/365
when right(age, charindex(' ', age)) = 'wks'
then Cast((left(age, charindex(' ', age))*7) as float)/365
end
from @a

 

[gmmastros]

I think there are deeper issues here. Specifically, how well have you tested your age calculation? Whenever I see 365.25 in an age calculation, I get nervous.

Try this query...

[COLOR=blue]SELECT[/color]  [COLOR=red]'290.PatientName'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.First,[COLOR=red]''[/color]) + [COLOR=red]' '[/color] + [COLOR=#FF00FF]isnull[/color](pp.Middle,[COLOR=red]''[/color]) + [COLOR=red]' '[/color] + [COLOR=#FF00FF]isnull[/color](pp.Last,[COLOR=red]''[/color])+ [COLOR=red]' '[/color] + [COLOR=#FF00FF]isnull[/color](pp.Suffix,[COLOR=red]''[/color]),
        [COLOR=red]'291.PatLast'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.Last,[COLOR=red]''[/color]),
        [COLOR=red]'292.PatFirst'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.First,[COLOR=red]''[/color]),
        [COLOR=red]'293.PatMiddle'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.Middle,[COLOR=red]''[/color]),
        [COLOR=red]'294.PatientAddr1'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.Address1,[COLOR=red]''[/color]),
        [COLOR=red]'295.PatientAddr2'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.Address2,[COLOR=red]''[/color]),
        [COLOR=red]'296.PatientCity'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.City,[COLOR=red]''[/color]),
        [COLOR=red]'297.PatientState'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.State,[COLOR=red]''[/color]),
        [COLOR=red]'298.PatientZip'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.Zip,[COLOR=red]''[/color]),
        [COLOR=red]'299.PatientCountry'[/color] = [COLOR=#FF00FF]ISNULL[/color](pp.Country,[COLOR=red]''[/color]),
        [COLOR=red]'300.PatientBirthdate'[/color] = pp.Birthdate,
        [COLOR=red]'301.PatientSex'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.Sex,[COLOR=red]''[/color]),
        [COLOR=red]'302.PatientPhone1'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.Phone1,[COLOR=red]''[/color]),
        [COLOR=red]'303.PatientSSN'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.SSN,[COLOR=red]''[/color]),
        [COLOR=red]'304.PatOccupation'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.EmpOccup,[COLOR=red]''[/color]),
        [COLOR=red]'305.PatSchool'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.MedicalRecordNumber,[COLOR=red]''[/color]),
        [COLOR=red]'306.PatBudget'[/color]=[COLOR=#FF00FF]IsNull[/color](g.Budget,0),
        [COLOR=red]'307.PatientSameAsGuarantor'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.PatientSameAsGuarantor,0),
        [COLOR=red]'308.PatSuffix'[/color]=[COLOR=#FF00FF]IsNull[/color](pp.Suffix,[COLOR=red]''[/color]),
        [COLOR=red]'400.PatientAge'[/color] = [COLOR=blue]CASE[/color]    [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]day[/color], pp.birthdate, @ApptDate) <= 6 [COLOR=blue]THEN[/color] [COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]day[/color], pp.birthdate, @ApptDate) [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](10)) + [COLOR=red]' dys'[/color]
            [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]day[/color], pp.birthdate, @ApptDate) <= 112 [COLOR=blue]THEN[/color] [COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]floor[/color]([COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]day[/color], pp.birthdate, @ApptDate) [COLOR=blue]as[/color] [COLOR=blue]decimal[/color]) / 7) [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](10)) + [COLOR=red]' wks'[/color]
            [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]floor[/color]([COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]day[/color], pp.birthdate, @ApptDate) [COLOR=blue]as[/color] [COLOR=blue]decimal[/color]) / 365.25) < 2 and [COLOR=#FF00FF]day[/color](pp.birthdate) <= [COLOR=#FF00FF]day[/color](@ApptDate) [COLOR=blue]THEN[/color] [COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]month[/color],pp.birthdate, @ApptDate) [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](10)) + [COLOR=red]' mos'[/color]
            [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]floor[/color]([COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]day[/color], pp.birthdate, @ApptDate) [COLOR=blue]as[/color] [COLOR=blue]decimal[/color]) / 365.25) < 2 and [COLOR=#FF00FF]day[/color](pp.birthdate) > [COLOR=#FF00FF]day[/color](@ApptDate) [COLOR=blue]THEN[/color] [COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]month[/color],pp.birthdate, @ApptDate) - 1 [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](10)) + [COLOR=red]' mos'[/color]
            [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]floor[/color]([COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]day[/color], pp.birthdate, @ApptDate) [COLOR=blue]as[/color] [COLOR=blue]decimal[/color]) / 365.25) >= 2 [COLOR=blue]THEN[/color] [COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]floor[/color]([COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]day[/color], pp.birthdate, @ApptDate) [COLOR=blue]as[/color] [COLOR=blue]decimal[/color]) / 365.25) [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](10)) + [COLOR=red]' yrs'[/color]
            [COLOR=blue]ELSE[/color] [COLOR=red]''[/color] [COLOR=blue]END[/color],
        [COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=#FF00FF]Year[/color](@ApptDate - pp.birthdate - 1) % 100 < 7 [COLOR=blue]Then[/color] [COLOR=red]'A'[/color]
             [COLOR=blue]When[/color] [COLOR=#FF00FF]Year[/color](@ApptDate - pp.birthdate - 1) % 100 < 18 And pp.Sex = [COLOR=red]'F'[/color] [COLOR=blue]Then[/color] [COLOR=red]'B'[/color]
             [COLOR=blue]When[/color] [COLOR=#FF00FF]Year[/color](@ApptDate - pp.birthdate - 1) % 100 < 18 And pp.Sex = [COLOR=red]'M'[/color] [COLOR=blue]Then[/color] [COLOR=red]'C'[/color]
             [COLOR=blue]When[/color] pp.Sex = [COLOR=red]'F'[/color] [COLOR=blue]Then[/color] [COLOR=red]'D'[/color]
             [COLOR=blue]When[/color] pp.Sex = [COLOR=red]'M'[/color] [COLOR=blue]Then[/color] [COLOR=red]'E'[/color]
             [COLOR=blue]Else[/color] [COLOR=red]'Unknown'[/color] [COLOR=blue]End[/color] [COLOR=blue]As[/color] [401.PatientAgeGroup]
[COLOR=blue]FROM[/color]    PatientProfile pp
        [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Guarantor g [COLOR=blue]ON[/color] pp.GuarantorId = g.GuarantorId
[COLOR=blue]WHERE[/color]    pp.PatientProfileID = 319

Make sure you test it pretty good for the age stuff.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Tech2377]

George -

Today, you are my hero! It works like a charm and I am truly grateful for your time and help. I would also like to thank anyone else who gave their time to help.