I have this table:
ID Date
-----------------
1 1/1/2019
1 1/15/2019
Expected output:
ID DATE LEAD_DATE
-------------------------
1 1/1/2019 1/14/2019
1 1/15/2019 SYSDATE
SQL:
SELECT
*,
CASE
WHEN LEAD (a.date) OVER (PARTITION BY a.ID ORDER BY a.date) = TRUNC(a.date) THEN NULL
ELSE LEAD (a.date) OVER (PARTITION BY a.id ORDER BY a.date) - NUMTODSINTERVAL(1,'second')
END AS LEAD_DT
FROM a
Results:
ID DATE LEAD_DATE
-------------------------
1 1/1/2019 1/14/2019
1 1/15/2019
Can I add the system date when null
in the case expression?