By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
| ID | DT | FLG |
|---|---|---|
| x | 01-JAN-15 | Y |
| x | 01-JAN-14 | N |
| y | 01-JAN-16 | Y |
| y | 01-JAN-15 | N |
| y | 01-JAN-20 | N |
| z | 01-JAN-20 | Y |
| EMPID | EFFDT | (CASEWHENMAX(CASEWHENEFFDT<SYSDATETHENEFFDTEND)OVER(PARTITIONBYEMPID)=EFFDTTHEN'Y'ELSE'N'END) |
|---|---|---|
| x | 01-JAN-14 | N |
| x | 01-JAN-15 | Y |
| y | 01-JAN-15 | N |
| y | 01-JAN-16 | Y |
| y | 01-JAN-20 | N |
| z | 01-JAN-20 | N |