SQL - Update rows between two values in a column

Question

I have a data set as below in SQL Server:

ROW_NUM  EMP_ID  DATE_KEY  TP_DAYS
1        U12345  20131003   1
2        U12345  20131004   0
3        U12345  20131005   0
4        U12345  20131006   0
5        U12345  20150627   1
6        U12345  20150628   0
1        U54321  20131003   1
2        U54321  20131004   0
3        U54321  20131005   0
4        U54321  20131006   0

I need to update all the zeros in column TP_DAYS with values increment by 1 to the previous value.
The required result set will be as follows:

ROW_NUM  EMP_ID  DATE_KEY  TP_DAYS
1        U12345  20131003   1
2        U12345  20131004   2
3        U12345  20131005   3
4        U12345  20131006   4
5        U12345  20150627   1
6        U12345  20150628   2
1        U54321  20131003   1
2        U54321  20131004   2
3        U54321  20131005   3
4        U54321  20131006   4

I tried using LAG and LEAD function in SQL. But couldn't achieve the result as expected.

Can someone help me to achieve it.

Answer 1

Using windowed functions (SUM/ROW_NUMBER so it will work with SQL Server 2008):

WITH cte AS
(
  SELECT *, s =  SUM(TP_DAYS) OVER(PARTITION BY EMP_ID ORDER BY ROW_NUM)
  FROM #tab
), cte2 AS
(
  SELECT *,
    tp_days_recalculated = ROW_NUMBER() OVER (PARTITION BY EMP_ID, s ORDER BY ROW_NUM)
  FROM cte
)
UPDATE cte2
SET TP_DAYS = tp_days_recalculated;

SELECT *
FROM #tab;

LiveDemo

Output:

╔═════════╦════════╦══════════╦═════════╗
║ ROW_NUM ║ EMP_ID ║ DATE_KEY ║ TP_DAYS ║
╠═════════╬════════╬══════════╬═════════╣
║       1 ║ U12345 ║ 20131003 ║       1 ║
║       2 ║ U12345 ║ 20131004 ║       2 ║
║       3 ║ U12345 ║ 20131005 ║       3 ║
║       4 ║ U12345 ║ 20131006 ║       4 ║
║       5 ║ U12345 ║ 20150627 ║       1 ║
║       6 ║ U12345 ║ 20150628 ║       2 ║
║       1 ║ U54321 ║ 20131003 ║       1 ║
║       2 ║ U54321 ║ 20131004 ║       2 ║
║       3 ║ U54321 ║ 20131005 ║       3 ║
║       4 ║ U54321 ║ 20131006 ║       4 ║
╚═════════╩════════╩══════════╩═════════╝   

#Addendum

Original OP question and sample data are very clear that tp_days indicators are 0 and 1 not any other values.

Especially for Atheer Mostafa:

check this example as a proof: https://data.stackexchange.com/stackoverflow/query/edit/423186

This should be new question, but I will handle that case:

;WITH cte AS
(
  SELECT *
   ,rn = s +  ROW_NUMBER() OVER(PARTITION BY EMP_ID, s ORDER BY ROW_NUM) -1
   ,rnk = DENSE_RANK() OVER(PARTITION BY EMP_ID ORDER BY s)
  FROM (SELECT *, s =  SUM(tp_days) OVER(PARTITION BY EMP_ID ORDER BY ROW_NUM)
        FROM #tab) AS sub
), cte2 AS
(
  SELECT c1.*,
   tp_days_recalculated = c1.rn - (SELECT COALESCE(MAX(c2.s),0)
                                   FROM cte c2
                                   WHERE c1.emp_id = c2.emp_id
                                     AND c2.rnk = c1.rnk-1)
  FROM cte c1
)
UPDATE cte2
SET tp_days = tp_days_recalculated;

LiveDemo2

Output:

╔═════════╦════════╦══════════╦═════════╗
║ row_num ║ emp_id ║ date_key ║ tp_days ║
╠═════════╬════════╬══════════╬═════════╣
║       1 ║ U12345 ║ 20131003 ║       2 ║
║       2 ║ U12345 ║ 20131004 ║       3 ║
║       3 ║ U12345 ║ 20131005 ║       4 ║
║       4 ║ U12345 ║ 20131006 ║       3 ║
║       5 ║ U12345 ║ 20150627 ║       4 ║
║       6 ║ U12345 ║ 20150628 ║       5 ║
║       1 ║ U54321 ║ 20131003 ║       2 ║
║       2 ║ U54321 ║ 20131004 ║       3 ║
║       3 ║ U54321 ║ 20131005 ║       1 ║
║       4 ║ U54321 ║ 20131006 ║       2 ║
╚═════════╩════════╩══════════╩═════════╝

it shouldn't change the values 3,4,2 to 1 .... this is the case. I don't need your solution when I have another generic answer, you don't tell me what to do ... thank you

Solution mentioned in comment is nothing more than quirky update. Yes it will work, but may easily fail:

• First of all there is no such thing as ordered table per se
• Query optimizer may read data in any way(especially when dataset is big and parallel execution is involved). Without ORDER BY you cannot guarantee the stable result
• The behavior is not documented,might work today but could break in the future

Related articles:

1. Robyn Page's SQL Server Cursor Workbench
2. Calculate running total / running balance
3. No Seatbelt - Expecting Order without ORDER BY

Answer 2

Let me assume SQL Server 2012+. You need to identify groups that are delimited by 1. A simple way to calculate the group is by doing a cumulative sum of 1s. Then row_number() can be used to calculate the new value. You can do this work uisng an updatable CTE:

with toupdate as (
      select t.*,
             row_number() over (partition by empid, grp order by row_num) as new_tp_days
      from (select t.*, 
                   sum(tp_days) over (partition by emp_id order by row_num) as grp
            from t
           ) t
     )
update toupdate
    set tp_days = new_tp_days;

In earlier versions of SQL Server, you can accomplish the same thing (less efficiently). One method uses outer apply.

Answer 3

I have tricky much simpler way with a simple code as follows:

DECLARE @last int=0
UPDATE #Employees set @last=CASE WHEN TP_DAYS=0 THEN @last+1 ELSE TP_DAYS END,
TP_DAYS=CASE WHEN TP_DAYS=0 THEN @last ELSE TP_DAYS END

This runs in any SQL Server Engine Check the demo here

https://data.stackexchange.com/meta.stackoverflow/query/422955/sql-update-rows-between-two-values-in-a-column?opt.withExecutionPlan=true#resultSets