SQL Lag distinct - to the previous different value

Question

I have the following simplified by many columns table.

 Person     Period           Cost_Center Previous_Cost_Center
----------- ---------------- ----------- ----------------------
123         2019003          1            
123         2019004          1            
123         2019005          2            
123         2019006          2            
123         2019007          3

For each Person, I would like to find his Previous Cost Center and save it in the corresponding column. The previous Cost Center has to be different from the current one.

What I would like to get:

Person      Period           Cost_Center Previous_Cost_Center
----------- ---------------- ----------- ---------------------
123         2019003          1           NULL 
123         2019004          1           NULL 
123         2019005          2           1 
123         2019006          2           1                     <----- Problematic row 
123         2019007          3           2

Standard LAG() function over the period would actually output:

Person      Period           Cost_Center Previous_Cost_Center
----------- ---------------- ----------- ---------------------
123         2019003          1           NULL 
123         2019004          1           NULL 
123         2019005          2           1 
123         2019006          2           2                     <----- Problematic row 
123         2019007          3           2

What I would like to have in a problematic row is the last different Cost_Center value which is 1 and not 2. What I thought of using is to check if the previous Cost_Center is different:

          CASE
                WHEN
                    LAG ( Cost_Center ) OVER ( PARTITION BY Person ORDER BY Period ) != Cost_Center
                THEN
                    LAG ( Cost_Center ) OVER ( PARTITION BY Person ORDER BY Period )
                ELSE
                    Previous_Cost_Center
                END
            AS Previous_Cost_Center,

But then I end up without a Previous_Cost_Center value in row 4 at all. How can this be archived using SQL? How can I take over the last different Cost_Center value to save it in Previous_Cost_Center sorting by Period?

Raj · Answer 1 · 2021-04-13T09:00:24.830

Replace #test with your table name. Its just select and you can change it to update. Please test this out as you will be having different scenarios.

SELECT s.person,
       s.period,
       s.cost_center,
       CASE WHEN z.person IS NULL THEN NULL ELSE z.cc END AS Previous_Cost_Center
FROM   #test s
       LEFT JOIN (SELECT *
                  FROM   (SELECT t1.*,
                                 t2.cost_center               cc,
                                 Row_number()OVER (partition BY t2.person, t1.period ORDER BY t2.period DESC) RN
                          FROM   #test t1
                                 JOIN #test t2
                                   ON t1.person = t2.person
                                      AND t1.period > t2.period
                          WHERE  t1.cost_center <> t2.cost_center)s
                  WHERE  rn = 1
         )z
              ON s.person = z.person
                 AND s.period = z.period

score 1 · Accepted Answer · answered Apr 13 '21 at 11:47

You can do this with window functions, but it is trickier than necessary because the ignore nulls option is not available.

First, you want to assign a grouping to adjacent values of the same group.

Second, you want to give the first value in the grouping the previous cost center.

Third, you want to "spread" that value through the entire group.

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;

The difference of row numbers defines the group. The max() spreads the previous value throughout the group.

Here is a db<>fiddle.

Thank you, Gordon! Great solution. – wounky Apr 13 '21 at 14:23 — wounky, Apr 13 '21 at 14:23

SQL Lag distinct - to the previous different value

2 Answers2