sql-server - 有没有办法加入日历表以开始和结束另一个查询的日期？

Question

我有一个带有日期键的表和另一个日历表。我想加入这两者，以便开始和结束之间的所有日期都可以获得数据。那可能吗？

请参阅下表进行说明:

事件表

Well    Activity    DateKey
A       Drill       20190101
A       Drill       20190102
A       Drill       20190106
A       Drill       20190107
A       Drill       20190108
B       Complete    20190107
B       Complete    20190108
B       Complete    20190111
B       Complete    20190115

日历表

Date Key    CalendarDate
20190101    1/1/2019
20190102    1/2/2019
20190103    1/3/2019
...

结果表

Calendar Date   Well    Activity
1/1/2019        A       Drill
1/2/2019        A       Drill
1/3/2019        A       Drill
1/4/2019        A       Drill
1/5/2019        A       Drill
1/6/2019        A       Drill
1/7/2019        A       Drill
1/7/2019        B       Complete
1/8/2019        A       Drill
1/8/2019        B       Complete
1/9/2019        B       Complete
1/10/2019       B       Complete
1/11/2019       B       Complete
1/12/2019       B       Complete
1/13/2019       B       Complete
1/14/2019       B       Complete
1/15/2019       B       Complete
1/16/2019       Null    Null
1/17/2019       Null    Null
1/18/2019       Null    Null
1/19/2019       Null    Null

我尝试了几种不同的连接，但没有任何东西可以填补空白。

SELECT 
    JA.[WellId]
    ,Well.[WellName]
    ,JA.[JobKey]
    ,Job.[JobType]
--  ,MIN(JA.DateKey)
--  ,MAX(JA.DateKey)
    ,Calendar.DisplayDate

FROM [WELLEZ].[PLY_WELLEZ_PRD].[rpt].[JobActivity] JA

Left Outer Join [WELLEZ].[PLY_WELLEZ_PRD].[rpt].[Well] Well on JA.[WellId] = Well.[WellId]
Left Outer Join [WELLEZ].[PLY_WELLEZ_PRD].[rpt].[Job] Job on JA.[JobId] = Job.[JobId]
Left Outer Join [PRODUCTION].[PLY_FV_PRD].[mdm].[Calendar] Calendar on Calendar.CalendarKey = JA.DateKey 

WHERE
    (well.[IsCurrentRecord] = 1 or well.[IsCurrentRecord] is null)
    and (well.[WellHasBeenDeleted] = 0 or Well.WellHasBeenDeleted is null)
    and (Job.[IsCurrentRecord] = 1 OR Job.[IsCurrentRecord] is Null)
    and JA.WellID = 104935.00

Group by 
    Calendar.DisplayDate
    ,Well.[WellName]
    ,Job.[JobType]
    ,JA.[WellId]
    ,JA.[JobKey]


Order by
    Well.[WellName]
    ,MIN(Calendar.CalendarDay)

Answer 1

您需要先找到开始日期和结束日期键，然后使用日历表中的左连接以获得所有日期。

WITH Activity  AS 
(
    SELECT 'A' AS Well,   'Drill'     AS Activity,  20190101 AS DateKey UNION
    SELECT 'A' AS Well,   'Drill'     AS Activity,  20190102 AS DateKey UNION
    SELECT 'A' AS Well,   'Drill'     AS Activity,  20190106 AS DateKey UNION
    SELECT 'A' AS Well,   'Drill'     AS Activity,  20190107 AS DateKey UNION
    SELECT 'A' AS Well,   'Drill'     AS Activity,  20190108 AS DateKey UNION
    SELECT 'B' AS Well,   'Complete'  AS Activity,  20190107 AS DateKey UNION
    SELECT 'B' AS Well,   'Complete'  AS Activity,  20190108 AS DateKey UNION
    SELECT 'B' AS Well,   'Complete'  AS Activity,  20190111 AS DateKey UNION
    SELECT 'B' AS Well,   'Complete'  AS Activity,  20190115 AS DateKey 
) , 
Calendar AS (
    SELECT  20190101 AS [DateKey], CAST('1/1/2019 ' AS DATE)  AS CalendarDate UNION
    SELECT  20190102 AS [DateKey], CAST('1/2/2019 ' AS DATE)  AS CalendarDate UNION
    SELECT  20190103 AS [DateKey], CAST('1/3/2019 ' AS DATE)  AS CalendarDate UNION
    SELECT  20190104 AS [DateKey], CAST('1/4/2019 ' AS DATE)  AS CalendarDate UNION
    SELECT  20190105 AS [DateKey], CAST('1/5/2019 ' AS DATE)  AS CalendarDate UNION
    SELECT  20190106 AS [DateKey], CAST('1/6/2019 ' AS DATE)  AS CalendarDate UNION
    SELECT  20190107 AS [DateKey], CAST('1/7/2019 ' AS DATE)  AS CalendarDate UNION
    SELECT  20190107 AS [DateKey], CAST('1/7/2019 ' AS DATE)  AS CalendarDate UNION
    SELECT  20190108 AS [DateKey], CAST('1/8/2019 ' AS DATE)  AS CalendarDate UNION
    SELECT  20190108 AS [DateKey], CAST('1/8/2019 ' AS DATE)  AS CalendarDate UNION
    SELECT  20190109 AS [DateKey], CAST('1/9/2019 ' AS DATE)  AS CalendarDate UNION
    SELECT  20190110 AS [DateKey], CAST('1/10/2019' AS DATE)  AS CalendarDate UNION
    SELECT  20190111 AS [DateKey], CAST('1/11/2019' AS DATE)  AS CalendarDate UNION
    SELECT  20190112 AS [DateKey], CAST('1/12/2019' AS DATE)  AS CalendarDate UNION
    SELECT  20190113 AS [DateKey], CAST('1/13/2019' AS DATE)  AS CalendarDate UNION
    SELECT  20190114 AS [DateKey], CAST('1/14/2019' AS DATE)  AS CalendarDate UNION
    SELECT  20190115 AS [DateKey], CAST('1/15/2019' AS DATE)  AS CalendarDate UNION
    SELECT  20190116 AS [DateKey], CAST('1/16/2019' AS DATE)  AS CalendarDate UNION
    SELECT  20190117 AS [DateKey], CAST('1/17/2019' AS DATE)  AS CalendarDate UNION
    SELECT  20190118 AS [DateKey], CAST('1/18/2019' AS DATE)  AS CalendarDate UNION
    SELECT  20190119 AS [DateKey], CAST('1/19/2019' AS DATE)  AS CalendarDate 
) 
, CTE_START_END_ACTIVITY AS (
    SELECT
            Well
           ,Activity
           ,MIN(DateKey) AS start_Datekey
           ,MAX(DateKey) AS end_Datekey
        FROM Activity
        GROUP BY
            Well
           ,Activity
)
SELECT
        d.CalendarDate
        ,a.Well
        ,a.Activity
    FROM Calendar d 
    LEFT JOIN CTE_START_END_ACTIVITY a
        ON d.DateKey BETWEEN a.start_Datekey AND end_Datekey
    ORDER BY
        D.CalendarDate
        ,a.Well
        ,a.Activity