How can I find consecutive active weeks in SQL?

date, sql, sql-server

Solution

When looking for sequential values, there is a simple observation that helps. If you subtract a sequence from the dates then the value is a constant. You can use this as a grouping mechanism

select CustomerId, min(RaceDate) as seqStart, max(RaceDate) as seqEnd,
       count(*) as NumDaysRaced
from (select t.*,
              dateadd(week, - row_number() over (partition by customerID, RaceDate),
                      RaceDate) as grp
      from table t
      where races >= 2
     ) t
group by CustomerId, grp;

You can then use this to get your final "points":

select CustomerId,
       sum(case when NumDaysRaced > 1 then (NumDaysRaced - 1) * 100 else 0 end) as Points
from (select CustomerId, min(RaceDate) as seqStart, max(RaceDate) as seqEnd,
             count(*) as NumDaysRaced
      from (select t.*,
                    dateadd(week, - row_number() over (partition by customerID, RaceDate),
                            RaceDate) as grp
            from table t
            where races >= 2
           ) t
      group by CustomerId, grp
     ) c
group by CustomerId;

Problem

What I would like to do is find the number of consecutive weeks that someone is active on Sundays and assign them a value. They have to participate in at least 2 races a day to be counted as active for the week. If they are active for 2 consecutive weeks I would like to assign a value of 100, 3 consecutive weeks a value of 200, 4 consecutive weeks a value of 300, and continuing up to 9 consecutive weeks. My difficulty is not determining consecutive weeks, but breaks in between consecutive dates. Suppose the following dataset: ``` CustomerID RaceDate Races 1 2/2/2014 2 1 2/9/2014 5 1 2/16/2014 3 1 2/23/2014 3 1 3/2/2014 4 1 3/9/2014 3 1 3/16/2014 3 2 2/2/2014 2 2 2/9/2014 3 2 3/2/2014 2 2 3/9/2014 4 2 3/16/2014 3 ``` CustomerID 1 would have 7 consecutive weeks for a value of 600. The hard part for me is CustomerID 2. They would have 2 consecutive weeks AND 3 consecutive weeks. So their total value would be 100 + 200 = 300. I would like to be able to do this with any different combination of consecutive weeks. Any help please? EDIT: I am using SQL Server 2008 R2.

Original source