在自定义列中显示日期范围 - 差距和孤岛 [英] Show Date Range in Custom Column - Gaps and Islands

问题描述

我有一个看起来像这样的表格:

I have table that looks like this:

+------------+------+
|    Date    | Name |
+------------+------+
| 2017-01-07 | A    |
| 2017-01-08 | A    |
| 2017-01-09 | A    |
| 2017-01-12 | A    |
| 2017-01-07 | B    |
| 2017-01-08 | B    |
| 2017-01-09 | B    |
+------------+------+

我希望能够将其变成以下内容:

I would like to be able to turn it into the following:

+-------------------------+------+
|       Date Range        | Name |
+-------------------------+------+
| 2017-01-07 - 2017-01-09 | A    |
| 2017-01-07 - 2017-01-09 | B    |
| 2017-01-12              | A    |
+-------------------------+------+

该代码将仅查找连续日期的最小值和最大值，使用 名称 列对结果进行分组，然后将最小值和最大值日期列为一列中的to 和 from"字符串.

The code would find the minimum and maximum of consecutive dates only, group the results using the Name column and then list the minimum and maximum dates as a 'to and from' string in one column.

我在尝试仅列出连续日期时遇到问题.请注意，上面的第三个条目有自己的条目，因为它与前面条目中A"的日期范围不连续.

I'm having problems trying to list consecutive dates only. Note that the third entry above gets its own entry because it is not consecutive with the date range for 'A' in the earlier entry.

请注意:这是特定于 SQL Server 2008 的，它不允许使用 LAG 函数.

Please note: This is specific to SQL Server 2008, which does not allow use of the LAG function.

编辑 2:McNets 提供的原始答案在 SQL Server 2012 上运行良好.我已将其包含在此处，因为如果您有 SQL Server 2012 以后会更好.

EDIT 2: The original answer supplied by McNets worked fine on SQL Server 2012. I've included it here as it's better if you have SQL Server 2012 onwards.

;WITH CalcDiffDays AS
(
    SELECT Date, Name,
    CONCAT (Name, CAST(DATEDIFF(DAY, LAG(Date, 1, Date - 1) OVER (PARTITION BY Name ORDER BY Name, Date), Date) AS VARCHAR(10))) AS NumDays
    FROM @tmpTable
)
SELECT CONCAT(CONVERT(VARCHAR(20), MIN(Date), 102), ' - ', CONVERT(VARCHAR(20), MAX(Date), 102)) AS [Data Range], Name
FROM CalcDiffDays
GROUP BY NumDays, Name;

推荐答案

首先，我在整个表格中添加了一个行号.

First I've added a row number to the whole table.

WITH RowN AS
(
    SELECT Date, Name, ROW_NUMBER() OVER (ORDER BY Name, Date) RN
    FROM #T
)

然后我将这个表与它本身结合起来只是为了计算日期之间的天数.

Then I've joined this table with itself just to calculate days between dates.

,CalcDiffDays AS
(
    SELECT RowN.Date, RowN.Name,
        ISLAND = RowN.Name + 
        CASE  
           WHEN RowN.RN > 1 AND RowN.Name = R2.Name THEN CAST(DATEDIFF(day, R2.Date, RowN.Date) AS VARCHAR(20))
           ELSE '1'
        END
    FROM RowN
         LEFT JOIN RowN R2 ON R2.RN = RowN.RN-1
)

差距.同名的连续日期之间相差多少天.

GAPS. How many days between consecutive dates of the same name.

岛屿.通过将名称添加到计算的天数中.

ISLANDS. By adding the name to the calculated days.

+---------------------+------+---------+
|         Date        | Name | NumDays |
+---------------------+------+---------+
| 07.01.2017 00:00:00 |   A  |    A1   |
+---------------------+------+---------+
| 08.01.2017 00:00:00 |   A  |    A1   |
+---------------------+------+---------+
| 09.01.2017 00:00:00 |   A  |    A1   |
+---------------------+------+---------+
| 12.01.2017 00:00:00 |   A  |    A3   |
+---------------------+------+---------+
| 07.01.2017 00:00:00 |   B  |    B1   |
+---------------------+------+---------+
| 08.01.2017 00:00:00 |   B  |    B1   |
+---------------------+------+---------+
| 09.01.2017 00:00:00 |   B  |    B1   |
+---------------------+------+---------+

第二部分:获取每个岛屿的MIN和MAX日期.

The second part: get the MIN and MAX Date of each island.

WITH RowN AS
(
    SELECT Date, Name, ROW_NUMBER() OVER (ORDER BY Name, Date) RN
    FROM #T
)
,CalcDiffDays AS
(
    SELECT RowN.Date, RowN.Name,
        ISLAND = RowN.Name + 
        CASE  
           WHEN RowN.RN > 1 AND RowN.Name = R2.Name THEN CAST(DATEDIFF(day, R2.Date, RowN.Date) AS VARCHAR(20))
           ELSE '1'
        END
    FROM RowN
         LEFT JOIN RowN R2 ON R2.RN = RowN.RN-1
)    
SELECT CONVERT(VARCHAR(20), MIN(Date), 102) + ' - ' + CONVERT(VARCHAR(20), MAX(Date), 102) AS [Data Range], Name
FROM CalcDiffDays
GROUP BY ISLAND, Name
ORDER BY MIN(Date);

+-------------------------+------+
|        Data Range       | Name |
+-------------------------+------+
| 2017.01.07 - 2017.01.09 |   A  |
+-------------------------+------+
| 2017.01.07 - 2017.01.09 |   B  |
+-------------------------+------+
| 2017.01.12 - 2017.01.12 |   A  |
+-------------------------+------+

可以在这里查看:http://rextester.com/MHLEEJ50479

这篇关于在自定义列中显示日期范围 - 差距和孤岛的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！