CODEWITHSUNDEEP
sqlteradata
Nitin Pal
Nitin Pal

Reputation: 21

sql:want to flag a column based on other column value and some condition

itm wk  f1  f2
i1  w1  0   0
i1  w2  0   0
i1  w3  0   0
i1  w4  0   0
i1  w5  0   0
i1  w6  1   0
i1  w7  1   0
i1  w8  1   0
i1  w9  1   0
i1  w10 1   0
i1  w11 1   0
i1  w12 0   1
i1  w15 0   1
i1  w16 0   0
i1  w17 0   0
i1  w18 0   0

----------
i2  w1  0   0
i2  w2  0   0
i2  w3  0   0
i2  w4  0   0
i2  w5  0   0
i2  w6  0   0
i2  w7  0   0
i2  w8  0   0
i2  w9  1   0
i2  w10 1   0
i2  w11 1   0
i2  w12 1   0
i2  w13 0   1
i2  w14 0   1
i2  w16 0   1
i2  w17 0   0
i2  w18 0   0
----------
i3  w1  0   0
i3  w2  0   0
i3  w3  0   0
i3  w4  0   0
i3  w5  0   0
i3  w6  0   0
i3  w7  0   0
i3  w8  0   0
i3  w9  1   0
i3  w10 1   0
i3  w11 0   1
i3  w12 0   1
i3  w13 0   1
i3  w14 0   1
i3  w15 0   0
i3  w16 0   0
i3  w17 0   0
i3  w18 0   0
----------

The above table has data for three items(i1,i2 and i3) for week one to eighteen (w1 to w18) and flag f1(0 or 1) for the respective item and week.Note: there might be weeks where the data is not present for example "i1" does not have data for w13 and w14, similarly "i2" does not have data for w15 and "i3" has data for every week.

Now i want to create another flag f2 where it is flagged 1 for the next four weeks from the point where "f1" is last made 1

for example: for item "i3" f1 was last flagged for week w10 and now the f2 will be flagged for next for four weeks i.e for weeks w11,w12,w13 and w14

For item "i1" f1 was last flagged for week w11 and now the f2 will be flagged for next for four weeks i.e for weeks w12,w13,w14 and w15 but the weeks w13 and w14 are not present so f2 will be flagged for only w12 and w15

For item "i2" f1 was last flagged for week w12 and now the f2 will be flagged for next for four weeks i.e for weeks w13,w14,w15 and w16 but the week w15 is not present so f2 will be flagged for only w13,w14 and w16

Please help in coding the above example in teradata

Upvotes: 0

Views: 1679

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

First, you need to find the last f1 for each item:

select itm, max(wk)
from table t
where f1 = 1
group by itm;

Next, you can use this information to define the flag:

select t.itm, t.wk, t.f1,
       (case when t.wk between tf1.maxwk + 1 and tf1.maxwk + 4 then 1 else 0 end) as f2
from table t left join
     (select itm, max(wk) as maxwk
      from table t
      where f1 = 1
      group by itm
     ) tf1
     on t.itm = tf1.itm;

You can actually do this with window functions, although I think the above may be clearer:

select t.itm, t.wk, t.f1,
       (case when max(case when f1 = 1 then wk end) over (partition by itm)  between wk - 4 and wk - 1
             then 1 else 0
        end) as f2
from table t;

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

Assuming you need to assign this flag multiple times using a MAX is not enough, you need to apply a Cumulative Max:

case 
  when max(case when f1 = 1 then wk end) -- last week where flag was set
       over (partition by itm
             order by wk
             rows unbounded preceding) >= wk - 4 -- must be not more than 4 weeks ago
        and f1 = 0 -- flag not set on the current row
  then 1 
  else 0 
end

Upvotes: 1

Related Questions