tsql - 将日期范围转换为每天记录的 SQL 查询

Question

要求

1. 我有一个数据表，用于保存日期范围内的数据。
2. 允许每条记录与之前的记录重叠(记录有一个 CreatedOn datetime 列)。
3. 如果需要，新记录可以定义自己的日期范围，因此可以与多个旧记录重叠。
4. 每条新的重叠记录都会覆盖它重叠的旧记录的设置。

结果集

我需要获取的是获取使用记录重叠的任何日期范围内的每日数据。它应该每天返回一条记录，其中包含该特定日期的相应数据。

要将范围转换为天数，我正在考虑 numbers/dates table 和用户定义函数 (UDF) 来获取范围内每一天的数据，但我想知道是否还有其他方法(如更好* 甚至更快)这样做的方式，因为我使用的是最新的 SQL Server 2008 R2。

存储的数据

想象一下我存储的数据是这样的

ID | RangeFrom | RangeTo  | Starts | Ends  | CreatedOn (not providing data)
---|-----------|----------|--------|-------|-----------
1  | 20110101  | 20110331 | 07:00  | 15:00
2  | 20110401  | 20110531 | 08:00  | 16:00
3  | 20110301  | 20110430 | 06:00  | 14:00 <- overrides both partially

结果

如果我想获取从 2011 年 1 月 1 日到 2001 年 5 月 31 日的数据，结果表应该如下所示(省略了明显的行):

DayDate | Starts | Ends
--------|--------|------
20110101| 07:00  | 15:00  <- defined by record ID = 1
20110102| 07:00  | 15:00  <- defined by record ID = 1
...                          many rows omitted for obvious reasons
20110301| 06:00  | 14:00  <- defined by record ID = 3
20110302| 06:00  | 14:00  <- defined by record ID = 3
...                          many rows omitted for obvious reasons
20110501| 08:00  | 16:00  <- defined by record ID = 2
20110502| 08:00  | 16:00  <- defined by record ID = 2
...                          many rows omitted for obvious reasons
20110531| 08:00  | 16:00  <- defined by record ID = 2

Answer 1

实际上，由于您正在处理日期，日历表会更有帮助。

Declare @StartDate date
Declare @EndDate date

;With Calendar As
    (
    Select @StartDate As [Date]
    Union All
    Select DateAdd(d,1,[Date])
    From Calendar
    Where [Date] < @EndDate
    )
Select ...
From Calendar
    Left Join MyTable
        On Calendar.[Date] Between MyTable.Start And MyTable.End
Option ( Maxrecursion 0 );

添加

在您的原始帖子中遗漏了有关王牌规则的部分:

Set DateFormat MDY;
Declare @StartDate date = '20110101';
Declare @EndDate date = '20110501';

-- This first CTE is obviously to represent
-- the source table
With SampleData As 
    (
    Select 1 As Id
        , Cast('20110101' As date) As RangeFrom
        , Cast('20110331' As date) As RangeTo
        , Cast('07:00' As time) As Starts
        , Cast('15:00' As time) As Ends
        , CURRENT_TIMESTAMP As CreatedOn
    Union All Select 2, '20110401', '20110531', '08:00', '16:00', DateAdd(s,1,CURRENT_TIMESTAMP )
    Union All Select 3, '20110301', '20110430', '06:00', '14:00', DateAdd(s,2,CURRENT_TIMESTAMP )
    )
    , Calendar As
    (
    Select @StartDate As [Date]
    Union All
    Select DateAdd(d,1,[Date])
    From Calendar
    Where [Date] < @EndDate
    )
    , RankedData As
    (
    Select C.[Date]
        , S.Id
        , S.RangeFrom, S.RangeTo, S.Starts, S.Ends
        , Row_Number() Over( Partition By C.[Date] Order By S.CreatedOn Desc ) As Num
    From Calendar As C
        Join SampleData As S
            On C.[Date] Between S.RangeFrom And S.RangeTo
    )
Select [Date], Id, RangeFrom, RangeTo, Starts, Ends
From RankedData
Where Num = 1   
Option ( Maxrecursion 0 );

简而言之，我优先选择与同一日期重叠的较新行对所有样本数据进行排名。