CODEWITHSUNDEEP
sqlsql-server
at9063
at9063

Reputation: 33

Update Flag Based On Change of Previous Value

I have below table .Need sql ,If there is change in INPUT value then update FLAG to 1 else 0.

INPUT   START_DATE  PERSON_ID  FLAG
42707   2017-01-01  227317      0
40000   2018-01-01  227317      1
42400   2019-01-01  227317      1
42400   2019-01-02  227317      0

Upvotes: 0

Views: 1420

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If you want this in a query, then use row_number():

select t.*,
       (case when row_number() over (partition by person_id order by start_date) = 1
             then 0 else 1
        end) as flag
from t;

If the input_value could be the same on different rows, then use first_value():

select t.*,
       (case when value <> first_value(input) over (partition by person_id order by start_date) = 1
             then 0 else 1
        end) as flag
from t;

Either form could be incorporated into an update using an updatable CTE if you want to update the table.

EDIT:

If you want to know if the value changes from one row to the "next", then use lag(). In an update, this looks like:

with toupdate as (
      select t.*,
             lag(input) over (partition by customerid order by date) as prev_input
      from t
     )
update toupdate
    set flag = (case when prev_input <> input then 1 else 0 end);

That said, I would not advise you to store the data in the table. Instead, just put the logic in a select when you need it. Otherwise, the data could get out of date if a historical value is updated.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use lag() :

select t.*, 
       (case when lag(input, 1, input) over (partition by person_id order by start_date) = input
             then 0 else 1 
        end) as FLAG
from table t;

Upvotes: 1

Related Questions