So you main target is to get the latest_DND, Does this means that a DND goes through diffrent values? like at start it is 105 and at the current value is 4008. from this data, I assume that rec1 is representing the latest dnd, and dnd_no is repsenting the initial DND. If you only have to get the latest DND then why dont you use max window function on date and get the DND at max date?
Does this makes sense?
The actual condition is, for a particular DND value if the correspoding rec1 value is 0 then latest_dnd should be dnd only. Consider the set of values i have provided as sample.
For the record, dnd_no =100 the corresponding rec1 =200. So this time it searches for dnd_no = 200 and the corresponding rec1=300. again it searches for 300 in dnd column and the corresponding rec1=0, So the latest_dnd would be 300 for the record 100.
It should keep on seraching the column dnd to find its corresponding rec1 = 0. Then it stops there and should fetch the result where the dnd is becoming as zero. Hope you understood what i said here.
assuming your actual data is not based on increasing values you need to do a recursion starting with the LATEST_DND:
WITH RECURSIVE cte(DND_NO, REC1, LATEST_DND) AS
SELECT DND_NO, REC1, DND_NO AS LATEST_DND
WHERE REC1 = 0
SELECT t2.DND_NO, t2.REC1, cte.LATEST_DND
FROM cte JOIN tab AS t2
ON t2.REC1 = cte.DND_NO
SELECT * FROM cte;
What are you trying to calculate here:
CASE WHEN (A.ACT_DATE='2020-12-31' OR
(SUBSTR(A.ACT_DATE,4,2) < EXTRACT(DAY FROM A.PRD_END_DT)) OR
(SUBSTR(A.ACT_DATE,4,2) = EXTRACT(DAY FROM A.PRD_END_DT) AND SUBSTR(A.ACT_DATE,1,2) <= EXTRACT(MONTH FROM A.PRD_END_DT)))
THEN 'Y' ELSE 'N' END AS ACT_CURR_IND
What's the datatype of A.ACT_DATE?
Thanks for the suggestion.
The data type for A.ACT_DATE is VARCHAR. I don't know why they have defined a date column as varchar field. But we have to code according to that :)