SQL to find runs of matching values

Question

I have the following table called [Store_Sales] -

Store     Date     Sales
    1     23/04    10000
    2     23/04    11000
    1     22/04    10000
    2     22/04    10500
    1     21/04    10000
    2     21/04    10550

I want a SQL that will return a "run" of similar values in the Sales column for a particular store. For example, from the above table it would return store 1 for the 23rd, 22nd and 21st, as they all have the same value (10,000).

I am using SQL Server 2008.

Answer 1

Have a look at something like this (full example)

DECLARE @Store_Sales TABLE(
        Store INT,
        Date DATETIME,
        Sales FLOAT
)

INSERT INTO @Store_Sales SELECT 1,'23 Apr 2010',10000 
INSERT INTO @Store_Sales SELECT 2,'23 Apr 2010',11000 
INSERT INTO @Store_Sales SELECT 1,'22 Apr 2010',10000 
INSERT INTO @Store_Sales SELECT 2,'22 Apr 2010',10500 
INSERT INTO @Store_Sales SELECT 1,'21 Apr 2010',10000 
INSERT INTO @Store_Sales SELECT 2,'21 Apr 2010',10550 

SELECT  ss.Store,
        MIN(ss.Date) StartDate,
        MAX(ssNext.Date) EndDate,
        ss.Sales
FROM    @Store_Sales ss INNER JOIN
        @Store_Sales ssNext ON  ss.Store = ssNext.Store
                            AND ss.Date + 1 = ssNext.Date
                            AND ss.Sales = ssNext.Sales
GROUP BY    ss.Store,
            ss.Sales