CODEWITHSUNDEEP
sqlapache-sparkapache-spark-sql
M_S_N
M_S_N

Reputation: 2810

SQL How to get sum of previous quarter when year changes?

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?

Upvotes: 1

Views: 561

Answers (3)

M_S_N
M_S_N

Reputation: 2810

So, I was able to solve my problem with some help from this SO post.

Since problem was specific to Spark-SQL, some functions mentioned in other two parts weren't working for me. so I used following CASE statement to solve my problem.

,SUM(CASE WHEN TO_DATE(Date) between add_months((date_trunc('quarter',to_timestamp('2022-05-31','yyyy-MM-dd'))),-3) AND date_add( date_trunc('quarter', cast('2022-05-31' as date)), -1 ) THEN 1 ELSE 0 END) AS repayment_CNT_prev_Q

Hopefully, it helps others looking for something similar specifically for Spark SQL.

Please do point out any errors in the above.

Upvotes: 1

Ivar Harris
Ivar Harris

Reputation: 156

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:

quarter q_total prev_quarter prev_q_total
2021.75 425.25 0 0
2022.00 273.33 2021.75 425.25
2022.25 109.33 2022.00 273.33
2022.50 654.33 2022.25 109.33
2022.75 210.33 2022.5 654.33

Upvotes: 1

Pred
Pred

Reputation: 9042

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.

date add_months(date, -3) quarter(date) quarter(add_months(date, -3))
2022-01-01 2021-01-01 1 4
2022-04-01 2022-01-01 2 1
2022-07-01 2022-04-01 3 2
2022-10-01 2022-07-01 4 3

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.

Upvotes: 0

Related Questions