基于字段创建日期范围

Question

我有以下表格作为输入

CREATE TABLE #Temp11
(
Field1              INT,
Date1               DATETIME,
Status1             VARCHAR(50),
Status2             VARCHAR(50),
Status3             VARCHAR(50),
Status4             VARCHAR(50),
ConcatStatus        VARCHAR(1000)
)

INSERT INTO #Temp11 VALUES(111 , '2018-01-05' , 'Yes' , 'Yes' , 'Yes' , 'Yes','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-06' , 'Yes' , 'Yes' , 'Yes' , 'Yes','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-07' , 'Yes' , 'Yes' , 'Yes' , 'Yes','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-11' , 'No' , 'No' , 'No' , 'No','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-12' , 'Yes' , 'Yes' , 'Yes' , 'Yes','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-13' , 'No' , 'No' , 'No' , 'No','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-17' , 'No' , 'No' , 'No' , 'No','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-18' , 'No' , 'No' , 'No' , 'No','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-19' , 'No' , 'No' , 'No' , 'No','');

现在，我想要以下输出

FromDate    |ToDate     |Status1    |Status2    |Status3    |Status4
====================================================================
05-Jan      |07-Jan     |Yes        |Yes        |Yes        |Yes
08-Jan      |10-Jan     |NULL       |NULL       |NULL       |NULL
11-Jan      |11-Jan     |No         |No         |No         |No
12-Jan      |12-Jan     |Yes        |Yes        |Yes        |Yes
13-Jan      |13-Jan     |No         |No         |No         |No
14-Jan      |16-Jan     |NULL       |NULL       |NULL       |NULL
17-Jan      |19-Jan     |No         |No         |No         |No

这里的逻辑是，如果所有状态相同，则Date1文件应转换为Date Rage和缺少的日期范围应具有空值

例如，1月5日、1月6日和1月7日的记录在所有四个状态字段中都具有相同的值，它们被组合在一起，并给定了1月5至1月7的日期范围。

我试图连接所有的状态，然后尝试应用分区和over，但它不起作用。

Answer 1

我相信这可以缩短/优化，但这里有一个解决方案。唯一的问题是，如果有大量数据，您可能不得不使用MAXRECURSION。

DROP TABLE IF EXISTS #Temp11;
GO
CREATE TABLE #Temp11
(
Field1              INT,
Date1               DATE,
Status1             VARCHAR(50),
Status2             VARCHAR(50),
Status3             VARCHAR(50),
Status4             VARCHAR(50),
ConcatStatus        VARCHAR(1000)
)

INSERT INTO #Temp11 VALUES(111 , '2018-01-05' , 'Yes' , 'Yes' , 'Yes' , 'Yes','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-06' , 'Yes' , 'Yes' , 'Yes' , 'Yes','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-07' , 'Yes' , 'Yes' , 'Yes' , 'Yes','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-11' , 'No' , 'No' , 'No' , 'No','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-12' , 'Yes' , 'Yes' , 'Yes' , 'Yes','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-13' , 'No' , 'No' , 'No' , 'No','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-17' , 'No' , 'No' , 'No' , 'No','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-18' , 'No' , 'No' , 'No' , 'No','');
INSERT INTO #Temp11 VALUES(111 , '2018-01-19' , 'No' , 'No' , 'No' , 'No','');
GO
SELECT * FROM #Temp11;
GO
WITH
ordered AS (
SELECT  Field1 ,
Date1 ,
Status1 ,
Status2 ,
Status3 ,
Status4 ,
ROW_NUMBER() OVER (PARTITION BY Field1 ORDER BY Date1) AS RowNumber
FROM    #Temp11
) ,
combined AS (
SELECT  Field1 ,
Date1 ,
CAST(NULL AS Date) AS Date2 ,
Status1 ,
Status2 ,
Status3 ,
Status4 ,
RowNumber
FROM    ordered
WHERE   RowNumber = 1
UNION ALL
SELECT  o.Field1 ,
CASE WHEN o.Status1 = c.Status1 AND o.Status2 = c.Status2 AND o.Status3 = c.Status3 AND o.Status4 = c.Status4
THEN c.Date1 ELSE o.Date1 END ,
CASE WHEN o.Status1 = c.Status1 AND o.Status2 = c.Status2 AND o.Status3 = c.Status3 AND o.Status4 = c.Status4
THEN o.Date1 ELSE NULL END ,
o.Status1 ,
o.Status2 ,
o.Status3 ,
o.Status4 ,
o.RowNumber
FROM    ordered o
INNER JOIN combined c
ON c.Field1 = o.Field1
AND c.RowNumber = o.RowNumber - 1
) ,
results AS (
SELECT  Field1 ,
Date1 ,
Date2 ,
Status1 ,
Status2 ,
Status3 ,
Status4 ,
ROW_NUMBER() OVER (PARTITION BY Field1, Date1 ORDER BY Date2 DESC) AS RowNumber
FROM    combined
)
SELECT  Field1 ,
Date1 ,
Date2 ,
Status1,
Status2,
Status3,
Status4
FROM    results
WHERE   RowNumber = 1
ORDER BY Field1 ,
Date1