Check for consecutive values and then make a decision

Question

I have a dataset where i need to check for next 7 consecutive dates if value is 0 then we will put all the in between value as 0 if the values in between are not 0 for 7 days then we will mark all value as 1.
eg:

Dataset

Days       Values  
12/21/2006  1  
12/22/2006  1  
12/23/2006  1  
12/24/2006  1  
12/25/2006  0  
12/26/2006  1  
12/27/2006  0  
12/28/2006  0  
12/29/2006  1  
12/30/2006  1  
12/31/2006  0  
1/1/2007    0  
1/2/2007    0  
1/3/2007    0  
1/4/2007    0  
1/5/2007    0  
1/6/2007    0  
1/7/2007    1  
1/8/2007    1

Expected Result

Day      Values NewVal  
12/21/2006  1   1    
12/22/2006  1   1  
12/23/2006  1   1  
12/24/2006  1   1  
12/25/2006  0   1  
12/26/2006  1   1  
12/27/2006  0   1  
12/28/2006  0   1  
12/29/2006  1   1  
12/30/2006  1   1  
12/31/2006  0   0  
1/1/2007    0   0  
1/2/2007    0   0  
1/3/2007    0   0  
1/4/2007    0   0  
1/5/2007    0   0  
1/6/2007    0   0  
1/7/2007    1   1  
1/8/2007    1   1

I have already tried using lead to check the 7 days data but not able to get the consecutive 7 days zero part.In my code whenever it is getting a zero in 7 days offset it is making the value as 0 which is not what i want .

My query is

select *, LEAD(Values,7,0) over (order by gas_dte) as nextval
from temp

Have you tried OVER Clause? https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017 — Fábio Nascimento, May 08 '19 at 09:34
@Nit2702 . . . Your sample data does not match the description. 2017-01-05 is 0 even though 2017-01-07 is "1". — Gordon Linoff, May 08 '19 at 11:06

score 0 · Answer 1 · answered May 08 '19 at 09:40

;with cte as (
    select '12/21/2006' as TheDay, 1 as TheValue union all
    select '12/22/2006' as TheDay, 1 as TheValue union all
    select '12/23/2006' as TheDay, 1 as TheValue union all
    select '12/24/2006' as TheDay, 1 as TheValue union all
    select '12/25/2006' as TheDay, 0 as TheValue union all
    select '12/26/2006' as TheDay, 1 as TheValue union all
    select '12/27/2006' as TheDay, 0 as TheValue union all
    select '12/28/2006' as TheDay, 0 as TheValue union all
    select '12/29/2006' as TheDay, 1 as TheValue union all
    select '12/30/2006' as TheDay, 1 as TheValue union all
    select '12/31/2006' as TheDay, 0 as TheValue union all
    select '01/01/2007' as TheDay, 0 as TheValue union all
    select '01/02/2007' as TheDay, 0 as TheValue union all
    select '01/03/2007' as TheDay, 0 as TheValue union all
    select '01/04/2007' as TheDay, 0 as TheValue union all
    select '01/05/2007' as TheDay, 0 as TheValue union all
    select '01/06/2007' as TheDay, 0 as TheValue union all
    select '01/07/2007' as TheDay, 1 as TheValue union all
    select '01/08/2007' as TheDay, 1 as TheValue
)
-- Define WeekNumber as Year * 100 + week
, set1 as (
    select *, YEAR(TheDay) * 100 + DATEPART(week, TheDay) as WeekNumber
    from cte
)
-- Aggregate Value per WeekNumber
, set2 as (
    select WeekNumber, sum(TheValue) as ValueSum
    from set1
    group by WeekNumber
)
-- Present
select set1.TheDay, set1.TheValue, case when set2.ValueSum > 0 then 1 else 0 end as NewVal
from set1
inner join set2
    on set1.WeekNumber = set2.WeekNumber

Gordon Linoff · Answer 2 · 2019-05-08T11:21:01.980

Based on your description, you can use min() as a window function:

select t.*,
       min(value) over (order by day rows between current row and 6 following) as newval
from t;

If you just want to keep groups of 7 (or more) consecutive zeros, then you can use window functions:

select t.day, t.value,
       (case when value = 0 and count(*) over (partition by value, dateadd(day, -seqnum, day)) >= 7
             then value
             else 1
        end) as newvalue
from (select t.*,
             row_number() over (partition by value order by day) as seqnum
      from t
     ) t;

A db<>fiddle for this is here.

The logic for this is a simplified group-and-islands solution. Groups of adjacent dates are found by subtracting a sequential number (generated by row_number()). The case logic then keeps the 0s that meet your conditions.

Check for consecutive values and then make a decision

2 Answers2