CODEWITHSUNDEEP
sql
user3802773
user3802773

Reputation: 51

logic for splitting revenue into quarters

how do i split up revenue into financial quarters when I have month and year in SQL?

Here is how far I got and not sure where i put this. As you can see I am SQL noob

CASE 
    WHEN month in (1,2,3) and year = 2012 then 'Q1 2012'
    WHEN month in (4,5,6) and year = 2012 then 'Q2 2012'
    WHEN month in (7,8,9) and year = 2012 then 'Q3 2012'
    WHEN month in (10,11,12) and year = 2012 then 'Q4 2012'
    WHEN month in (1,2,3) and year = 2013 then 'Q1 2013'
    WHEN month in (4,5,6) and year = 2013 then 'Q2 2013'
    WHEN month in (7,8,9) and year = 2013 then 'Q3 2013'
    WHEN month in (10,11,12) and year = 2013 then 'Q4 2013'
    WHEN month in (1,2,3) and year = 2013 then 'Q1 2014'
    WHEN month in (4,5,6) and year = 2013 then 'Q2 2014'
  END

Upvotes: 0

Views: 2357

Answers (2)

p.campbell
p.campbell

Reputation: 100607

Try approach it from a set-based perspective. That's what RDBMSs are good at.

Make yourself a Quarter table. It'll be far more maintainable and readable as other devs read your code. Prepopulate this with data reaching out far into the future (say 50 years from now), and you'll never need to worry about it. If/when the business shifts/adjusts its fiscal quarters on the calendar, you'll have made the job of adjusting easy by modifying the rows to suit, and your queries for past/historical will continue to be accurate as the quarters existed back then.

CREATE TABLE Quarter(ID INT, [Desc] varchar(100),
             QuarterStart DATETIME, QuarterEnd DATETIME);
INSERT INTO Quarter(QuarterStart, QuarterEnd, [Desc])
VALUES ('Jan 1 2012', 'Mar 31 2012', 'Q1 2012');
INSERT INTO Quarter(QuarterStart, QuarterEnd, [Desc])
VALUES ('Apr 1 2012', 'Jun 30 2012', 'Q2 2012');

Adjust obviously with a PK, and create an index for this table once and you're set.

Then join this table against your sales data for whatever query you have. e.g.

SELECT SUM(SalesAmount) AS QuarterSales,
       Q.Desc AS InQuarter
FROM   Sales
JOIN   Quarter AS Q 
WHERE  S.SalesDate BETWEEN Q.QuarterStart AND Q.QuarterEnd
GROUP BY Q.Desc;

Upvotes: 0

jdotjdot
jdotjdot

Reputation: 17072

SELECT year, CONCAT("Q", CONVERT(VARCHAR(2), CEILING(month / 3))) AS quarter, revenue
FROM mytable
GROUP BY year, quarter

Without more information about your table or database, this is kind of hard to say, but this here is relatively database-agnostic and hopefully gets the point across.

Rather than use your complicated CASE WHEN, I simply take the month, divide by 3, and round up, which effectively gives me the quarter.

Upvotes: 1

Related Questions