选择并分组相对于特定日期的过去数据

Question

我在两个表中有数据ORDERS和training。为各种客户提供不同类型的培训。我想看看这些培训对相关客户的收入有什么影响。为了实现这一目标，我想查看每个客户从接受培训之日起过去 90 天和未来 90 天的收入。换句话说，如果客户在 2014 年 3 月 30 日接受了培训，我想查看从 2014 年 1 月 1 日到 2014 年 6 月 30 日的收入。我想出了以下查询，它几乎可以完成这项工作：

select 
  o.custno,
  sum(ISNULL(a.revenue,0)) as Revenue,
  o.Date as TrainingDate,
  DATEADD(mm, DATEDIFF(mm, 0, a.created), 0) as RevenueMonth 
from ORDERS a,
  (select distinct custno, max(Date) as Date from Training group by custno) o
where a.custnum = o.custno
  and a.created between DATEADD(day, -90, o.Date) and DATEADD(day, 90, o.Date)
group by o.custno, o.Date, DATEADD(mm, DATEDIFF(mm, 0, a.created), 0)
order by o.custno

此查询的示例输出如下所示：

custno    Revenue    TrainingDate    RevenueMonth
0000100 159.20  2014-06-02 00:00:00.000 2014-03-01 00:00:00.000
0000100 199.00  2014-06-02 00:00:00.000 2014-04-01 00:00:00.000
0000100 79.60   2014-06-02 00:00:00.000 2014-05-01 00:00:00.000
0000100 29.85   2014-06-02 00:00:00.000 2014-06-01 00:00:00.000
0000100 79.60   2014-06-02 00:00:00.000 2014-07-01 00:00:00.000
0000100 99.50   2014-06-02 00:00:00.000 2014-08-01 00:00:00.000
0000250 437.65  2013-02-26 00:00:00.000 2012-11-01 00:00:00.000
0000250 4181.65 2013-02-26 00:00:00.000 2012-12-01 00:00:00.000
0000250 4146.80 2013-02-26 00:00:00.000 2013-01-01 00:00:00.000
0000250 6211.93 2013-02-26 00:00:00.000 2013-02-01 00:00:00.000
0000250 2199.72 2013-02-26 00:00:00.000 2013-03-01 00:00:00.000
0000250 4452.65 2013-02-26 00:00:00.000 2013-04-01 00:00:00.000

所需的输出示例：

如果培训是在 2014 年 3 月 15 日提供的，对于客户编号 100，我需要以下格式的收入数据：

CustNo        Revenue           TrainingDate                      RevenueMonth
100    <Some revenue figure>    March 15 2014        Dec 15 2013 – Jan 14 2014 (Past month 1)
100    <Some revenue figure>    March 15 2014        Jan 15 2014 – Feb 14 2014 (Past month 2)
100    <Some revenue figure>    March 15 2014        Feb 15 2014 – Mar 14 2014 (Past month 3)
100    <Some revenue figure>    March 15 2014        Mar 15 2014 – Apr 14 2014 (Future month 1)
100    <Some revenue figure>    March 15 2014        Apr 15 2014 – May 14 2014 (Future month 2)
100    <Some revenue figure>    March 15 2014        May 15 2014 – Jun 14 2014 (Future month 3)

在这里，只要RevenueMonth列具有与训练日期相关的数据，就不需要采用这种格式。大括号中的"过去"和"未来"月份引用仅用于解释输出，它们不必出现在输出中。

我的查询获取数据并按月对数据进行分组。我希望相对于培训日期对月份进行分组。例如 - 如果培训是在 3 月 15 日进行的，我希望过去一个月是从 2 月 15 日到 3 月 14 日，我的查询不会这样做。我相信在这个查询中进行一些调整可能会达到我所追求的。

任何有关查询的帮助将不胜感激。

Answer 1

类似这些行的内容可能会做你想要的：

select 
  t.custno,
  sum(ISNULL(o.revenue,0)) as Revenue,
  t.maxDate as TrainingDate,
  ((DATEDIFF(day, TrainingDate, o.created) + 89) / 30) - 3 as DeltaMonth
from ORDERS o
  join (select custno, max([Date]) as maxDate from Training group by custno) t
    on o.custnum = t.custno
where o.created
  between DATEADD(day, -89, t.maxDate) and DATEADD(day, 90, t.maxDate)
group by t.custno, t.maxDate, DeltaMonth
order by t.custno

一般策略是计算与训练日期相比的月差(实际上是 30 天(，并按此分组。 此版本使用从训练前 89 天到训练后 90 天，因为如果您从 -90 运行到 +90，那么您将多一天(训练日本身(而不是平均分为 30 天。

查询遵循原始查询的一般结构，但有几项更改：

• 它将DeltaMonth(从 -3 到 2(计算为相对于训练日期的 30 天周期的索引，训练日期是数字 -1 DeltaMonth的最后一天。
• 它在 GROUP BY 子句中使用 DeltaMonth 别名，而不是重复其公式。 这提供了更好的清晰度、简单性和可维护性。
• 我更改了表别名。 我根本无法处理一个名为"ORDERS"的表和别名"o"，后者与前者无关。
• 该查询使用新式联接语法，以便更清晰
• 适当更新GROUP BY条款