SQL Server - Selecting periods without changes in data

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

Answers


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

Need Your Help

How to specify the view controller classes to be used by UITabBarController?

iphone interface-builder uitabbarcontroller

In interface builder I select the Tab Bar Controller item. The inspector shows me a list of associated view controllers. For any strange reason, I can not define anywhere the exact name for an view

How would I go about saving four separate values to four hidden fields?

javascript html hidden-fields

Currently I have four separate sections: Sole, Collar, Lining and Tongue. In each section there is the option of selecting a material, the code below is an example of two of the sections.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.