CODEWITHSUNDEEP
sqlsql-serverdatet-sqlgroup-by
thalacker
thalacker

Reputation: 2837

Group Date Range from other table

I want to group a table based off another tables date range.

I am trying to do the following query where I join a table, Actuals, which has a StartDate.

I want to group its Amounts by Fiscal Months which have StartDate and EndDate and where the Actual.StartDate is between the FiscalMonthsCalendarOptions Start and End dates. I can't do strait months (which would be an easy group by of GROUP BY DATEPART(month, act.StartDate), act.LinkingId) which is what is making this hard.

I attempted the following but obviously you can group on a table alias. I believe the right way to do this is with a partition by and maybe using a lag operator, but I couldn't get it working.

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

I made a quick example table with the example data and expected result

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)

Upvotes: 0

Views: 42

Answers (1)

thalacker
thalacker

Reputation: 2837

One solution is to join the calendar table on the act.StartDate so then you don't have to mess around with the 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

Upvotes: 1

Related Questions