SQL Server - 选择不更改数据的句点

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.

基本上构建[External_Damages]恒定的周期帧并检查[Internal_Damages]在同一时间段内的变化(无关紧要多少次)。我花了很多时间尝试,但我担心我的抽象水平在很低的范围内思考...很高兴看到任何建议。

Thanks,

Bartosz

1 个解决方案

#1

I believe this is a form of Islands Problem.

我相信这是群岛问题的一种形式。

Here is a solution using ROW_NUMBER and GROUP BY:

这是使用ROW_NUMBER和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

#1