Sql-server – Difference between date value change

gaps-and-islandssql serversql-server-2008

Link: Query (duration) different between data value change

I am trying to write a query that would get me the duration of a status in a table. This query needs to work in SQL Server 2008.

Say I have the following table:

CREATE TABLE RateTable(
    [Key] [int],
    [Value] varchar(10) NULL,
    [RecordDate] [DateTime] NULL
    )
GO

INSERT INTO [RateTable] VALUES (1,'abc','2012-01-01');
INSERT INTO [RateTable] VALUES (1,'abc','2012-01-02');
INSERT INTO [RateTable] VALUES (1,'xyz','2012-01-02');
INSERT INTO [RateTable] VALUES (1,'xyz','2012-01-05');
INSERT INTO [RateTable] VALUES (1,'abc','2012-01-05');
INSERT INTO [RateTable] VALUES (1,'abc','2012-01-08');
INSERT INTO [RateTable] VALUES (2,'abc','2012-01-10');
INSERT INTO [RateTable] VALUES (2,'abc','2012-01-11');
INSERT INTO [RateTable] VALUES (2,'abc','2012-01-12');
GO

So it contains the rows that follow:

Key Value RecordDate
--- ----- ----------
1   abc   2012-01-01
1   abc   2012-01-02
1   xyz   2012-01-02
1   xyz   2012-01-05
1   abc   2012-01-05
1   abc   2012-01-08
2   abc   2012-01-10
2   abc   2012-01-11
2   abc   2012-01-12

I would like to have output (the result set) for the value 'abc' only, as shown below:

Value StartDate  EndDate  
----- ---------- ----------    
1     2012-01-01 2012-01-02 
1     2012-01-05 2012-01-08
2     2012-01-10 2012-01-12

Best Answer

Yet another grouping or window. It is a bit longer due SQL Server 2008 doesn't support LAG functions and it must be simulated.

-- add a row number to simulate a lag function
with a as
(
    select [key], value, recorddate, row_number() over (partition by [key],value order by [key], value, recorddate) rn 
    from ratetable 
    where value = 'abc' 
)
  -- set reset points
  , b as
  (
      select t1.[key], t1.value, t1.recorddate, t2.recorddate rd2,
             case when t2.recorddate is null
                       or t1.[key] <> t2.[key] 
                       or datediff (day, t2.recorddate, t1.recorddate) > 1 then 1 end is_reset
      from a t1
      left join a t2
      on t2.[key] = t1.[key] 
      and t2.rn = t1.rn -1

  )
    -- generate groups  
    , c as
    (
        select [key], [value], recorddate, count(is_reset) over (order by recorddate) grp
        from   b
    )
    -- max/min of each group
    select [Key], min(recorddate) as StartDate, max(recorddate) as EndDate 
    from c
    group by [Key], grp;

GO

Key | StartDate           | EndDate            
--: | :------------------ | :------------------
  1 | 01/01/2012 00:00:00 | 02/01/2012 00:00:00
  1 | 05/01/2012 00:00:00 | 05/01/2012 00:00:00
  1 | 08/01/2012 00:00:00 | 08/01/2012 00:00:00
  2 | 10/01/2012 00:00:00 | 12/01/2012 00:00:00

Warning: Null value is eliminated by an aggregate or other SET operation.
Related Question