trouble shooting data with temp
-
The following data keeps coming up with the error that there is an issue around the as in the alias for age...any thoughts?
Select
dbo.b_ENROLLMENT.MEMBER_DOB_DATE,
dbo.b_ENROLLMENT.EFF_DATE,
dbo.b_ENROLLMENT.MED_ID,
dbo.b_ENROLLMENT.PLAN_CODE as aid,
dbo.p_DMAS_MEDICAL_TRANSITION.recip as recipno,
dbo.p_DMAS_MEDICAL_TRANSITION.SEX as sex,
CAST(CASE WHEN (dbo.p_DMAS_MEDICAL_TRANSITION.SEX)='M' THEN 1
WHEN (dbo.p_DMAS_MEDICAL_TRANSITION.SEX)='F' THEN 0
END AS int) AS MALE,
case when (dbo.b_ENROLLMENT.PLAN_CODE)='450' or (dbo.b_ENROLLMENT.PLAN_CODE)='451' or (dbo.b_ENROLLMENT.PLAN_CODE)='452' and (temp.AGE ) > =18 then 'DA'
when (dbo.b_ENROLLMENT.PLAN_CODE)='450' or (dbo.b_ENROLLMENT.PLAN_CODE)='451' or (dbo.b_ENROLLMENT.PLAN_CODE)='452' and (temp.AGE ) <18 then 'DC'
when (dbo.b_ENROLLMENT.PLAN_CODE)='453' or (dbo.b_ENROLLMENT.PLAN_CODE)='454' and (temp.AGE) > =18 then 'AA'
when (dbo.b_ENROLLMENT.PLAN_CODE)='453' or (dbo.b_ENROLLMENT.PLAN_CODE)='454' and (temp.AGE ) <18 then 'AC'
/*else 'other'*/ end as aid
FROM ( dbo.b_ENROLLMENT
inner join dbo.p_DMAS_MEDICAL_TRANSITION
on dbo.p_DMAS_MEDICAL_TRANSITION.recip=dbo.b_ENROLLMENT.MED_ID
DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE( )) as age,temp.PLAN_CODE
)temp
where dbo.b_ENROLLMENT.EFF_DATE between '9/1/2011' and '11/1/2011'
and aid not in ('455','456','457','458','459')
/*transition refreshed around the 25th of the month*/
-
chalbert (5/22/2012)
The following data keeps coming up with the error that there is an issue around the as in the alias for age...any thoughts?Select
dbo.b_ENROLLMENT.MEMBER_DOB_DATE,
dbo.b_ENROLLMENT.EFF_DATE,
dbo.b_ENROLLMENT.MED_ID,
dbo.b_ENROLLMENT.PLAN_CODE as aid,
dbo.p_DMAS_MEDICAL_TRANSITION.recip as recipno,
dbo.p_DMAS_MEDICAL_TRANSITION.SEX as sex,
CAST(CASE WHEN (dbo.p_DMAS_MEDICAL_TRANSITION.SEX)='M' THEN 1
WHEN (dbo.p_DMAS_MEDICAL_TRANSITION.SEX)='F' THEN 0
END AS int) AS MALE,
case when (dbo.b_ENROLLMENT.PLAN_CODE)='450' or (dbo.b_ENROLLMENT.PLAN_CODE)='451' or (dbo.b_ENROLLMENT.PLAN_CODE)='452' and (temp.AGE ) > =18 then 'DA'
when (dbo.b_ENROLLMENT.PLAN_CODE)='450' or (dbo.b_ENROLLMENT.PLAN_CODE)='451' or (dbo.b_ENROLLMENT.PLAN_CODE)='452' and (temp.AGE ) <18 then 'DC'
when (dbo.b_ENROLLMENT.PLAN_CODE)='453' or (dbo.b_ENROLLMENT.PLAN_CODE)='454' and (temp.AGE) > =18 then 'AA'
when (dbo.b_ENROLLMENT.PLAN_CODE)='453' or (dbo.b_ENROLLMENT.PLAN_CODE)='454' and (temp.AGE ) <18 then 'AC'
/*else 'other'*/ end as aid
FROM ( dbo.b_ENROLLMENT
inner join dbo.p_DMAS_MEDICAL_TRANSITION
on dbo.p_DMAS_MEDICAL_TRANSITION.recip=dbo.b_ENROLLMENT.MED_ID
DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE( )) as age,temp.PLAN_CODE
)temp
where dbo.b_ENROLLMENT.EFF_DATE between '9/1/2011' and '11/1/2011'
and aid not in ('455','456','457','458','459')
/*transition refreshed around the 25th of the month*/
Because this line of code is part of the ON join condition:
DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE( )) as age,temp.PLAN_CODE
-
chalbert (5/22/2012)
The following data keeps coming up with the error that there is an issue around the as in the alias for age...any thoughts?Select
dbo.b_ENROLLMENT.MEMBER_DOB_DATE,
dbo.b_ENROLLMENT.EFF_DATE,
dbo.b_ENROLLMENT.MED_ID,
dbo.b_ENROLLMENT.PLAN_CODE as aid,
dbo.p_DMAS_MEDICAL_TRANSITION.recip as recipno,
dbo.p_DMAS_MEDICAL_TRANSITION.SEX as sex,
CAST(CASE WHEN (dbo.p_DMAS_MEDICAL_TRANSITION.SEX)='M' THEN 1
WHEN (dbo.p_DMAS_MEDICAL_TRANSITION.SEX)='F' THEN 0
END AS int) AS MALE,
case when (dbo.b_ENROLLMENT.PLAN_CODE)='450' or (dbo.b_ENROLLMENT.PLAN_CODE)='451' or (dbo.b_ENROLLMENT.PLAN_CODE)='452' and (temp.AGE ) > =18 then 'DA'
when (dbo.b_ENROLLMENT.PLAN_CODE)='450' or (dbo.b_ENROLLMENT.PLAN_CODE)='451' or (dbo.b_ENROLLMENT.PLAN_CODE)='452' and (temp.AGE ) <18 then 'DC'
when (dbo.b_ENROLLMENT.PLAN_CODE)='453' or (dbo.b_ENROLLMENT.PLAN_CODE)='454' and (temp.AGE) > =18 then 'AA'
when (dbo.b_ENROLLMENT.PLAN_CODE)='453' or (dbo.b_ENROLLMENT.PLAN_CODE)='454' and (temp.AGE ) <18 then 'AC'
/*else 'other'*/ end as aid
FROM ( dbo.b_ENROLLMENT
inner join dbo.p_DMAS_MEDICAL_TRANSITION
on dbo.p_DMAS_MEDICAL_TRANSITION.recip=dbo.b_ENROLLMENT.MED_ID
DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE( )) as age,temp.PLAN_CODE
)temp
where dbo.b_ENROLLMENT.EFF_DATE between '9/1/2011' and '11/1/2011'
and aid not in ('455','456','457','458','459')
/*transition refreshed around the 25th of the month*/
Not exactly sure what you are trying to but the from thing down there will not work. It is almost like you are trying to add columns inside the list of tables or something and then alias that????
Maybe something like this is what you are looking for??
Select
dbo.b_ENROLLMENT.MEMBER_DOB_DATE,
dbo.b_ENROLLMENT.EFF_DATE,
dbo.b_ENROLLMENT.MED_ID,
dbo.b_ENROLLMENT.PLAN_CODE as aid,
dbo.p_DMAS_MEDICAL_TRANSITION.recip as recipno,
dbo.p_DMAS_MEDICAL_TRANSITION.SEX as sex,
CAST(CASE WHEN (dbo.p_DMAS_MEDICAL_TRANSITION.SEX)='M' THEN 1
WHEN (dbo.p_DMAS_MEDICAL_TRANSITION.SEX)='F' THEN 0
END AS int) AS MALE,
case when (dbo.b_ENROLLMENT.PLAN_CODE)='450' or (dbo.b_ENROLLMENT.PLAN_CODE)='451' or (dbo.b_ENROLLMENT.PLAN_CODE)='452' and (temp.AGE ) > =18 then 'DA'
when (dbo.b_ENROLLMENT.PLAN_CODE)='450' or (dbo.b_ENROLLMENT.PLAN_CODE)='451' or (dbo.b_ENROLLMENT.PLAN_CODE)='452' and (temp.AGE ) <18 then 'DC'
when (dbo.b_ENROLLMENT.PLAN_CODE)='453' or (dbo.b_ENROLLMENT.PLAN_CODE)='454' and (temp.AGE) > =18 then 'AA'
when (dbo.b_ENROLLMENT.PLAN_CODE)='453' or (dbo.b_ENROLLMENT.PLAN_CODE)='454' and (temp.AGE ) <18 then 'AC'
/*else 'other'*/ end as aid
,DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE( )) as age, dbo.b_ENROLLMENT.PLAN_CODE
FROM dbo.b_ENROLLMENT
inner join dbo.p_DMAS_MEDICAL_TRANSITION
on dbo.p_DMAS_MEDICAL_TRANSITION.recip=dbo.b_ENROLLMENT.MED_ID
where dbo.b_ENROLLMENT.EFF_DATE between '9/1/2011' and '11/1/2011'
and aid not in ('455','456','457','458','459')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Holy parentheses Batman! I cleaned this up a bit and bolded the problem section.
Selectdbo.b_ENROLLMENT.MEMBER_DOB_DATE,
dbo.b_ENROLLMENT.EFF_DATE,
dbo.b_ENROLLMENT.MED_ID,
dbo.b_ENROLLMENT.PLAN_CODE as aid,
dbo.p_DMAS_MEDICAL_TRANSITION.recip as recipno,
dbo.p_DMAS_MEDICAL_TRANSITION.SEX as sex,
CASE
WHEN dbo.p_DMAS_MEDICAL_TRANSITION.SEX = 'M' THEN 1
WHEN dbo.p_DMAS_MEDICAL_TRANSITION.SEX = 'F' THEN 0
END AS MALE,
CASE
WHEN dbo.b_ENROLLMENT.PLAN_CODE = '450' OR dbo.b_ENROLLMENT.PLAN_CODE = '451' OR dbo.b_ENROLLMENT.PLAN_CODE = '452' AND temp.AGE >= 18 THEN 'DA'
WHEN dbo.b_ENROLLMENT.PLAN_CODE = '450' OR dbo.b_ENROLLMENT.PLAN_CODE = '451' OR dbo.b_ENROLLMENT.PLAN_CODE = '452' AND temp.AGE < 18 then 'DC'
WHEN dbo.b_ENROLLMENT.PLAN_CODE = '453' OR dbo.b_ENROLLMENT.PLAN_CODE = '454' AND temp.AGE >= 18 then 'AA'
WHEN dbo.b_ENROLLMENT.PLAN_CODE = '453' OR dbo.b_ENROLLMENT.PLAN_CODE = '454' AND temp.AGE < 18 then 'AC'
END AS aid
FROM dbo.b_ENROLLMENT
INNER JOIN dbo.p_DMAS_MEDICAL_TRANSITION
ON dbo.p_DMAS_MEDICAL_TRANSITION.recip = dbo.b_ENROLLMENT.MED_ID
DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE( )) as age,temp.PLAN_CODE)temp
WHERE dbo.b_ENROLLMENT.EFF_DATE BETWEEN '9/1/2011' AND '11/1/2011'
AND aid NOT IN ('455','456','457','458','459')
/*transition refreshed around the 25th of the month*/
So this part: "DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE( )) as age,temp.PLAN_CODE)temp" is misplaced, and I'm not really sure where it is supposed to go.
Jared
CE - Microsoft -
SQLKnowItAll (5/22/2012)
So this part: "DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE( )) as age,temp.PLAN_CODE)temp" is misplaced, and I'm not really sure where it is supposed to go.I had the same problem:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Thanks...but I keep getting the errors:
Msg 207, Level 16, State 1, Line 22
Invalid column name 'aid'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'aid'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'aid'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'aid'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'aid'.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "temp.AGE" could not be bound.
Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "temp.AGE" could not be bound.
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "temp.AGE" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "temp.AGE" could not be bound.
-
chalbert (5/22/2012)
Thanks...but I keep getting the errors:Msg 207, Level 16, State 1, Line 22
Invalid column name 'aid'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'aid'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'aid'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'aid'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'aid'.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "temp.AGE" could not be bound.
Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "temp.AGE" could not be bound.
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "temp.AGE" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "temp.AGE" could not be bound.
You have two columns in your query aliased as aid.
If you look at the query I posted you will have to replace temp.AGE with actual calculation.
DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE( ))
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
chalbert (5/22/2012)
Thanks...but I keep getting the errors:Msg 207, Level 16, State 1, Line 22
Invalid column name 'aid'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'aid'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'aid'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'aid'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'aid'.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "temp.AGE" could not be bound.
Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "temp.AGE" could not be bound.
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "temp.AGE" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "temp.AGE" could not be bound.
1. You have 2 columns aliased as aid
2. You cannot reference an alias in the NOT IN, use dbo.b_ENROLLMENT.PLAN_CODE NOT IN (...)
3. temp.AGE is not a column in a table. You must create your temp table, which you have not done.
Jared
CE - Microsoft -
Where should datediff go?
-
chalbert (5/22/2012)
Where should datediff go?Try this:
Selectdbo.b_ENROLLMENT.MEMBER_DOB_DATE,
dbo.b_ENROLLMENT.EFF_DATE,
dbo.b_ENROLLMENT.MED_ID,
dbo.b_ENROLLMENT.PLAN_CODE as aid1,
dbo.p_DMAS_MEDICAL_TRANSITION.recip as recipno,
dbo.p_DMAS_MEDICAL_TRANSITION.SEX as sex,
CASE
WHEN dbo.p_DMAS_MEDICAL_TRANSITION.SEX = 'M' THEN 1
WHEN dbo.p_DMAS_MEDICAL_TRANSITION.SEX = 'F' THEN 0
END AS MALE,
CASE
WHEN dbo.b_ENROLLMENT.PLAN_CODE = '450' OR dbo.b_ENROLLMENT.PLAN_CODE = '451' OR dbo.b_ENROLLMENT.PLAN_CODE = '452' AND DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE( )) >= 18 THEN 'DA'
WHEN dbo.b_ENROLLMENT.PLAN_CODE = '450' OR dbo.b_ENROLLMENT.PLAN_CODE = '451' OR dbo.b_ENROLLMENT.PLAN_CODE = '452' AND DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE( )) < 18 then 'DC'
WHEN dbo.b_ENROLLMENT.PLAN_CODE = '453' OR dbo.b_ENROLLMENT.PLAN_CODE = '454' AND DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE( )) >= 18 then 'AA'
WHEN dbo.b_ENROLLMENT.PLAN_CODE = '453' OR dbo.b_ENROLLMENT.PLAN_CODE = '454' AND DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE( )) < 18 then 'AC'
END AS aid
FROM dbo.b_ENROLLMENT
INNER JOIN dbo.p_DMAS_MEDICAL_TRANSITION
ON dbo.p_DMAS_MEDICAL_TRANSITION.recip = dbo.b_ENROLLMENT.MED_ID
WHERE dbo.b_ENROLLMENT.EFF_DATE BETWEEN '9/1/2011' AND '11/1/2011'
AND dbo.b_ENROLLMENT.PLAN_CODE NOT IN ('455','456','457','458','459')
/*transition refreshed around the 25th of the month*/
Jared
CE - Microsoft -
chalbert (5/22/2012)
Where should datediff go?Like this???
Select
dbo.b_ENROLLMENT.MEMBER_DOB_DATE,
dbo.b_ENROLLMENT.EFF_DATE,
dbo.b_ENROLLMENT.MED_ID,
dbo.b_ENROLLMENT.PLAN_CODE as aid,
dbo.p_DMAS_MEDICAL_TRANSITION.recip as recipno,
dbo.p_DMAS_MEDICAL_TRANSITION.SEX as sex,
CASE WHEN (dbo.p_DMAS_MEDICAL_TRANSITION.SEX)='M' THEN 1
WHEN (dbo.p_DMAS_MEDICAL_TRANSITION.SEX)='F' THEN 0
END AS MALE,
case
when (dbo.b_ENROLLMENT.PLAN_CODE)='450' or (dbo.b_ENROLLMENT.PLAN_CODE)='451' or (dbo.b_ENROLLMENT.PLAN_CODE)='452' and (DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE())) > =18 then 'DA'
when (dbo.b_ENROLLMENT.PLAN_CODE)='450' or (dbo.b_ENROLLMENT.PLAN_CODE)='451' or (dbo.b_ENROLLMENT.PLAN_CODE)='452' and (DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE())) <18 then 'DC'
when (dbo.b_ENROLLMENT.PLAN_CODE)='453' or (dbo.b_ENROLLMENT.PLAN_CODE)='454' and (DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE())) > =18 then 'AA'
when (dbo.b_ENROLLMENT.PLAN_CODE)='453' or (dbo.b_ENROLLMENT.PLAN_CODE)='454' and (DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE())) <18 then 'AC'
end as SomeNameOtherThan_aid,
DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE()) as age, dbo.b_ENROLLMENT.PLAN_CODE
FROM dbo.b_ENROLLMENT
inner join dbo.p_DMAS_MEDICAL_TRANSITIONon dbo.p_DMAS_MEDICAL_TRANSITION.recip=dbo.b_ENROLLMENT.MED_ID
where dbo.b_ENROLLMENT.EFF_DATE between '9/1/2011' and '11/1/2011'
and dbo.b_ENROLLMENT.PLAN_CODE not in ('455','456','457','458','459')
LOL looks like you got identical answers...and mine was second in line. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
chalbert (5/22/2012)
Where should datediff go?Two others gave you good answers, mine would have been "I don't know, this is your code. You tell us." 😉
-
chalbert's code
DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE()) as ageThis doesn't compute someone's age. It needs a formula like:
Age =
CASE
WHEN DATEPART(DAY, dbo.b_ENROLLMENT.MEMBER_DOB_DATE) > DATEPART(DAY, GETDATE())
THEN DATEDIFF(MONTH, dbo.b_ENROLLMENT.MEMBER_DOB_DATE, GETDATE()) - 1
ELSE DATEDIFF(MONTH, dbo.b_ENROLLMENT.MEMBER_DOB_DATE, GETDATE())
END / 12
See http://www.sqlteam.com/article/datediff-function-demystified
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
All hail the SS Carpal Tunnel....she works she works!!!
-
SQL Kiwi (5/22/2012)
chalbert's code
DATEDIFF(YY,dbo.b_ENROLLMENT.MEMBER_DOB_DATE,GETDATE()) as ageThis doesn't compute someone's age. It needs a formula like:
Age =
CASE
WHEN DATEPART(DAY, dbo.b_ENROLLMENT.MEMBER_DOB_DATE) > DATEPART(DAY, GETDATE())
THEN DATEDIFF(MONTH, dbo.b_ENROLLMENT.MEMBER_DOB_DATE, GETDATE()) - 1
ELSE DATEDIFF(MONTH, dbo.b_ENROLLMENT.MEMBER_DOB_DATE, GETDATE())
END / 12
See http://www.sqlteam.com/article/datediff-function-demystified
I wasn't even going to mention that Paul, there look to be so many other possible issues with this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply