asked on

SQL Server Age calculation

Hello all I am moving an MS Access app to SQL Server

I have several append queries that calculate age.

I am having some problems with this. I keep getting that Age, Date, and Nz are not recognized functions...

Original Query

INSERT INTO Person_RANK3_AGE_RX ( Person_ID )
SELECT Person_PHARM_TABLE.Person_ID
FROM Person_TABLE INNER JOIN ((DRUGS INNER JOIN Person_PHARM_TABLE ON DRUGS.DRUG_ID = Person_PHARM_TABLE.DRUG_ID) INNER JOIN AVOID_DRUGS ON DRUGS.DRUG_DESC = AVOID_DRUGS.Medicine_Name) ON Person_TABLE.Person_ID = PATIENT_PHARM_TABLE.Person_ID
WHERE (((AVOID_DRUGS.RISK)=3) AND ((Age(Nz([Person_TABLE].[DOB],DateAdd("yyyy",1,Date()))))>64))
GROUP BY Person_PHARM_TABLE.Person_ID, DRUGS.DRUG_ID;

What this query does is it looks at a risk associated with taking a specific drug frm the AVOID_DRUGs table. and then only looks for peopel older than 64. It retrieves the DOB from the Person_table.

Should I use something similar to Datediff...?

Scott Pletcher

Yes, in place of AGE() use DATEDIFF(). Not sure what "Nz" is, but my best guess is it checks for NULL; if so, it would be replaced by ISNULL(column, value-if-null).

Scott Pletcher

Hmm, even less sure about nz, but here goes; for example:

AND (DATEDIFF(YEAR, ISNULL([Person_TABLE].[DOB], GETDATE()), GETDATE()) > 64)

ASKER CERTIFIED SOLUTION

adwiseman

membership

This solution is only available to members.

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

Start Free Trial

Scott Pletcher

CORRECTION:

AND (DATEDIFF(YEAR, ISNULL([Person_TABLE].[DOB], GETDATE()), GETDATE()) - CASE WHEN DATEPART(DAYOFYEAR, [Person_TABLE].[DOB]) <= DATEPART(DAYOFYEAR, GETDATE()) THEN 0 ELSE 1 END > 64)

adwiseman

Scott's correction works with the exception of a Leapyear, Feb 29th, April 1st have the same DAYOFYEAR when one is a leapyear, and one is not.

adwiseman

AND (dbo.UFun_CMPC_AgeAtDate(ISNULL([Person_TABLE].[DOB], GETDATE()), GETDATE()) > 64)

Scott Pletcher

D'OH, yeah, forgot about leap years. Was trying to avoid string comparisons, but can't, I guess:

AND (DATEDIFF(YEAR, ISNULL([Person_TABLE].[DOB], GETDATE()), GETDATE()) - CASE WHEN CONVERT(CHAR(5), GETDATE(), 1) >= CONVERT(CHAR(5), [Person_TABLE].[DOB], 1) THEN 0 ELSE 1 END > 64)

Scott Pletcher

OOH, thinking more clearly now since I just had breakfast :-), the *much* better way to do this is:

WHERE (((AVOID_DRUGS.RISK)=3) AND ([Person_TABLE].[DOB] <= DATEADD(YEAR, -64, GETDATE())

Scott Pletcher

This allows the table column to be "pure" (no functions performed against it), which will allow an index to be used. Also, the other calculation is much easier and only has to be performed once.

adwiseman

I agree with Scott, this is your best option for determining of someone is under a given age.

WHERE (((AVOID_DRUGS.RISK)=3) AND ([Person_TABLE].[DOB] <= DATEADD(YEAR, -64, GETDATE())

This would make a good wheaties commercial

cubic22

ASKER

Well shoot....I just commented and accepted.... DOH...I forgot to refresh the browser first
.....

cubic22

ASKER

What should I do now?....

Scott Pletcher

That's up to you. If you want to, you could request cancelling the acceptance and selecting a different split/answer. If so, just post a follow-up request here and I will take care of it. Or, of course, you could leave as is if you are comfortable with the current status.

cubic22

ASKER

Let me test out the new way and I will get back to yall.

cubic22

ASKER

If its not too much trouble I would like to request a cancel so I can split the points.