Sql case multiple conditions

[soldat452002]

Hello
I choose a CASE to look at 3 things:

TERMINATIONREASONCODE <> COMPLETE
LICENSE < CURRENT_DATE


THE HIGHEST DATE OF THESE TWO (DATEEFF > COMPLETIONDATE. Im getting stuck here. Any suggestions I will appreciate


SELECT P.PRODUCERID, C.CONTRACTID, TERMINATIONREASONCODE, to_char(to_date(DateTERM, 'yyyy/mm/dd'), 'mm/dd/yyyy') AS "LIC Expiration", to_char(to_date(E.COMPLETIONDATE, 'yyyy/mm/dd'), 'mm/dd/yyyy') AS "trAINING DATE",
to_char(to_date(CV.DATEEFF, 'yyyy/mm/dd'), 'mm/dd/yyyy') AS "STATUS EFF",
CASE
WHEN CV.TERMINATIONREASONCODE <> 'COMPLETE' THEN 'NOT ELIGIBLE TO SELL'
WHEN LV.DATETERM < to_char(current_date,'YYYYMMDD') THEN 'NOT ELIGIBLE TO SELL'
WHEN Cv.DateEff > E.COMPLETIONDATE THEN 'RETURN HIGHEST DATE OF THESE TWO FIELDS'
ELSE 'ELIGIBLE TO SELL'
END AS "ELIGIBLE TO SELL STATUS"



FROM PRODUCER P
INNER JOIN PRODUCERVER PV ON PV.PRODUCERNO = P.PRODUCERNO
INNER JOIN Contract C on C.ProducerNo = P.ProducerNo
INNER JOIN ContractVer CV on CV.ContractNo = C.ContractNo
LEFT JOIN License L on L.ProducerNo = P.ProducerNo
LEFT JOIN LicenseVer LV on LV.LicenseNo = L.LicenseNo
LEFT JOIN Education E on E.ProducerNo = P.ProducerNo
INNER JOIN JURISDICTION juris ON L.jurisdictionno = juris.jurisdictionno

WHERE CV.PROSTA = 1 AND CONTRACTTYPE IN ('Selling Agent', 'Producer') AND L.JURISDICTIONNO = E.JURISDICTIONNO
AND PV.PROSTA = 1
AND LV.PROSTA = 1
AND E.BUSINESSUNIT = 'FDD' AND EDUCATIONCATEGORY = 'Product'
AND AGREEMENT = '2019' AND to_date(cv.DateExp, 'yyyymmdd') > SYSDATE AND to_date(Lv.DateExp, 'yyyymmdd') > SYSDATE

[ranman256]

....wait...is this for Access sql?
or
SQL server sql?

[Minty]

This looks like T SQL rather than Access.

Why are you messing around with the dates so much? Simply Cast them into the format you want.
You certainly shouldn't need two functions to get them into the format you want. The performance hit will be huge.

Anyway to answer your question

CASE WHEN Cv.DateEff > E.COMPLETIONDATE THEN Cv.Dateeff ELSE E.COMPLETIONDATE END as HighestDate

You may want to use >= to determine what happens in the event of a tie.