CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014gaps-and-islands
Avi
Avi

Reputation: 1845

Update a column with the value from previous row based on conditions

I have a table where I need to find a value satisfying few conditions and if those conditions are satisfied I need to assign the same value to each of the columns 

 Table 
  ID       Name   Salary  rownum
  1         Jon    500     1
  1         Jim    600     2 
  1         Jack   700     3
  1         Bob    1000    4
  2         Adam    500    1
  2         Aron    600    2 
  2         James   900    3
  2         Jay     1000   4

First condition is I need to have the same ID, then within the ID I need to compare the first row with the 2nd one if the difference is less than or equal to 100. I can only compare Rownum 1 with 2 and 2 with 3 and so on. After identifying if the condition is satisfied, I need to update the salary values to the value which exists for Rownum 1 until the point where the condition is satisfied keeping the names same.

Expected Output 

 Table 
  ID       Name   Salary  rownum
  1         Jon    500     1
  1         Jim    500     2 
  1         Jack   500     3
  1         Bob    1000    4
  2         Adam    500    1
  2         Aron    500    2 
  2         James   900    3  
  2         Jay     900    4

Jim had 600 as salary and difference with Jon's salary is <=100, and Jack had 700 difference with Jim's is <=100, as Jim is within Jon's salary and Jack is within Jim's salary these values are continuous, so we need Jon's value for these rows, while Bob is independent as it does not fall in the range. Same logic applies for ID = 2

Upvotes: 3

Views: 4476

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17126

Your problem is not as simple as it appears as it is possible that preceding row may have greater value(say 800) compared to current row (say 750). It is also possible that there are more islands and gaps than simply one.

Below is my solution. Also see working demo

; with cte as 
(
    select *
    , toUpdate= case 
                    when 
                        salary - lag( salary) over( partition by Id order by rownum asc) between 0 and 100 
                    then 
                        1 
                    else 
                        0 
                end
from t
)

update t5 
set t5.salary=t4.newsalary
from t t5 
join
(
    select 
    t1.*, 
    rn=row_number () over( partition by t1.id, t1.rownum order by t3.rownum desc), 
    newsalary=t3.salary 
    from cte t1 
        outer apply
            (
                select *  
                    from cte t2 
                where t2.id=t1.id 
                    and t2.rownum<t1.rownum 
                    and t2.toUpdate=0
              ) t3
    )t4 
on t4.rn=1 and t4.toUpdate=1 and t5.id=t4.id and t5.rownum=t4.rownum

select * from t

Upvotes: 2

Wei Lin
Wei Lin

Reputation: 3811

try this:

if "left join self + where Salary + 100 * (jointable.rownum - rownum) = jointable.Salary 's data is not null" then update Salary 

update T
set Salary = NewSalary
from (
  select T2.*,T1.Salary NewSalary,(T2.rownum - T1.rownum) fd
  from [Table] T1
  left join [Table] T2 on T1.ID = T2.ID
    and T1.rownum <> T2.rownum
    and (T2.rownum - T1.rownum) > 0 
    and T1.[Salary] + 100 * (T2.rownum - T1.rownum) = T2.[Salary] 
  where T2.id is not null
) T ;  

| ID |  Name | Salary | rownum |
|----|-------|--------|--------|
|  1 |   Jon |    500 |      1 |
|  1 |   Jim |    500 |      2 |
|  1 |  Jack |    500 |      3 |
|  1 |   Bob |   1000 |      4 |
|  2 |  Adam |    500 |      1 |
|  2 |  Aron |    500 |      2 |
|  2 | James |    900 |      3 |
|  2 |   Jay |    900 |      4 |

SQL Fiddle DEMO LINK

Upvotes: 3

Related Questions