I have a challenge where I have to calculate rolling mean of previous quarter, I broke down it in steps and trying to calculate sum of transactions from previous quarter.

SUM(CASE WHEN QUARTER(TO_DATE(date)) = QUARTER('2022-05-31') - 1 THEN amount else 0 END) as prev_q_sum

it will work for current year only but if I am standing in Q1 of 2022 previous will be Q4 of 2021 and the above query will not work.

How can I solve this problem?

CodePudding user response：

Manipulate the date, not the result.

A quarter is 3 months long, extract 3 months from the date and get the quarter for that calculated date.

Please note, that it does not really matter on which day you do the calculations as long as the resulting date is in the quarter you are looking for.

You also want to add the year to the packet of information to accurately pinpoint the period you are looking for.

CodePudding user response：

Here is one way that you could tackle this question. I turned your date into a decimal value. This simplifies finding previous quarter located in the previous year.

I made a temporary table to hold test values. Then I created another temporary table to hold the values of the quarters.

It was made like this to simplify the example of how you would compare the quarters.

Here is what the code looks like:

Declare @TestData TABLE
(
    testdate        datetime,
    amount          decimal(10,2)
);

INSERT INTO @TestData(testdate, amount) select '2021-11-11 7:11:11.11', 425.25
INSERT INTO @TestData(testdate, amount) select '2022-01-01 0:10:49.12', 27.11
INSERT INTO @TestData(testdate, amount) select '2022-02-05 2:20:21.12', 31.11
INSERT INTO @TestData(testdate, amount) select '2022-03-10 4:30:27.12', 215.11
INSERT INTO @TestData(testdate, amount) select '2022-04-15 6:40:35.12', 97.11
INSERT INTO @TestData(testdate, amount) select '2022-05-20 8:50:07.12', 11.11
INSERT INTO @TestData(testdate, amount) select '2022-06-25 10:00:32.12', 1.11
INSERT INTO @TestData(testdate, amount) select '2022-07-30 12:10:01.12', 532.11
INSERT INTO @TestData(testdate, amount) select '2022-08-01 14:20:14.12', 87.11
INSERT INTO @TestData(testdate, amount) select '2022-09-05 16:30:16.12', 35.11
INSERT INTO @TestData(testdate, amount) select '2022-10-10 18:40:16.12', 18.11
INSERT INTO @TestData(testdate, amount) select '2022-11-15 20:50:18.12', 65.11
INSERT INTO @TestData(testdate, amount) select '2022-12-20 22:55:47.12', 127.11

Declare @TestData2 TABLE
(
    testdate        datetime,
    amount          decimal(10,2),
    quarter         decimal(6,2)
);

INSERT INTO @TestData2(testdate, amount, quarter)
select t1.testdate,
       t1.amount,
       datepart(year, testdate)  
       ( case when datepart(month, testdate) between 1 and 3 then 0.00
              when datepart(month, testdate) between 4 and 6 then 0.25
              when datepart(month, testdate) between 7 and 9 then 0.50
              when datepart(month, testdate) between 10 and 12 then 0.75
              else null end
        ) as quarter
from @TestData as t1

Declare @TestData3 TABLE
(
    quarter         decimal(6,2),
    amount          decimal(10,2)
);

INSERT INTO @TestData3(quarter, amount)
select distinct
q1.quarter,
sum(q1.amount) over(partition by q1.quarter)
from @TestData2 as q1

select distinct
r1.quarter,
r1.amount as q_total,
r2.quarter as prev_quarter,
r2.amount as prev_q_total
from @TestData3 as r1
left join @TestData3 as r2
on r2.quarter = r1.quarter -0.25

And here is what the result looks like: