vendredi 8 mai 2015

Want specific years between date joined and now

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'

Aucun commentaire:

Enregistrer un commentaire