时间戳

首页 > 解决方案 > 每月摘要中的差距 - TSQL

tsql - 每月摘要中的差距 - TSQL

问题描述

我有一些事件——比如命名风暴——我根据该事件的交易的第一个日期对整个滚动月份的交易进行汇总。

例如，飓风内特的第一个日期是 2017 年 10 月 8 日 - 所以我有一个数据集，显示事件名称、第一个损失日期、月份数以及事件发生后每月窗口的开始和结束日期：

在此处输入图像描述

使用它，我加入到存储交易的表中，以对金额求和以获得这些月份中的每个月的总数。这部分确实有效 - 但如果一个事件在窗口中没有任何事务，我不会返回任何数据 - 但我需要返回一行返回的金额显示 0.00 或 NULL - 无论哪种方式都可以，我只是需要显示所有月份以用于报告目的。

在此处输入图像描述

下面是一些用于创建#temp 表的示例数据以及我用来生成每月摘要的 TSQL。我已经尝试过 aLEFT JOIN和 anOUTER APPLY来尝试完成这项工作，但月份 5 和 9 没有出现在我的结果集中。

关于如何获得所需输出的建议？

CREATE TABLE #NamedEvents
(
    Catastrophe VARCHAR (60),
    FirstLossDate DATE,
    MonthNumber INT,
    BeginDate DATE,
    EndDate DATE
);
INSERT INTO #NamedEvents
VALUES
('Hurricane Nate', N'2017-10-08', 1, N'2017-10-08', N'2017-11-08'),
('Hurricane Nate', N'2017-10-08', 2, N'2017-11-08', N'2017-12-08'),
('Hurricane Nate', N'2017-10-08', 3, N'2017-12-08', N'2018-01-08'),
('Hurricane Nate', N'2017-10-08', 4, N'2018-01-08', N'2018-02-08'),
('Hurricane Nate', N'2017-10-08', 5, N'2018-02-08', N'2018-03-08'),
('Hurricane Nate', N'2017-10-08', 6, N'2018-03-08', N'2018-04-08'),
('Hurricane Nate', N'2017-10-08', 7, N'2018-04-08', N'2018-05-08'),
('Hurricane Nate', N'2017-10-08', 8, N'2018-05-08', N'2018-06-08'),
('Hurricane Nate', N'2017-10-08', 9, N'2018-06-08', N'2018-07-08'),
('Hurricane Nate', N'2017-10-08', 10, N'2018-07-08', N'2018-08-08');

CREATE TABLE #Transactions
(
    Catastrophe VARCHAR (50),
    TransactionDate DATE,
    Amount NUMERIC (21, 4)
);

INSERT INTO #Transactions
(
    Catastrophe,
    TransactionDate,
    Amount
)
VALUES
('Hurricane Nate', '20171015', 81324.0000),
('Hurricane Nate', '20171115', 104842.0000),
('Hurricane Nate', '20171116', 5000.0000),
('Hurricane Nate', '20171216', -2000.0000),
('Hurricane Nate', '20171218', -847.0000),
('Hurricane Nate', '20180118', -11453.0000),
('Hurricane Nate', '20180318', -3500.0000),
('Hurricane Nate', '20180320', -70.0000),
('Hurricane Nate', '20180420', -1530.0000),
('Hurricane Nate', '20180520', 4250.0000),
('Hurricane Nate', '20180720', 10370.0000);

SELECT * FROM #NamedEvents;
SELECT * FROM #Transactions;

SELECT ne.Catastrophe,
       ne.FirstLossDate,
       ne.MonthNumber,
       ne.BeginDate,
       ne.EndDate,
       SUM ( t.Amount ) AS Amount
FROM #NamedEvents AS ne
    OUTER APPLY #Transactions AS t
WHERE t.Catastrophe = ne.Catastrophe
      AND ne.BeginDate <= t.TransactionDate
      AND ne.EndDate > t.TransactionDate
