1

I have an Activity table with Type,StartDate,EndDate and Amount. I want to calculate the sum of amounts grouped by Type for all the quarters using start date and enddates.

For example, if I have StartDate and EndDate falls between Jan to Mar then sum of all the records' amounts related to that quarter should be calculated.

And I want to do the same for the remaining records.

CREATE TABLE activity (id int(11) ,Type varchar(10) ,StartDate date ,EndDate date ,Amount int(11) ); INSERT INTO activity VALUES (1,'Type1','2021-01-15','2021-02-25',10000), (2,'Type1','2021-01-25','2021-02-25',10000), (3,'Type2','2021-08-05','2021-09-25',15000), (4,'Type3','2021-10-15','2021-12-25',5000);

This is the expected output.

EXPECTED OUTPUT:

Type T1 T2 T3 T4 Type1 20000 0 0 0 Type2 0 0 15000 0 Type3 0 0 0 5000 T1,T2,T3,T4 are quarters of Year T1 -> Jan TO Mar T2 -> April TO June T3 -> July TO September T4 -> October TO December

I have tried a query. I have given that query in the online editor.

This is the Online editor link with sample data.

Is there anything I'm missing in my query to fetch the correct response?

Improve this question
7
  • Your output doesn't make sense, because the 2000 from the 2nd quarter is actually spead out across T2 and T3. Please edit your question with the output you really want here. Commented Jan 25, 2021 at 8:18
  • It's nice that date ranges never straddle quarters Commented Jan 25, 2021 at 8:26
  • Hey, @TimBiegeleisen , I've updated the expected result. Could you please look into that and suggest me where am doing wrong and how to achieve the solution Commented Jan 25, 2021 at 9:33
  • @Aravind I'm not going to edit my answer, but here is a demo using my current query along with updated data from above. Commented Jan 25, 2021 at 9:36
  • 1
    @Aravind WITH Activity (...) is a common table expression, I am using this in lieu of going to the trouble of defining and populating a formal table. If your scope already has an Activity table, then just delete WITH Activity (...) ; you don't need it. Commented Jan 25, 2021 at 9:42

1 Answer 1

Reset to default
2

You may handle this requirement with the help of a calendar table, which in this case maintains the date ranges for each quarter:

SELECT a.Type, SUM(CASE WHEN quarter = 'T1' THEN a.Amount ELSE 0 END) AS T1, SUM(CASE WHEN quarter = 'T2' THEN a.Amount ELSE 0 END) AS T2, SUM(CASE WHEN quarter = 'T3' THEN a.Amount ELSE 0 END) AS T3, SUM(CASE WHEN quarter = 'T4' THEN a.Amount ELSE 0 END) AS T4 FROM Activity a INNER JOIN ( SELECT 'T1' AS quarter, '2021-01-01' AS QuarterStart, '2021-04-01' AS QuarterEnd UNION ALL SELECT 'T2', '2021-04-01', '2021-07-01' UNION ALL SELECT 'T3', '2021-07-01', '2021-10-01' UNION ALL SELECT 'T4', '2021-10-01', '2022-01-01' ) q ON a.StartDate < q.QuarterEnd AND a.EndDate >= q.QuarterStart GROUP BY a.Type;

screen capture from demo link below

Demo

Note that I changed your sample data, because the current Type 2 data actually spans both the 2nd and 3rd quarters, and you did not make it clear how the accounting should work in this case. So, I changed the end date for that data to 2020-06-30 to ensure that it only falls in the 2nd quarter.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Note: The subquery I aliased as q above should probably exist as a separate table. Call it quarters or something like that. It can also contain quarters from more than 1 year.
Thank you and ya I did the same. I created a view for the quarter and joined in the main query. It worked as expected

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.