Listing 3: SQL Server 2000 Query That Calculates Ranges of Activity
SELECT storeid, MIN(dt) AS startperiod, MAX(dt) AS endperiod
FROM (SELECT storeid, dt,
        (SELECT MIN(dt)
         FROM dbo.Sales AS S2
         WHERE S2.storeid = S1.storeid
           AND S2.dt >= S1.dt
           AND NOT EXISTS
             (SELECT *
              FROM dbo.Sales AS S3
              WHERE S3.storeid = S2.storeid
                AND S3.dt - 1 = S2.dt)) AS grp
      FROM dbo.Sales AS S1
      WHERE dt BETWEEN '20050101' AND '20050131') AS D
GROUP BY storeid, grp
ORDER BY storeid, startperiod;