Want specific years between date joined and now

Question

I am trying to get the members of a company that qualify for 'EMERITUS' status.

To qualify, one must be a member for 35 years from the date joined 'JOIN_DATE' and must be >=65 years of age to qualify 'BIRTH_DATE'. I want to see >= 2015 under the 'EMERITUS' column. Does this query make sense?

SELECT 
  N.ID, N.FULL_NAME, N.MEMBER_TYPE,
  N.JOIN_DATE,DA.BIRTH_DATE,
  (SELECT CASE 
     WHEN DATEDIFF(YEAR,N.JOIN_DATE,GETDATE()) + 35 > DATEDIFF(YEAR,DA.BIRTH_DATE,GETDATE()) + 65 
       THEN CONVERT(VARCHAR(4),YEAR(N.JOIN_DATE) + 35)
     WHEN DATEDIFF(YEAR,N.JOIN_DATE,GETDATE()) + 35 < DATEDIFF(YEAR,DA.BIRTH_DATE,GETDATE()) + 65 
       THEN CONVERT(VARCHAR(4),YEAR(DA.BIRTH_DATE) + 65)
     ELSE NULL
   END) AS 'EMERITUS'

Can you please explain the problem you are currently facing with this query? — Nivs, May 08 '15 at 16:39
It would be much clearer if you included both some sample data and what your query results should look like> — Philip Kelley, May 08 '15 at 16:39
I tossed in my own random data into sqlfiddle: http://sqlfiddle.com/#!6/dee06/1/0 My guess is that you need to split out your 2 whens into 2 columns and set a where statement to require both be <= CURRENT_YR to have emeritus status currently but it's hard to say. — clesiemo3, May 08 '15 at 16:47
This will not calculate age the same way Western civilization calculates age. It's simply calculating the difference in the year. `DATEDIFF(YEAR,N.JOIN_DATE,GETDATE())` is equivalent to `YEAR(GETDATE()) - YEAR(N.JOIN_DATE)`. [Here](http://stackoverflow.com/questions/57599/how-to-calculate-age-in-t-sql-with-years-months-and-days) is how you need to calculate age in years if you want to be sensitive to anniversary dates like birthdays. — Bacon Bits, May 08 '15 at 16:56
I am pretty much trying to get the projected number of years, where a member must be >=65 years of age and must have been a member for more than >=35 years to qualify. — BI_frog, May 08 '15 at 16:57

score 0 · Accepted Answer · answered May 08 '15 at 19:02

Based upon the comments above it looks like you are on the right track.

Using the below SQL (with example in SQLFiddle listed) you should be able to get the year they will be EMERITUS and the number of years until EMERITUS.

select N_sub.*
,case when DATEDIFF(d,GETDATE(),N_sub.EMERITUS)/365.0 > 0 
    then DATEDIFF(d,GETDATE(),N_sub.EMERITUS)/365.0 
    else 0 
    end YEARS_UNTIL_EMERITUS
from(select N."ID"
        ,N.FULL_NAME
        ,N.MEMBER_TYPE
        ,N.JOIN_DATE
        ,N.BIRTH_DATE
        , (select case 
        when DATEDIFF (d,N.JOIN_DATE, GETDATE ())/365 + 35 > DATEDIFF(d,N.BIRTH_DATE, GETDATE ())/365 + 65 
            then CONVERT(VARCHAR(10),DATEADD(year,35,N.BIRTH_DATE),110)
        when DATEDIFF (d,N.JOIN_DATE, GETDATE ())/365 + 35 < DATEDIFF(d,N.BIRTH_DATE, GETDATE ())/365 + 65 
            then CONVERT(VARCHAR(10),DATEADD(year,65,N.BIRTH_DATE),110) 
        else null 
        end) AS 'EMERITUS'
from N
) N_sub

SQL Fiddle: http://sqlfiddle.com/#!6/e464cc/7

With this query it is a bit better than just raw comparing the years as it goes by # of days divided by 365. Logic could be added to account for leap years. The results will show the date they get Emeritus and the number of years until they would get it. 0 if == 0 or negative.

Want specific years between date joined and now

1 Answers1