T-SQL - 在日期范围内过滤日期

Question

代码：

DECLARE @T1 TABLE ([ID] INT, [WD] DATE, [SD] DATETIME, [ED] DATETIME, [Val] INT)
INSERT INTO @T1 ( [ID], [WD], [SD], [ED], [VAL] )
VALUES  ( 1, '2016-02-14', '2016-02-14 08:00:00', '2016-02-14 16:30:00', 12 )
        ,( 1, '2016-02-14', '2016-02-16 08:00:00', '2016-02-16 16:30:00', 15 )
        ,( 3, '2016-02-14', '2016-02-16 08:00:00', '2016-02-16 16:30:00', 12 )
        ,( 1, '2016-02-21', '2016-02-22 08:00:00', '2016-02-22 16:30:00', 100 )
        ,( 2, '2016-02-21', '2016-02-25 08:00:00', '2016-02-25 16:30:00', 124 )
        ,( 3, '2016-03-20', '2016-03-21 08:00:00', '2016-03-21 16:30:00', 10 )
        ,( 3, '2016-04-17', '2016-04-17 08:00:00', '2016-04-17 16:30:00', 8 );

DECLARE @StartDate DATE
        , @EndDate DATE;

SELECT @StartDate = MIN(WD) FROM @T1
SELECT @EndDate = DATEADD(DAY,6,MAX(WD)) FROM @T1;

DECLARE @T2 TABLE (D DATE) -- Table generated to get ALL dates between the minium WD (Min Sunday) and 6 days after the maximum WD (Max Sunday + 6 days to complete the week date range)
INSERT INTO @T2 ( [D] )
SELECT  DATEADD(DAY, NBR - 1, @StartDate)
FROM    ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS NBR
          FROM   sys.columns c
        ) NBR
WHERE   NBR - 1 <= DATEDIFF(DAY, @StartDate, @EndDate);

SELECT * FROM @T1;
SELECT * FROM @T2;

描述：

@T1是一个预设的数据表，其中包含特定日期， [ID]是某种标识字段，[WD]表示不同的一周开始时间（星期日），[SD]表示在该[WD]周内的某天的开始时间，[ED]表示在该[WD]周内的某天的结束时间，[Val]为该ID / WD / SD / ED记录分配的某个值

@T2是我定义和填充的表，以获得MIN([WD])和MAX([WD])+6天之内的所有日期（以完成本周）

目标：

返回@T1数据以及那些[ID]/[WD]周缺失的任何日期。(请参考期望输出以更好地理解)

期望结果（从Excel复制，因此格式不同，但没关系）

[ID][WD]        [SD]            [ED]            [Val]
1   2/14/2016   2/14/16 8:00    2/14/16 16:30   12
1   2/14/2016   2/15/16 00:00   2/15/16 00:00   NULL
1   2/14/2016   2/16/16 8:00    2/16/16 16:30   15
1   2/14/2016   2/17/16 00:00   2/17/16 00:00   NULL
1   2/14/2016   2/18/16 00:00   2/18/16 00:00   NULL
1   2/14/2016   2/19/16 00:00   2/19/16 00:00   NULL
1   2/14/2016   2/20/16 00:00   2/20/16 00:00   NULL
3   2/14/2016   2/14/16 00:00   2/14/16 00:00   NULL
3   2/14/2016   2/15/16 00:00   2/15/16 00:00   NULL
3   2/14/2016   2/16/16 8:00    2/16/16 16:30   12
3   2/14/2016   2/17/16 00:00   2/17/16 00:00   NULL
3   2/14/2016   2/18/16 00:00   2/18/16 00:00   NULL
3   2/14/2016   2/19/16 00:00   2/19/16 00:00   NULL
3   2/14/2016   2/20/16 00:00   2/20/16 00:00   NULL
1   2/21/2016   2/21/16 00:00   2/21/16 00:00   NULL
1   2/21/2016   2/22/16 8:00    2/22/16 16:30   100
1   2/21/2016   2/23/16 00:00   2/23/16 00:00   NULL
1   2/21/2016   2/24/16 00:00   2/24/16 00:00   NULL
1   2/21/2016   2/25/16 00:00   2/25/16 00:00   NULL
1   2/21/2016   2/26/16 00:00   2/26/16 00:00   NULL
1   2/21/2016   2/27/16 00:00   2/27/16 00:00   NULL
2   2/21/2016   2/21/16 00:00   2/21/16 00:00   NULL
2   2/21/2016   2/22/16 00:00   2/22/16 00:00   NULL
2   2/21/2016   2/23/16 00:00   2/23/16 00:00   NULL
2   2/21/2016   2/24/16 00:00   2/24/16 00:00   NULL
2   2/21/2016   2/25/16 8:00    2/25/16 16:30   124
2   2/21/2016   2/26/16 00:00   2/26/16 00:00   NULL
2   2/21/2016   2/27/16 00:00   2/27/16 00:00   NULL
3   3/20/2016   3/20/16 00:00   3/20/16 00:00   NULL
3   3/20/2016   3/21/16 8:00    3/21/16 16:30   10
3   3/20/2016   3/22/16 00:00   3/22/16 00:00   NULL
3   3/20/2016   3/23/16 00:00   3/23/16 00:00   NULL
3   3/20/2016   3/24/16 00:00   3/24/16 00:00   NULL
3   3/20/2016   3/25/16 00:00   3/25/16 00:00   NULL
3   3/20/2016   3/26/16 00:00   3/26/16 00:00   NULL
3   4/17/2016   4/17/16 8:00    4/17/16 16:30   8
3   4/17/2016   4/18/16 00:00   4/18/16 00:00   NULL
3   4/17/2016   4/19/16 00:00   4/19/16 00:00   NULL
3   4/17/2016   4/20/16 00:00   4/20/16 00:00   NULL
3   4/17/2016   4/21/16 00:00   4/21/16 00:00   NULL
3   4/17/2016   4/22/16 00:00   4/22/16 00:00   NULL
3   4/17/2016   4/23/16 00:00   4/23/16 00:00   NULL

