1

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
  • 5
    Why would your output not include 1, '2012-01-05', '2012-01-08'? And, why just one row for ID = 2, instead of 2, '2012-01-10', '2012-01-11' and 2, '2012-01-11', '2012-01-12'? – RDFozz Jun 5 '17 at 18:01
  • 1
    From your desired output - for each set of consecutive entries for a given ID where the value is abc, you want the first date and the last date. What do you want to see if there's only one record: for instance, add an additional entry: Key = 3; value = abc, date = 2012-01-11. Should there be no entry (only one record), or should we treat this as the first and last (returning value = 3, startdate = 2012-01-11, enddate = 2012-01-11)? – RDFozz Jun 6 '17 at 14:39
3

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.
0

Check this,

DECLARE  @RateTable TABLE(
    [Key] [int],
    [Value] varchar(10) NULL,
    [RecordDate] [DateTime] NULL
    )


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');
declare @input varchar(50)='abc'
;

WITH CTE
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            ORDER BY (
                    SELECT NULL
                    )
            ) rn
    FROM @RateTable
    WHERE value = @input
    )
    ,cte1
AS (
    SELECT *
        ,dateadd(day, [key], [RecordDate]) ENDDATE
    FROM cte
    WHERE rn % 2 <> 0
    )
SELECT c.*
FROM (
    SELECT c.value
        ,c.[RecordDate] [start_date]
        ,CASE 
            WHEN ca.[RecordDate] IS NULL
                THEN c.[RecordDate]
            ELSE ca.[RecordDate]
            END ENDDATE
        ,ROW_NUMBER() OVER (
            PARTITION BY CASE 
                WHEN ca.[RecordDate] IS NULL
                    THEN c.[RecordDate]
                ELSE ca.[RecordDate]
                END ORDER BY c.[RecordDate]
            ) rn1
    FROM cte1 c
    OUTER APPLY (
        SELECT [RecordDate]
        FROM cte1 c1
        WHERE c.value = c1.value
            AND c.enddate = c1.[RecordDate]
        ) ca
    ) c
WHERE rn1 = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.