Gaps in Monthly Summaries - TSQL

Question

I have events - like named storms - that I am summing transactions for across rolling months based on the first date of a transaction for that event.

For instance, the first date for Hurricane Nate was 2017-10-08 - so I have a data set that shows me the event name, first loss date, month number, and begin and end dates of the monthly windows since the event occurred:

Using this, I am joining to the table that stores the transactions to sum the amount to get the totals for each of these months. This part does work - but if an event does not have any transactions in the window, I am not getting any data back - but I need to return a row back with the amount showing 0.00 or NULL - either way would be fine, I just need to show all months for reporting purposes.

Below is some sample data to create #temp tables and the TSQL I am using to generate the monthly summaries. I have tried a LEFT JOIN and an OUTER APPLY to try to make this work, but month numbers 5 and 9 are not appearing in my result set.

Suggestions on how I can get the desired output?

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;

By the way: This is a very good question! Thanks for the DDL, the INSERT and the clear explanation. I'd be happy, if all questions were like this ;-) — Shnugo, Sep 18 '19 at 12:37

score 2 · Accepted Answer · answered Sep 18 '19 at 09:51

You can try it with something along this:

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;

The result

+----------------+---------------+-------------+------------+------------+-------------+
| 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        |
+----------------+---------------+-------------+------------+------------+-------------+

The idea in short:

Whenever you want a gapless list with joined values (if they exist), you start with the fix list and use a LEFT JOIN to get the side values.

Additionally I shifted your WHERE to a better suited place: The #Transaction table is better joined with an ON-clause here.

Hint: I'd suggest to implement a numbers/date table (very handsome in many cases) and use this instead of the CTE. (Find an example here)

Thanks, I was able to get my query working with this. And I will look at the numbers table in the link. — MISNole, Sep 18 '19 at 12:31

Gaps in Monthly Summaries - TSQL

1 Answers1