我尝试使用交叉连接等技术，但无法获得所需的输出。

- 007

Answer 1

我已经移除了你的@T2，并使用了我的@T3。

DECLARE @T1 TABLE ([ID] INT, [WD] DATE, [SD] DATETIME, [ED] DATETIME, [Val] INT)
INSERT INTO @T1 ( [ID], [WD], [SD], [ED], [VAL] )
VALUES  ( 1, '2016-02-14', '2016-02-14 08:00:00', '2016-02-14 16:30:00', 12 )
        ,( 1, '2016-02-14', '2016-02-16 08:00:00', '2016-02-16 16:30:00', 15 )
        ,( 3, '2016-02-14', '2016-02-16 08:00:00', '2016-02-16 16:30:00', 12 )
        ,( 1, '2016-02-21', '2016-02-22 08:00:00', '2016-02-22 16:30:00', 100 )
        ,( 2, '2016-02-21', '2016-02-25 08:00:00', '2016-02-25 16:30:00', 124 )
        ,( 3, '2016-03-20', '2016-03-21 08:00:00', '2016-03-21 16:30:00', 10 )
        ,( 3, '2016-04-17', '2016-04-17 08:00:00', '2016-04-17 16:30:00', 8 );

DECLARE @T3 TABLE ([ID] INT, [WD] DATE, [SD] DATETIME, [ED] DATETIME);
WITH cte AS (
SELECT DISTINCT ID, WD
FROM
    @T1
)
INSERT INTO @T3 (ID, WD, SD, ED)
SELECT ID, WD, DATEADD(DAY,n,WD), DATEADD(DAY,n,WD)
FROM
    cte
    CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6)) AS ADDED(n);

SELECT t3.ID, t3.WD,COALESCE(t1.SD,t3.SD) AS SD, COALESCE(t1.ED,t3.ED) AS ED, t1.Val
FROM @T3 t3
    LEFT JOIN @T1 t1 ON t3.ID=t1.ID AND CONVERT(DATE,t1.SD)=CONVERT(DATE,t3.SD)

- DVT

---

感谢您提供这个创意解决方案。它返回了预期的数据。 :) - 007

Answer 2

这将是你的源数据与你创建的时间轴进行左连接。

我无法确定ID是如何分配的，但当我使用COALESCE来填充缺失的值时，这将返回您想要的输出（除了ID）。

SELECT  [t1].[ID]
       ,COALESCE([t1].[WD], DATEADD(DAY, 1 - DATEPART(WEEKDAY, [t2].[D]), [t2].[D])) AS WD
       ,COALESCE([t1].[SD], [t2].[D]) AS SD
       ,COALESCE([t1].[ED], [t2].[D]) AS ED
       ,[t1].[Val]
FROM    @T2 [t2]
LEFT JOIN @T1 [t1]
ON      [t2].[D] = CONVERT(DATE, [t1].[SD]);

- zwitschi

---

谢谢您提供这个解决方案。它将在未来的不同任务中派上用场。 :) - 007