GROUP BY ne.Catastrophe,
         ne.FirstLossDate,
         ne.MonthNumber,
         ne.BeginDate,
         ne.EndDate;

DROP TABLE IF EXISTS #NamedEvents;
DROP TABLE IF EXISTS #Transactions;

标签： tsqlsql-server-2016

解决方案

你可以试试这个：

WITH MonthNumbers AS
(SELECT MonthNumber FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) t(MonthNumber))
SELECT ne.Catastrophe,
       ne.FirstLossDate,
       mn.MonthNumber,
       ne.BeginDate,
       ne.EndDate,
       SUM ( t.Amount ) AS Amount
FROM MonthNumbers mn
    LEFT JOIN #NamedEvents AS ne ON mn.MonthNumber=ne.MonthNumber
    LEFT JOIN #Transactions AS t  ON t.Catastrophe = ne.Catastrophe
                                 AND ne.BeginDate <= t.TransactionDate
                                 AND ne.EndDate > t.TransactionDate
GROUP BY ne.Catastrophe,
         ne.FirstLossDate,
         mn.MonthNumber,
         ne.BeginDate,
         ne.EndDate
ORDER BY mn.MonthNumber;

结果

+----------------+---------------+-------------+------------+------------+-------------+
| Catastrophe    | FirstLossDate | MonthNumber | BeginDate  | EndDate    | Amount      |
+----------------+---------------+-------------+------------+------------+-------------+
| Hurricane Nate | 2017-10-08    | 1           | 2017-10-08 | 2017-11-08 | 81324.0000  |
+----------------+---------------+-------------+------------+------------+-------------+
| Hurricane Nate | 2017-10-08    | 2           | 2017-11-08 | 2017-12-08 | 109842.0000 |
+----------------+---------------+-------------+------------+------------+-------------+
| Hurricane Nate | 2017-10-08    | 3           | 2017-12-08 | 2018-01-08 | -2847.0000  |
+----------------+---------------+-------------+------------+------------+-------------+
| Hurricane Nate | 2017-10-08    | 4           | 2018-01-08 | 2018-02-08 | -11453.0000 |
+----------------+---------------+-------------+------------+------------+-------------+
| Hurricane Nate | 2017-10-08    | 5           | 2018-02-08 | 2018-03-08 | NULL        |
+----------------+---------------+-------------+------------+------------+-------------+
| Hurricane Nate | 2017-10-08    | 6           | 2018-03-08 | 2018-04-08 | -3570.0000  |
+----------------+---------------+-------------+------------+------------+-------------+
| Hurricane Nate | 2017-10-08    | 7           | 2018-04-08 | 2018-05-08 | -1530.0000  |
+----------------+---------------+-------------+------------+------------+-------------+
| Hurricane Nate | 2017-10-08    | 8           | 2018-05-08 | 2018-06-08 | 4250.0000   |
+----------------+---------------+-------------+------------+------------+-------------+
| Hurricane Nate | 2017-10-08    | 9           | 2018-06-08 | 2018-07-08 | NULL        |
+----------------+---------------+-------------+------------+------------+-------------+
| Hurricane Nate | 2017-10-08    | 10          | 2018-07-08 | 2018-08-08 | 10370.0000  |
+----------------+---------------+-------------+------------+------------+-------------+
| NULL           | NULL          | 11          | NULL       | NULL       | NULL        |
+----------------+---------------+-------------+------------+------------+-------------+
| NULL           | NULL          | 12          | NULL       | NULL       | NULL        |
+----------------+---------------+-------------+------------+------------+-------------+

简而言之：

每当您想要一个包含连接值的无间隙列表（如果它们存在）时，您都可以从修复列表开始并使用 aLEFT JOIN来获取边值。

此外，我将您转移WHERE到了一个更合适的位置：该表最好在此处#Transaction加入- 子句。ON

提示：我建议实现一个数字/日期表（在许多情况下非常漂亮）并使用它而不是 CTE。（在这里找到一个例子）

推荐阅读