Forums / Categories / Help Using the Community / PS Query service calc expression

    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.

Log in to reply

Looks like your connection to Quest Oracle Community was lost, please wait while we try to reconnect.