By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select *
into t
from (values
(123, '2019003', 1),
(123, '2019004', 1),
(123, '2019005', 2),
(123, '2019006', 2),
(123, '2019007', 3)
) v (person, period, Cost_Center)
5 rows affected
select t.*,
max(case when immediate_prev_cost_center <> cost_center then immediate_prev_cost_center
end) over (partition by person, cost_center, (seqnum - seqnum_2)
) as prev_cost_center
from (select t.*,
row_number() over (partition by person order by period) as seqnum,
row_number() over (partition by person, cost_center order by period) as seqnum_2,
lag(cost_center) over (partition by person order by period) as immediate_prev_cost_center
from t
) t;
| person | period | Cost_Center | seqnum | seqnum_2 | immediate_prev_cost_center | prev_cost_center |
|---|---|---|---|---|---|---|
| 123 | 2019003 | 1 | 1 | 1 | null | null |
| 123 | 2019004 | 1 | 2 | 2 | 1 | null |
| 123 | 2019005 | 2 | 3 | 1 | 1 | 1 |
| 123 | 2019006 | 2 | 4 | 2 | 2 | 1 |
| 123 | 2019007 | 3 | 5 | 1 | 2 | 2 |
Warning: Null value is eliminated by an aggregate or other SET operation.