Server查询将表与另一个查询的结果连接起来

Question

我正在使用包含事件票的datatable (第一个变量声明中引用的dbo.IncidentDimvw)。

我正在尝试生成一个查询，该查询将枚举从最早的票证到现在的每一个日期，以及每天的票“打开”和“关闭”的数量(可能为0)，以及每天“打开”的票数(不同于“打开的”)。

要完成这个任务，我需要一个日期列表(即日历)，它独立于我的票证数据表。我能够使用一些CTE来完成这项工作，在查询中动态地构建日期列表。

这个部分工作得完美无缺，您可以在下面测试我的代码，如果您想看到它正在运行(为@SSOReportDateRangeBegin提供一个常量值)。

下面显示了我当前的查询，并附有描述性注释。它返回一个完整的日历，从2012年12月27日到当前日期每一天一行(可伸缩到十年)。

我遇到的问题是，我不知道如何将dbo.IncidentDimvw加入到这个“日历”中，以便在每个日期提取门票的开启、关闭和开放计数。

CTE和DISTINCT的选择使我很难理解如何构建这些连接。

我真正想要的是，将日历CTE的最终结果选择为一个普通的旧数据集，从中比较票证--从而将日历创建的逻辑与票证选择的逻辑分离开来。

我已经尝试过将LEFT JOIN-ing dbo.IncidentDimvw用于日历查询，但是当我选择

COUNT(OpenedInc.ID) AS 'Incidents Opened'结果的计数太大了--这表明日历逻辑干扰了票证选择逻辑--或者在这种情况下我可能不知道如何正确地进行GROUP。

/* Begin our date range with the earliest
   incident ticket creation date on record. (currently 12/27/2012)
   CAST is necessary because the CreatedDate column is a datetime value,
   and we specifically want to truncate the "time" portion
   for the purposes of this query.*/
DECLARE @SSOReportDateRangeBegin AS DATE =
    (SELECT MIN(CAST(inc.CreatedDate AS DATE)) FROM dbo.IncidentDimvw inc);

/* State how many years to include in our date range.
   Currently our range includes 10 years. (12/27/2012 - 12/26/2022)
   Later on, our selection will limit results to only dates <=CAST(GETDATE() AS DATE) */
DECLARE @SSOReportDateRangeYears AS INT = 10;
/* @SSOReportDateRangeYears cannot be greater than 31.
   For larger time periods, add CTEs for decades, centuries, millenia, etc. */

/* Calendar calculater based on a row counter with 31 rows.
   Calculates day 1 through day 31 for all 12 months, for 10 years.

   Since some months don't have 31 days, some DATEADD calculations
   will rollover to the next month, causing duplicate date values.
   (e.g. Feb 31st will be interpreted as Mar 3rd in non-leap year,
   Mar 2nd in leap-year, duplicating the date values for March 2nd or 3rd)

   SELECT DISTINCT is used to eliminate duplicate date values,
   giving us a clean and complete calendar to work with. */
WITH [counter](N) AS
(SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1),
SSOReportDays(N) AS (SELECT row_number() OVER (ORDER BY (SELECT NULL)) FROM [counter]),
SSOReportMonths(N) AS (SELECT N - 1 FROM SSOReportDays WHERE N <= 12),
SSOReportYears(N) AS (SELECT N - 1 FROM SSOReportDays WHERE N <= @SSOReportDateRangeYears)

SELECT DISTINCT

CAST(DATEADD(DAY, SSOReportDays.n,
     DATEADD(MONTH, SSOReportMonths.n,
     DATEADD(YEAR, SSOReportYears.n,
     DATEADD(DAY, -1, @SSOReportDateRangeBegin)))) AS DATE) AS CalendarDate
     /* Subtract 1 day from @SSOReportDateRangeBegin,
        because the counter will begin with the following day. */

/* CROSS JOIN to compute every possible combination
   of day(1-31) month(1-12) and year(1-10) */
FROM SSOReportYears CROSS JOIN SSOReportMonths CROSS JOIN SSOReportDays

/* Reduce calendar to <= the current date,
   since incident tickets cannot be created with future dates. */
WHERE CAST(DATEADD(DAY, SSOReportDays.n,
           DATEADD(MONTH, SSOReportMonths.n,
           DATEADD(YEAR, SSOReportYears.n,
           DATEADD(DAY, -1, @SSOReportDateRangeBegin)))) AS DATE) <= CAST(GETDATE() AS DATE)
           /* Subtract 1 day from @SSOReportDateRangeBegin,
              because the counter will begin with the following day. */

Answer 1

没有什么可以阻止您向查询中添加更多的CTE。您已经有4个CTE，为日历日期添加第五个CTE，为表中的票务计数添加第六个CTE，然后使用票证计数离开：

WITH [counter](N) AS
(SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1),
SSOReportDays(N) AS (SELECT row_number() OVER (ORDER BY (SELECT NULL)) FROM [counter]),
SSOReportMonths(N) AS (SELECT N - 1 FROM SSOReportDays WHERE N <= 12),
SSOReportYears(N) AS (SELECT N - 1 FROM SSOReportDays WHERE N <= @SSOReportDateRangeYears),
Calendar AS(
SELECT DISTINCT
CAST(DATEADD(DAY, SSOReportDays.n,
     DATEADD(MONTH, SSOReportMonths.n,
     DATEADD(YEAR, SSOReportYears.n,
     DATEADD(DAY, -1, @SSOReportDateRangeBegin)))) AS DATE) AS CalendarDate
     /* Subtract 1 day from @SSOReportDateRangeBegin,
        because the counter will begin with the following day. */

/* CROSS JOIN to compute every possible combination
   of day(1-31) month(1-12) and year(1-10) */
FROM SSOReportYears CROSS JOIN SSOReportMonths CROSS JOIN SSOReportDays

/* Reduce calendar to <= the current date,
   since incident tickets cannot be created with future dates. */
WHERE CAST(DATEADD(DAY, SSOReportDays.n,
           DATEADD(MONTH, SSOReportMonths.n,
           DATEADD(YEAR, SSOReportYears.n,
           DATEADD(DAY, -1, @SSOReportDateRangeBegin)))) AS DATE) <= CAST(GETDATE() AS DATE)),
TicketCount AS (
SELECT TicketDate, Status, Count(*) cnt -- These are just fake columns. Use your columns
FROM dbo.IncidentDimvw
GROUP BY TicketDate, Status)
SELECT c.CalendarDate, O.cnt, C.cnt, E.cnt
FROM Calendar c
LEFT JOIN TicketCount O ON c.CalendarDate = O.TicketDate AND O.Status = 'Open'
LEFT JOIN TicketCount C ON c.CalendarDate = C.TicketDate AND C.Status = 'Close'
LEFT JOIN TicketCount E ON c.CalendarDate = E.TicketDate AND E.Status = 'etc.' -- keep adding until you get all required statuses
ORDER BY 1