I have the below table results.
Criteria:
Partition by CUSTID and ITEMID Where end_dt is not null If the RESP column is null then take the latest value that has an entry.
CUSTID ITEMID START_DT END_DT RESP START_CYCLE END_CYCLE
1 101 1/1/2019 4/1/2019 400 1/1/2019 1/12/2019
1 101 1/1/2019 4/1/2019 1/13/2019 1/18/2019
1 101 1/1/2019 4/1/2019 750 1/19/2019 2/15/2019
1 101 1/1/2019 4/1/2019 2/16/2019 4/1/2019
2 909 3/1/2019 444 3/1/2019 3/2/2019
2 909 3/1/2019 3/3/2019 3/10/2019
2 909 3/1/2019 767 3/11/2019 3/28/2019
2 909 3/1/2019 3/29/2019 12/31/3000
Expected Results:
CUSTID ITEMID START_DT END_DT RESP START_CYCLE END_CYCLE
1 101 1/1/2019 4/1/2019 400 1/1/2019 1/12/2019
1 101 1/1/2019 4/1/2019 1/13/2019 1/18/2019
1 101 1/1/2019 4/1/2019 750 1/19/2019 2/15/2019
1 101 1/1/2019 4/1/2019 750 2/16/2019 4/1/2019
2 909 3/1/2019 444 3/1/2019 3/2/2019
2 909 3/1/2019 3/3/2019 3/10/2019
2 909 3/1/2019 767 3/11/2019 3/28/2019
2 909 3/1/2019 3/29/2019 12/31/3000
The only row that is changing is
1 101 1/1/2019 4/1/2019 750 2/16/2019 4/1/2019
This row should not change, this is correct:
1 101 1/1/2019 4/1/2019 1/13/2019 1/18/2019
You have to check three things: resp is null, end_dt is not null and if this is the last row for this custid, itemid. Only in this case use last_value, like here, column resp2:
select custid, itemid, start_dt, end_dt, resp, start_cycle, end_cycle,
case when resp is null
and end_dt is not null
and lead(itemid) over (partition by custid, itemid order by start_cycle) is null
then last_value(resp) ignore nulls
over (partition by custid, itemid order by start_cycle)
else resp
end resp2
from t