ZJAY ZJAY - 1 month ago 12
SQL Question

Simplifying a SUM() across custom date quarters

When I run the query below, the temp_08.members table created only features one column (Q4'10) which is the sum of all calcs. This is incorrect.

INSTEAD I am trying to create a separate column for each quarter with the sum(calc) for just data inside that quarter. For some reason the query below does not accomplish this.

Second, is there a more efficient way to write this query. I will have 20 quarters, and the only thing that changes in each is the column name (as Q4_16) and the dates for each select.

NOTE: I need to preserve the ability to define each quarter's dates. The
quarters are fiscal quarters that don't always follow standard date intervals or patterns.

SELECT
sum(calc) as Q4_10,
brand
into temp_08.members
from temp_08.source
WHERE date > '09/30/2010' and date <= '12/29/2010'
GROUP BY brand

insert into temp_08.members
SELECT
sum(calc) as Q1_11,
brand
from temp_08.source
WHERE date > '12/29/2010' and date <= '3/28/2011'
GROUP BY brand

insert into temp_08.members
SELECT
sum(calc) as Q2_11,
brand
from temp_08.source
WHERE date > '3/28/2011' and date <= '6/30/2011'
GROUP BY brand

insert into temp_08.members
SELECT
sum(calc) as Q3_11,
brand
from temp_08.source
WHERE date > '6/30/2011' and date <= '9/25/2011'
GROUP BY brand

insert into temp_08.members
SELECT
sum(calc) as Q4_11,
brand
from temp_08.source
WHERE date > '9/25/2011' and date <= '12/31/2011'
GROUP BY brand

Answer

You should really have a table that defines the quarters. In a query, you can do this using a CTE:

with q(qqyy, firstday, lastday) as (
      (values ('Q4_10','2010-10-01', '2010-12-29'),
              . . .
      ))
SELECT q.qqyy, brand, sum(calc) as Q4_10,
into   temp_08.members
from temp_08.source s join
     q
     on s.date >= q.firstday and s.date <= q.lastday
GROUP BY q.qqyy, brand;

EDIT:

An an example of the CTE:

with q(qqyy, firstday, lastday) as (
      (values ('Q4_10', date '2010-10-01', date '2010-12-29'),
              ('Q1_11', date '2010-12-30', date '2011-03-28'),
              ('Q2_11', date '2011-03-29', date '2011-06-30')
      ))
select * from q