对于两个日期之间的每一天，在“开始”/“结束”列中添加具有相同信息但仅在当天的行。

Question

我有一张表，类型为varchar、datetime、datetime

NAME | START | END
Bob  | 10/30 | 11/2

我可以查找什么SQL查询，以了解如何使该表成为：

NAME | START | END
Bob  | 10/30 | 10/30
Bob  | 10/31 | 10/31
Bob  | 11/01 | 11/01
Bob  | 11/02 | 11/02

这是只运行一次，并在一个非常小的数据集。优化是不必要的。

Answer 1

也许你需要一个Recursive CTE。

CREATE TABLE #dates(NAME  VARCHAR(50),START DATETIME,[END] DATETIME)

INSERT INTO #dates
VALUES      ('Bob','2014-10-30','2014-11-02')

DECLARE @maxdate DATETIME = (SELECT Max([end]) FROM   #dates);

WITH cte
     AS (SELECT NAME,
                START,
                [END]
         FROM   #dates
         UNION ALL
         SELECT NAME,
                Dateadd(day, 1, start),
                Dateadd(day, 1, start)
         FROM   cte
         WHERE  start < @maxdate)
SELECT *
FROM   cte 

输出:

name    START       END
----    ----------  ----------
Bob     2014-10-30  2014-10-30
Bob     2014-10-31  2014-10-31
Bob     2014-11-01  2014-11-01
Bob     2014-11-02  2014-11-02

Answer 2

您可以使用递归的cte来完成这一任务：

;with cte AS (SELECT Name,Start,[End]
              FROM YourTable
              UNION  ALL
              SELECT Name
                    ,DATEADD(day,1,Start)
                    ,[End]
              FROM cte
              WHERE Start < [End])
SELECT Name, Start, Start AS [End]
FROM cte

然而，我建议创建一个日历表并加入它：

SELECT a.Name,b.CalendarDate AS Start, b.CalendarDate AS [End]
FROM YourTable a
JOIN tlkp_Calendar b
  ON b.CalendarDate BETWEEN a.[Start] AND a.[End]

这两个查询的演示：SQL Fiddle

Answer 3

我在这个问题/答案中遇到的问题是，这仅仅是为了一个记录。我在这个答案中找到了一个简单有效的解决方案-- SQL如何将带日期范围的行转换为每个日期的多行。。

"RichardTheKiwi“的解决方案包括根据整数表(list)添加新的日期记录，并通过使用datediff函数计算日期范围连接到源表。可以直接从Server主数据库(SELECT master..spt_values WHERE v.type='P')中提取整数列表。这

谷歌搜索术语sql spt_values和有许多有趣的博客文章关于这个表。例如..。

• 生成不带循环的集合或序列.第2部分
• 值- Server

全面解决办法：

--NAME | START | END
--Bob  | 10/30 | 11/2

DECLARE @SampleData as table 
    (PersonName nvarchar(50), StartDate date, EndDate date)
INSERT INTO @SampleData
    (PersonName, StartDate, EndDate)
VALUES
    ('Bob', '2019-10-30', '2019-11-02')
    , ('Joe', '2019-10-30', '2019-11-05')
;

WITH 
cteSampleData_RecordAdded AS
-- NOTE: Range record converted to daily records for 'SampleData'
(
    SELECT 
        T1.PersonName
        , T1.StartDate
        , T1.EndDate
        , DATEADD(d,v.number,T1.StartDate) AS [NewRecordDate]
        , DATEDIFF(day, T1.StartDate, T1.EndDate)+1 AS [QDaysActive]
    FROM 
        @SampleData T1
        -- Adds a record for each date in the range
        JOIN MASTER..spt_values v 
            ON v.type='P'AND v.number BETWEEN 0 AND datediff(d, T1.StartDate, T1.EndDate)
)

select * from cteSampleData_RecordAdded

结果：

+------------+------------+-----------+---------------+-------------+
| PersonName | StartDate  | EndDate   | NewRecordDate | QDaysActive |
+------------+------------+-----------+---------------+-------------+
| Bob        | 10/30/2019 | 11/2/2019 | 10/30/2019    | 4           |
+------------+------------+-----------+---------------+-------------+
| Bob        | 10/30/2019 | 11/2/2019 | 10/31/2019    | 4           |
+------------+------------+-----------+---------------+-------------+
| Bob        | 10/30/2019 | 11/2/2019 | 11/1/2019     | 4           |
+------------+------------+-----------+---------------+-------------+
| Bob        | 10/30/2019 | 11/2/2019 | 11/2/2019     | 4           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 10/30/2019    | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 10/31/2019    | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 11/1/2019     | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 11/2/2019     | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 11/3/2019     | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 11/4/2019     | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 11/5/2019     | 7           |
+------------+------------+-----------+---------------+-------------+