组合递归查询

Question

我有一个递归查询，它为上个月的每个小时创建一行，如下所示；

WITH a AS (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) AS [DateTime]
UNION ALL
SELECT dateadd(hour, 1, [DateTime])
FROM a
WHERE dateadd(hour, 1, [DateTime]) < EOMONTH(GETDATE(), -1)
)
SELECT 
a.*
FROM a
OPTION (maxrecursion 0);

这就产生了这些结果；

DateTime
2020-11-01 00:00:00.000
2020-11-01 01:00:00.000
2020-11-01 02:00:00.000
etc.....

接下来，我有一个查询，计算上个月每个客户每小时的通话次数，作为4周的平均值，如下所示；

SELECT
DATEADD(hour, DATEDIFF(hour, 0,
DATEADD(minute, 30 - DATEPART(minute, Timestamp + '00:30:00.000'),
Timestamp)), 0)  as RoundedToHour,
Campaign,
COUNT(*)/4 AS Average
FROM [Reporting].[dbo].[New_Five9_CallLog] WITH (NOLOCK)
WHERE DATEDIFF(mm, Timestamp, GETDATE()) = 1
AND Call_Type = 'Inbound'
GROUP BY Campaign,
DATEADD(hour, DATEDIFF(hour, 0,
DATEADD(minute, 30 - DATEPART(minute, Timestamp + '00:30:00.000'),
Timestamp)), 0)

这产生了以下结果；

RoundedToHour             Campaign     Average
2020-11-01 02:00:00.000   Client1      0
2020-11-01 04:00:00.000   Client2      2
etc....

我很难把这两者结合起来。我最初的想法是在第二个查询中使用递归查询的CTE作为where子句，但由于CTE必须使用WITH，而我的递归查询必须使用WITH，所以这不会直接起作用，因为不能嵌套WITH。

我要寻找的最终结果是一个查询，它为每个客户生成上个月每个小时的4周平均通话次数。如果有人对如何实现我的最终目标有更好的建议，我愿意改变我的工作方式。

Answer 1

您不能在子查询中声明一个CTE，但可以一起声明多个CTE。所以声明二者，然后LEFT JOIN它们。

WITH a AS (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) AS [DateTime]
UNION ALL
SELECT dateadd(hour, 1, [DateTime])
FROM a
WHERE dateadd(hour, 1, [DateTime]) < EOMONTH(GETDATE(), -1)
), b AS (
SELECT
DATEADD(hour, DATEDIFF(hour, 0,
DATEADD(minute, 30 - DATEPART(minute, Timestamp + '00:30:00.000'),
Timestamp)), 0)  as RoundedToHour,
Campaign,
COUNT(*)/4 AS Average
FROM [Reporting].[dbo].[New_Five9_CallLog] WITH (NOLOCK)
WHERE DATEDIFF(mm, Timestamp, GETDATE()) = 1
AND Call_Type = 'Inbound'
GROUP BY Campaign,
DATEADD(hour, DATEDIFF(hour, 0,
DATEADD(minute, 30 - DATEPART(minute, Timestamp + '00:30:00.000'),
Timestamp)), 0)
)
SELECT a.*, b.*
FROM a
LEFT JOIN b on b.RoundedToHour = a.DateTime
OPTION (maxrecursion 0);