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

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:

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.

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.