时间戳

首页 > 解决方案 > 动态日期范围内的总和

sql - 动态日期范围内的总和

问题描述

我正在尝试根据每个事件的每月期间汇总总计 - 每月时间窗口基于每个事件的第一次发生。

例如，我有以下三个事件和我试图求和的日期范围：

Catastrophe         MonthNumber  BeginDate  EndDate
Hurricane Barry     1            7/16/2019  8/16/2019
Hurricane Barry     2            8/16/2019  9/13/2019
Hurricane Dorian    1            9/4/2019   9/13/2019
Hurricane Florence  1            9/15/2018  10/15/2018
Hurricane Florence  2            10/15/2018 11/15/2018
Hurricane Florence  3            11/15/2018 12/15/2018
Hurricane Florence  4            12/15/2018 1/15/2019
Hurricane Florence  5            1/15/2019  2/15/2019
Hurricane Florence  6            2/15/2019  3/15/2019
Hurricane Florence  7            3/15/2019  4/15/2019
Hurricane Florence  8            4/15/2019  5/15/2019
Hurricane Florence  9            5/15/2019  6/15/2019
Hurricane Florence  10           6/15/2019  7/15/2019
Hurricane Florence  11           7/15/2019  8/15/2019
Hurricane Florence  12           8/15/2019  9/13/2019

对于飓风巴里，我希望有两个记录：

对于多利安飓风，我们只有一份月度摘要：

自从佛罗伦萨飓风于 2018 年 9 月 15 日发生以来——我们现在有 12 个我正在尝试生成的月度摘要。

我认为这个 TSQL 会给我每月的摘要，但是这个查询没有返回任何数据：

SELECT r.Catastrophe,
       r.MonthNumber,
       SUM ( b.Amount )
FROM TransactionTable AS b
    INNER JOIN #CatastropheDateRanges AS r ON r.Catastrophe = b.Catastrophe
WHERE r.BeginDate >= b.TransactionDate
      AND r.EndDate < b.TransactionDate
GROUP BY r.Catastrophe,
         r.MonthNumber;

以下是我希望结果集的样子：

Catastrophe         Month Number  Total
Hurricane Barry     1             $300.00
Hurricane Barry     2             $350.00
Hurricane Dorian    1             $700.00
Hurricane Florence  1             $300.00
Hurricane Florence  2             $1,000.00
Hurricane Florence  3             $250.00
Hurricane Florence  4             $350.00

获取数据集的样本数据：

CREATE TABLE #CatastropheDateRanges
(
    Catastrophe VARCHAR (60),
    MonthNumber INT,
    BeginDate DATE,
    EndDate DATE
);
INSERT INTO #CatastropheDateRanges
VALUES
('Hurricane Barry', 1, N'2019-07-16', N'2019-08-16'),
('Hurricane Barry', 2, N'2019-08-16', N'2019-09-13'),
('Hurricane Dorian', 1, N'2019-09-04', N'2019-09-13'),
('Hurricane Florence', 1, N'2018-09-15', N'2018-10-15'),
('Hurricane Florence', 2, N'2018-10-15', N'2018-11-15'),
('Hurricane Florence', 3, N'2018-11-15', N'2018-12-15'),
('Hurricane Florence', 4, N'2018-12-15', N'2019-01-15'),
('Hurricane Florence', 5, N'2019-01-15', N'2019-02-15'),
('Hurricane Florence', 6, N'2019-02-15', N'2019-03-15'),
('Hurricane Florence', 7, N'2019-03-15', N'2019-04-15'),
('Hurricane Florence', 8, N'2019-04-15', N'2019-05-15'),
('Hurricane Florence', 9, N'2019-05-15', N'2019-06-15'),
('Hurricane Florence', 10, N'2019-06-15', N'2019-07-15'),
('Hurricane Florence', 11, N'2019-07-15', N'2019-08-15'),
('Hurricane Florence', 12, N'2019-08-15', N'2019-09-13');


SELECT *
FROM #CatastropheDateRanges;


CREATE TABLE #TransactionTable
(
    Catastrophe VARCHAR (50),
    TransactionDate DATE,
    TransactionAmount DECIMAL (19, 4)
);

INSERT INTO #TransactionTable
(
    Catastrophe,
    TransactionDate,
    TransactionAmount
)
VALUES
('Hurricane Barry', '20190716', 100.00),
('Hurricane Barry', '20190720', 150.00),
('Hurricane Barry', '20190725', 50.00),
('Hurricane Barry', '20190825', 350.00),
('Hurricane Dorian', '20190910', 350.00),
('Hurricane Dorian', '20190911', 350.00),
('Hurricane Florence', '20180920', 100.00),
('Hurricane Florence', '20180922', 200.00),
('Hurricane Florence', '20181022', 500.00),
('Hurricane Florence', '20181023', 500.00),
('Hurricane Florence', '20181115', 100.00),
('Hurricane Florence', '20181116', 150.00),
('Hurricane Florence', '20181216', 350.00);

SELECT *
FROM #TransactionTable;

DROP TABLE #TransactionTable;
DROP TABLE #CatastropheDateRanges;

关于如何根据我的#CatastropheDateRanges 表中的开始和结束日期获取这些月度摘要的任何建议？

标签： sqlsql-servertsqlsql-server-2016

解决方案

就问题而言，您只需要反转日期比较。

改变：

WHERE r.BeginDate >= b.TransactionDate AND r.EndDate < b.TransactionDate

到：

WHERE r.BeginDate <= b.TransactionDate AND r.EndDate > b.TransactionDate

DB Fiddle 上的演示：

灾难 | 月数 | （无列名）
:----------------- | ----------: | :---------------
飓风巴里 | 1 | 300.0000        
飓风巴里 | 2 | 350.0000        
飓风多利安 | 1 | 700.0000        
飓风佛罗伦萨 | 1 | 300.0000        
飓风佛罗伦萨 | 2 | 1000.0000       
飓风佛罗伦萨 | 3 | 250.0000        
飓风佛罗伦萨 | 4 | 350.0000

推荐阅读