SQL Server - Selecting periods without changes in data

Question

What I am trying to do is to select periods of time where the rest of data in the table was stable based on one column and check was there a change in second column value in this period.

Table:

create table #stable_periods
(
[Date]             date,
[Car_Reg]          nvarchar(10),
[Internal_Damages] int,
[External_Damages] int
)

insert into #stable_periods
values  ('2015-08-19', 'ABC123', 10, 10),
        ('2015-08-18', 'ABC123', 9, 10),
        ('2015-08-17', 'ABC123', 8, 9),
        ('2015-08-16', 'ABC123', 9, 9),
        ('2015-08-15', 'ABC123', 10, 10),
        ('2015-08-14', 'ABC123', 10, 10),
        ('2015-08-19', 'ABC456', 5, 3),
        ('2015-08-18', 'ABC456', 5, 4),
        ('2015-08-17', 'ABC456', 8, 4),
        ('2015-08-16', 'ABC456', 9, 4),
        ('2015-08-15', 'ABC456', 10, 10),
        ('2015-01-01', 'ABC123', 1, 1),
        ('2015-01-01', 'ABC456', NULL, NULL);

--select * from #stable_periods
-- Unfortunately I can’t post pictures yet but you get the point of how the table looks like

What I would like to receive is

Car_Reg	  FromDate	ToDate	          External_Damages    Have internal damages changed in this period?
ABC123	  2015-08-18	2015-08-19	  10	              Yes
ABC123	  2015-08-16	2015-08-17	  9	              Yes
ABC123	  2015-08-14	2015-08-15	  10	              No
ABC123	  2015-01-01	2015-01-01	  1	              No
ABC456	  2015-08-19	2015-08-19	  3	              No
ABC456	  2015-08-16	2015-08-18	  4	              Yes
ABC456	  2015-08-15	2015-08-15	  10	              No
ABC456	  2015-01-01	2015-01-01	  NULL	              NULL

Basically to build period frames where [External_Damages] were constant and check did the [Internal_Damages] change in the same period (doesn't matter how many times). I spend a lot of time trying but I am afraid that my level of abstraction thinking in much to low... Will be great to see any suggestions.

Thanks,

Bartosz

Answer 1

I believe this is a form of Islands Problem.

Here is a solution using ROW_NUMBER and GROUP BY:

SQL Fiddle

WITH CTE AS(
    SELECT *,
        RN = DATEADD(DAY, - ROW_NUMBER() OVER(PARTITION BY Car_reg, External_Damages ORDER BY [Date]), [Date])
    FROM #stable_periods
)
SELECT
    Car_Reg,
    FromDate = MIN([Date]),
    ToDate = MAX([Date]) ,
    External_Damages,
    Change =
            CASE 
                WHEN MAX(External_Damages) IS NULL THEN NULL
                WHEN COUNT(DISTINCT Internal_Damages) > 1 THEN 'Yes' 
                ELSE 'No' 
            END     
FROM CTE c
GROUP BY Car_Reg, External_Damages, RN
ORDER BY Car_Reg, ToDate DESC