CODEWITHSUNDEEP
sqlsql-server
Mike
Mike

Reputation: 65

If column value is 1 then get same value for the following date

I have data like table A. I try to write a SQL code to change the Flag column value to 1 wherever the pervious month (column Date) Flag value is 1 for each ID.

 **Table A**
 ID      | Date      | Flag |
+--------+-----------+------+
| 1      | Jan 20    | 1    |
| 1      | Feb 20    | 0    |
| 1      | Mar 20    | 0    |  
| 2      | Jan 20    | 0    |
| 2      | Feb 20    | 1    |
| 2      | Mar 20    | 0    |
+--------+-----------+------+

I want results like this:

 ID      | Date      | Flag |
+--------+-----------+------+
| 1      | Jan 20    | 1    |
| 1      | Feb 20    | 1    |
| 1      | Mar 20    | 0    |  
| 2      | Jan 20    | 0    |
| 2      | Feb 20    | 1    |
| 2      | Mar 20    | 1    |
+--------+-----------+------+

I'd really appreciated if someone can help me.

Upvotes: 0

Views: 104

Answers (2)

Popeye
Popeye

Reputation: 35930

If you want to update the record then use exists as follows:

Update your_table t
   Set t.flag = 1
 Where t.flag = 0 
   And exists 
      (Select 1 from your_table tt
        Where t.id = tt.id
          And DATEADD(month, -1, tt.date) = t.date
          And tt.flag = 1)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

In a select you can do:

select t.*,
       (case when flag = 0 and lag(flag) over (partition by id order by date) = 1
             then 1
             else flag
        end) as imputed_flag          
from t;

If you want to update the flag, you can use:

with toupdate as (
      select t.*, 
             lag(flag) over (partition by id order by date) as prev_flag
      from t
     )
update toupdate
    set flag = 1
    where prev_flag = 1;

Upvotes: 1

Related Questions