CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
Brandon
Brandon

Reputation: 3276

How to get consecutive rows given a condition

I've got a table that looks like this

    WeekId     PassesCondition
----------------------------------
      1              1
      2              0
      3              0
      4              1
      5              1
      6              1

I need to write a query that counts the consecutive weeks back from the max WeekId that the condition has been passed.

I've done some searching and I seem to need to do something with a CTE and ROW_NUMBER() possibly. I've tried a few things, but the results haven't even been worthy to post as a "What I've tried so far".

The output should be just a count of how many weeks from the Max(WeekId) was the condition passed. e.g. 1, 2, 5, etc.

Upvotes: 0

Views: 933

Answers (3)

Ross Bush
Ross Bush

Reputation: 15185

If you need to account for the multiple start and stops you can count the islands using a query similar to:

DECLARE @T TABLE(WeekId INT, PassedCondition BIT)
INSERT @T VALUES (1,1),(2,0),(3,0),(4,1),(5,1),(6,1),(7,1),(8,0),(9,0),(10,1)

;WITH Holes AS(SELECT WeekID FROM @T WHERE PassedCondition=0),
SegmentStart AS
(
    SELECT WeekId, HoleWeekId 
    FROM
    (
        SELECT  This.WeekId, HoleWeekId = MIN(H.WeekId)
        FROM  @T This
        INNER  JOIN Holes H ON H.WeekId > This.WeekID AND PassedCondition=1
        GROUP BY This.WeekId
        UNION 
        SELECT WeekId = MAX(WeekID), HoleWeekId=MAX(WeekID)+1 
        FROM @T 
        WHERE PassedCondition=1
    )AS X
)
SELECT 
    WeekSegmentEnd = HoleWeekId-1,
    ConsecutiveCount = COUNT(*)
FROM 
    SegmentStart
GROUP BY
    HoleWeekId

Upvotes: 1

forpas
forpas

Reputation: 164164

Count the rows that are between the maximum weekid that has passesCondition = 0 (exclusive) and the maximum weekid (exclusive):

SELECT COUNT(*)
FROM tablename t
WHERE 
  (t.weekid < (SELECT MAX(weekid) FROM tablename))
  AND
  (t.weekid > (SELECT MAX(weekid) FROM tablename WHERE passesCondition = 0));

If you want to include the last weekid you change < to <=.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270593

Assuming the condition is only 0 or 1:

select count(*)
from t
where t.weekid > (select max(t2.weekid) from t t2 where t2.passesCondition = 0) or
      not exists (select 1 from t t2 where t2.passesCondition = 0);

The second condition is unnecessary if you know a row with passesCondition = 0 exists.

If you like using window functions, you can also do this with a cumulative reverse min:

select count(*)
from (select t.*,
             min(passesCondition) over (order by weekid desc) as running_min
      from t
     ) t
where running_min = 1;

Or, not exists can be used:

select count(*)
from t
where not exists (select 1
                  from t t2
                  where t2.weekid > t.weekid and t2.passesCondition = 0
                 );

Or > all:

select count(*)
from t
where t.weekid > all (select t2.weekid from t t2 where t2.passesCondition = 0);

I hadn't realized there are so many different way to express this. No doubt, there are quite a few more.

Upvotes: 3

Related Questions