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