asked on

Age at Time of Service - SQL 2005

I need help with the following field:

'Age @ TOS' = DATEDIFF (YY, patientprofile.birthdate, patientvisitprocs.dateofservicefrom),

PatientProfile.Birthdate - 01/01/2001
PatientVisitProcs.dateofservicefrom = 07/01/2001

In my report, I get a '0' as my Age @ TOS. What I would like to have is 7 months vs the '0' it is reporting. I would like to try accomplishing this without doing a function if possible. I know I used YY in my DATEDIFF. I only noticed this today because of a few patients in my DB were under 1 yr at the TOS.

ASKER CERTIFIED SOLUTION

TextReport

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeff S

ASKER

Hi David -

I noticed the second comma in Andrew's code and the ELSE .... however his technique got me going in the right direction.

I am now getting this error with the code in my snippet:

Server: Msg 245, Level 16, State 1, Line 115
Syntax error converting the varchar value '6 Months' to a column of data type int.

'Age @ TOS' = 
     	CASE WHEN DATEDIFF(YY,patientprofile.birthdate, patientvisitprocs.dateofservicefrom) <= 0
        THEN CAST(DATEDIFF(MM,patientprofile.birthdate, patientvisitprocs.dateofservicefrom)as varchar(10))+ ' Months'
        ELSE DATEDIFF(YY,patientprofile.birthdate, patientvisitprocs.dateofservicefrom)
        END,

Open in new window

Jeff S

ASKER

Ok I got it...

'Age @ TOS' = CASE
     	WHEN DATEDIFF(YY,patientprofile.birthdate, patientvisitprocs.dateofservicefrom) <= 0
        THEN CAST(DATEDIFF(MM,patientprofile.birthdate, patientvisitprocs.dateofservicefrom)as varchar(10))+ ' Months'
        ELSE CAST(DATEDIFF(YY,patientprofile.birthdate, patientvisitprocs.dateofservicefrom)as varchar(10))+ ' Yr'
        END,

Open in new window

Jeff S

ASKER

Thanks again

Jeff S

ASKER

Split points to be fair.

TextReport

Dear JeffSturgeon2002, you should be able to do this yourself by accepting 1 answer and assigning an assist.
Cheers, Andrew

Jeff S

ASKER

ya I did ...