SELECT EMPID
, RATE
,FSEQ
FDT , case when lead is null then '2500-12-31' else lead -1 end as ef_dt
FROM (SELECT EMPID, RATE, FSEQ, FDT , lead(FDT) over (partition by EMPID order by FDT, fseq) as lead --- this returns INT
SELECT
EMPID
, RATE
, FSEQ FDT
, case
when lead is null then '2500-12-31'
else DateAdd(DAY, -1, lead )
end as ef_dt
FROM
( SELECT
EMPID
, RATE
, FSEQ
, FDT
, lead(FDT)
over (partition by EMPID order by FDT, fseq) as lead --- this returns INT
FROM job j
) j1
WHERE
FDT <= getdate();