T-SQL Query with SUM, DATEADD logic

Question

我正在寻找一个可以从值表中计算计算表的查询。 查询必须采用日期值，例如'2017-03-01'，但我需要从 2 个月前的记录中获取值，但它必须是具有相同 ID 的记录。在这种情况下，它必须从 2017-03-01 、2017-02-01、2017-01-01 (993、492、312( 中获取值并汇总在一起 (1,797(，并将其存储在 2017-03-01 记录中，如下所示，来自客户 CustomerID = 1001。

|1001 | 2017-02-01 |   492 |             |
|1001 | 2017-03-01 |   993 |             |
|1002 | 2017-01-01 |   838 |        1797 |

这需要对所有记录完成。 当然，某些记录不能回去减去 2 个月，但这些值可以保持为空。 我真的不知道写这个查询。

有一些测试查询来执行一些步骤，例如：

SELECT SUM(Value) FROM TestTable WHERE Date BETWEEN Date AND DATEADD(month, -2, Date);
+------------+------------+-------+-------------+
| CustomerID |    Date    | Value | Calculation |
+------------+------------+-------+-------------+
|       1001 | 2016-08-01 |   123 |             |
|       1001 | 2016-09-01 |   434 |             |
|       1001 | 2016-10-01 |   423 |             |
|       1001 | 2016-11-01 |   235 |             |
|       1001 | 2016-12-01 |   432 |             |
|       1001 | 2017-01-01 |   312 |             |
|       1001 | 2017-02-01 |   492 |             |
|       1001 | 2017-03-01 |   993 |             |
|       1002 | 2017-01-01 |   838 |             |
|       1002 | 2017-02-01 |   234 |             |
|       1002 | 2017-03-01 |   453 |             |
|       1002 | 2017-04-01 |   838 |             |
|       1003 | 2017-01-01 |   746 |             |
|       1003 | 2017-02-01 |   242 |             |
|       1003 | 2017-03-01 |   432 |             |
|       1004 | 2017-01-01 |   431 |             |
|       1004 | 2017-02-01 |   113 |             |
+------------+------------+-------+-------------+

我想要我的表格如下

+------------+------------+-------+-------------+
| CustomerID |    Date    | Value | Calculation |
+------------+------------+-------+-------------+
|       1001 | 2016-08-01 |   123 | NULL        |
|       1001 | 2016-09-01 |   434 | NULL        |
|       1001 | 2016-10-01 |   423 | 980         |
|       1001 | 2016-11-01 |   235 | 1092        |
|       1001 | 2016-12-01 |   432 | 1090        |
|       1001 | 2017-01-01 |   312 | 979         |
|       1001 | 2017-02-01 |   492 | 1236        |
|       1001 | 2017-03-01 |   993 | 1797        |
|       1002 | 2017-01-01 |   838 | NULL        |
|       1002 | 2017-02-01 |   234 | NULL        |
|       1002 | 2017-03-01 |   453 | 1525        |
|       1002 | 2017-04-01 |   838 | 1525        |
|       1003 | 2017-01-01 |   746 | NULL        |
|       1003 | 2017-02-01 |   242 | NULL        |
|       1003 | 2017-03-01 |   432 | 1420        |
|       1004 | 2017-01-01 |   431 | NULL        |
|       1004 | 2017-02-01 |   113 | NULL        |
+------------+------------+-------+-------------+

希望你能帮到我！ 😉

Answer 1

--First Create Table
create table Testtable(
CustomerID int,
Date date,
Value int
)

--Insert test values
insert into Testtable VALUES(1001,'2016-08-01',123),
(1001,'2016-09-01',434),
(1001,'2016-10-01',423),
(1001,'2016-11-01',235),
(1001,'2016-12-01',432),
(1001,'2017-01-01',312),
(1001,'2017-02-01',492),
(1001,'2017-03-01',993),
(1002,'2017-01-01',838),
(1002,'2017-02-01',234),
(1002,'2017-03-01',453),
(1002,'2017-04-01',838),
(1003,'2017-01-01',746),
(1003,'2017-02-01',242),
(1003,'2017-03-01',432),
(1004,'2017-01-01',431),
(1004,'2017-02-01',113);
--Select Query
SELECT 
CustomerID,
Date,
Value,
CASE WHEN (SELECT COUNT(*) FROM Testtable T4 WHERE T4.CustomerID = T3.CustomerID AND T4.Date < T3.Date) < 2 THEN NULL
ELSE Calculation END AS Calculation
FROM
(SELECT 
*,
(SELECT SUM(T2.Value) FROM Testtable T2 WHERE T.CustomerID = T2.CustomerID AND T2.Date BETWEEN DATEADD(month,-2,T.Date) AND T.Date) AS Calculation
FROM Testtable T) AS T3

Answer 2

这可能需要一些反复试验才能完全正确，但我会试一试，请尝试以下操作：

SELECT CustomerID
, Date
, Value
, Value + 
(SELECT Value from table_name where CustomerID = x.CustomerID and Date = 
DATEADD(m,-1,x.Date)) +
(SELECT Value from table_name where CustomerID = x.CustomerID and Date = 
DATEADD(m,-2,x.Date)) as Calculation
FROM table_name x

请注意，这仅适用于客户 ID/日期是表中的组合键。

希望这有帮助！