select caseid,stepid,actiondate,lead(stepid) over (partition by caseid orderby actiondate) nextstepid, lead(actiondate) over (partition by caseid orderby actiondate) nextactiondate, lag(stepid) over (partition by caseid orderby actiondate) prestepid, lag(actiondate) over (partition by caseid orderby actiondate) preactiondate from lead_table
select caseid,stepid,actiondate,nextactiondate,nextactiondate-actiondate datebetween from ( select caseid,stepid,actiondate,lead(stepid) over (partition by caseid orderby actiondate) nextstepid, lead(actiondate) over (partition by caseid orderby actiondate) nextactiondate, lag(stepid) over (partition by caseid orderby actiondate) prestepid, lag(actiondate) over (partition by caseid orderby actiondate) preactiondate from lead_table)