Query help - calculating Age

[jeffsturgeon2002]

I am using the following code and the 'PatientAge' is not returning a value ... confused. Any insight is appreciated...

Declare @ApptDate DATETIME
DECLARE @AppointmentsId INT

Select @ApptDate = a.ApptStart
FROM    Appointments a
WHERE   a.AppointmentsId = @AppointmentsId

SELECT  pp.First, pp.Last, pp.PatientId, a.ApptStart, df.ListName AS Resource, 
	CONVERT(varchar,pp.Birthdate,101) AS Birthdate, 
	ISNULL(pp.sex,'')AS Sex,
	ISNULL(SUBSTRING(pp.SSN,1,3) +  '-' + SUBSTRING(pp.SSN,4,2) + '-' + SUBSTRING(pp.SSN,6,4),'')AS SSN,
	[COLOR=red]'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 [/color]

FROM    Appointments a 
	INNER JOIN DoctorFacility df ON a.ResourceId = df.DoctorFacilityId 
	INNER JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId

WHERE	a.AppointmentsId = 974

Jeff

SELECT * FROM users WHERE clue > 0

 

[gmmastros]

This thread thread183-1186615 has a nice little function that may be helpful.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom