August 26, 2008 at 6:38 am
I start by saying that I am really new at SQL coding. My task is to identify all members who as of 1/1/2008 were between the ages of 9 and 12. So, I wrote the code below but for some reason in the result I get back there are members which will turn 12 during 2008 but way after 1/1/2008. I was thinking to convert the datediff into a number with 1 decimal to avoid the rounding up of the age. How do I do that formatting? Or, am I doing something wrong?
SELECT
MEM.MEMBER_ID,
MEM.MBR_BIRTH_DT,
DATEDIFF(YEAR,MEM.MBR_BIRTH_DT,'2008-01-01') AS AGE_YRS
FROM MEMBER MEM
JOIN MEMBER_ADDRESS MA
ON MA.MEMBER_ID=MEM.MEMBER_ID
JOIN LOAD_FACT_UTILIZATION_CLAIM_DETAIL LF
ON LF.MEMBER_ID = MEM.MEMBER_ID
LEFT JOIN DIM_PLAN_PRODUCT_ACCRUAL PP
ON PP.PLAN_PRODUCT_ACCRUAL_ID = LF.PLAN_PRODUCT_ACCRUAL_ID
WHERE MEM.MBR_BIRTH_DT BETWEEN '1995-01-01' AND '1998-12-31'
AND PP.PLAN_CD = 'xx'
GROUP BY
MEM.MEMBER_ID,
MEM.MBR_BIRTH_DT
August 26, 2008 at 1:22 pm
I think the problem lies with the '1998-12/31'....shouldn't it be 1998-01/01 ?
WHERE MEM.MBR_BIRTH_DT BETWEEN '1995-01-01' AND '1998-12-31'
Regards,
Terry
August 26, 2008 at 2:23 pm
There are two pieces here - one is the selection criteria and the other is the age computation.
The selection criteria is wrong I think: If you do not want 12 year olds included, you need to change the where clause to BETWEEN '1996-01-01' AND '1998-12-31'. Someone born in 1995 will turn 12 during the year 2007. Those born in 1996 will turn 12 during 2008.
The age computation using DATEDIFF can be deceiving. DATEDIFF only counts the changes between dateparts. For example, SELECT DATEDIFF(YEAR, '12/31/2007', '1/1/2008') returns 1 because the year changed between the two dates - it obviously doesn't indicate that a person born 12/31/2007 is 1 year old on 1/1/2008, even though you might think that at first. In order to get the "age" of a person, you have to do a little more math. See Lynn's article: http://www.sqlservercentral.com/articles/T-SQL/63351/ for a more extended discussion, a formula to compute age, and some alternative age computation formulas in the comments on the article.
Good Luck!
Chad
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply