PS Query service calc expression

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

Hello
If you're looking to modify your expression to calculate the total service between start and end dates, but if the end date is in the future, you want to calculate it only up to the current system date (SYSDATE). Here's how you can adjust the expression:
SUM(
CASE
WHEN TO_DATE(A.EFFDT_END_WA, 'YYYY-MM-DD') > SYSDATE
THEN (TO_DATE(SYSDATE, 'YYYY-MM-DD') - TO_DATE(A.EFFDT_START_WA, 'YYYY-MM-DD')) / 365
ELSE (TO_DATE(NVL(A.EFFDT_END_WA, SYSDATE), 'YYYY-MM-DD') - TO_DATE(A.EFFDT_START_WA, 'YYYY-MM-DD')) / 365
END
)

This adjusted expression uses a CASE statement to check if the end date (A.EFFDT_END_WA) is greater than the current system date (SYSDATE). If it is, it calculates the service based on the difference between the current system date (SYSDATE) and the start date (A.EFFDT_START_WA). If the end date is not in the future, it calculates the service based on the difference between the end date and the start date. The SUM function then adds up these calculated values.

Hope it helps you.