PS Query service calc

Hi, a question re an expression added to calculate the total service between start and end dates.
Includes the expression SUM((TO_DATE(NVL(A.EFFDT_END_WA, SYSDATE),'YYYY-MM-DD')-TO_DATE(A.EFFDT_START_WA,'YYYY-MM-DD'))/365)...works fine Seeking some advice re adding to this expression so that employees that have an end date greater than system date have the service calculated to SYSDATE date only eg A.EFFDT_END_WA > SYSDATE, THEN SYSDATE. Thanks

@Michael-King Thanks Michael

@Kip Wainwright

I forgot one parenthesis
CASE WHEN (TO_DATE(NVL(A.EFFDT_END_WA, SYSDATE),'YYYY-MM-DD')) <---- that one

CASE WHEN (TO_DATE(NVL(A.EFFDT_END_WA, SYSDATE),'YYYY-MM-DD')) > TO_DATE(SYSDATE,'YYYY-MM-DD')
THEN SUM((TO_DATE(SYSDATE,'YYYY-MM-DD')-TO_DATE(A.EFFDT_START_WA,'YYYY-MM-DD'))/365)
ELSE SUM((TO_DATE(NVL(A.EFFDT_END_WA, SYSDATE),'YYYY-MM-DD')-TO_DATE(A.EFFDT_START_WA,'YYYY-MM-DD'))/365)
END

@Michael-King Thanks Michael that is what i am after.i do get a sql error when i run the query however, Code=907, Message=ORA-00907: missing right parenthesis (50,380)

@Kip Wainwright said in PS Query service calc:

A.EFFDT_END_WA > SYSDATE

This part is telling you that sometimes the end date is not populated.
(TO_DATE(NVL(A.EFFDT_END_WA, SYSDATE),'YYYY-MM-DD')

When it is not populated, use today's date. NVL = null value.
When it is populated, then used the end date.
The current query says give the days between the start date and the end date, if there is no end date, then use today's date.

The change you are asking to make is that sometimes the end date is future dated.
You only want the days between the start date and today, unless they have already left.

CASE WHEN (TO_DATE(NVL(A.EFFDT_END_WA, SYSDATE),'YYYY-MM-DD') > TO_DATE(SYSDATE,'YYYY-MM-DD')
THEN SUM((TO_DATE(SYSDATE,'YYYY-MM-DD')-TO_DATE(A.EFFDT_START_WA,'YYYY-MM-DD'))/365)
ELSE SUM((TO_DATE(NVL(A.EFFDT_END_WA, SYSDATE),'YYYY-MM-DD')-TO_DATE(A.EFFDT_START_WA,'YYYY-MM-DD'))/365)
END

If the end date is greater than today, then do the math on today's date.
Otherwise do the math on the end date.

@Nicole Apostola thanks Nicole..i will experiment with this

@Kip Wainwright You'll likely want to do a CASE-WHEN expression using this.

Something like CASE WHEN [end date greater than sysdate] THEN [the logic you want to use] ELSE [the other logic]