SQL: Group By on Consecutive Records

Question

A slightly tricky SQL question (we are running SQL server 2000).

I have the following table, StoreCount -

WeekEndDate    StoreCount
2010-07-25     359
2010-07-18     359
2010-07-11     358
2010-07-04     358
2010-06-27     358
2010-06-20     358
2010-06-13     358
2010-06-06     359
2010-05-30     360
2010-05-23     360
2010-05-16     360

I want to turn this into the following output -

StartDate    EndDate       StoreCount
2010-07-18   2010-07-25    359
2010-06-13   2010-07-11    358
2010-06-06   2010-06-06    359
2010-05-16   2010-05-30    360

As you can see, I'm wanting to group the store counts, by only as they run in sequence together.

Answer 1

Here's a kick at the can, only it may have syntax not available in SS2k. It was actually written on Oracle as I don't have that version of SS around anymore. The only catch might be the the select of a select...(it's been a while since I've used SS2k, so it's hard to remember what features weren't available back then.)

select min(weekenddate) as start_date, end_date, storecount
from (
select s1.weekenddate
     , (select max(weekenddate)
          from store_stats s2
         where s2.storecount = s1.storecount
           and not exists (select null
                             from store_stats s3
                            where s3.weekenddate < s2.weekenddate
                              and s3.weekenddate > s1.weekenddate
                              and s3.storecount <> s1.storecount)
       ) as end_date
     , s1.storecount
from store_stats s1
) result
group by end_date, storecount
order by 1 desc


START_DATE END_DATE   STORECOUNT
---------- ---------- ----------
2010-07-18 2010-07-25        359
2010-06-13 2010-07-11        358
2010-06-06 2010-06-06        359
2010-05-16 2010-05-30        360

Answer 2

Use cursor. I don't know how to do it in sql2k by using query.

DECLARE @w datetime
DECLARE @s int
DECLARE @prev_s int
DECLARE @start_w datetime
DECLARE @end_w datetime

CREATE TABLE #zz(start datetime, [end] datetime, StoreCount int)

DECLARE a_cursor CURSOR
FOR SELECT WeekEndDate, StoreCount FROM Line ORDER BY WeekEndDate DESC, StoreCount
OPEN a_cursor
FETCH NEXT FROM a_cursor INTO @w, @s
WHILE @@FETCH_STATUS = 0
BEGIN

    IF @end_w IS NULL 
    BEGIN

        SET @end_w = @w
        SET @start_w = @w     
        SET @prev_s = @s
    END 
    ELSE IF @prev_s <> @s
    BEGIN
       INSERT INTO #zz values(@start_w,  @end_w, @prev_s)

       SET @end_w = @w  
       SET @start_w = @w  
       SET @prev_s = @s 
    END ELSE
        SET @start_w = @w 

    FETCH NEXT FROM a_cursor INTO @w, @s
END
-- add last one
INSERT INTO #zz values(@start_w, @end_w, @prev_s)

CLOSE a_cursor
DEALLOCATE a_cursor

SELECT * FROM #zz ORDER BY 1 DESC
DROP TABLE #zz