其他表中的Group Date Range

Question

我想根据另一个表的日期范围对一个表进行分组。

我试图做以下查询，我加入一个表，Actuals，它有一个StartDate。

我想按具有StartDate和EndDate的财政月对其Amounts进行分组，其中Actual.StartDate位于FiscalMonthsCalendarOptions开始日期和结束日期之间。我不能做六个月(这将是一个简单的GROUP BY DATEPART(month, act.StartDate), act.LinkingId组)，这就是使这变得困难的原因。

我尝试了以下操作，但显然您可以对表别名进行分组。我相信正确的方法是使用partition by，也许使用lag操作符，但我无法让它工作。

SELECT 
act.LinkingId,
SUM(act.Amount) as AmountTotal,
CASE 
WHEN act.StartDate BETWEEN fco.StartDate AND fco.EndDate 
THEN fco.StartDate
ELSE act.StartDate
END AS FiscalStartDate
FROM 
[Actuals] act
INNER JOIN 
FiscalMonthsCalendarOptions fco ON 1 = 1 
WHERE 
act.Amount <> 0
GROUP BY 
FiscalStartDate, act.LinkingId

我用示例数据和预期结果制作了一个快速示例表

DECLARE @actuals TABLE (LinkingId int, StartDate datetime, Amount decimal(18,4))
INSERT INTO @actuals(LinkingId, StartDate, Amount)
VALUES (1, '2021-01-01', 5),
(1, '2021-01-15', 3),
(2, '2021-01-01', 5),
(2, '2021-01-30', 4),
(2, '2021-02-05', 2)
DECLARE @fiscalMonthsCalendarOptions TABLE (StartDate datetime, EndDate datetime, Code varchar(20))
INSERT INTO @fiscalMonthsCalendarOptions (StartDate, EndDate, Code)
VALUES ('2020-12-30', '2021-1-29', 'January'),
('2021-1-30', '2021-2-28', 'Feburary')
-- RESULT DESIRED: (LinkingId, Amount, StartDate)
-- (1, 8, 2020-12-30)
-- (2, 5, 2020-12-30)
-- (2, 6, 2021-1-30)

Answer 1

一个解决方案是在act.StartDate上加入日历表，这样你就不必在partition by上乱搞了

SELECT 
act.LinkingId,
SUM(act.Amount) as AmountTotal,
fco.StartDate
FROM @actuals act
INNER JOIN @fiscalMonthsCalendarOptions fco on act.StartDate between fco.StartDate AND fco.EndDate
GROUP BY fco.StartDate, act.LinkingId