2

I have a table containing transactional events for customers:

table: events | id | cust_id | date | amount | |------|----------|----------------------------|--------| | 78 | 211 | 2010-06-18 20:10:00.000000 | 20 | | 84 | 211 | 2010-07-18 05:53:00.000000 | 35 | | 91 | 211 | 2010-07-27 05:45:00.000000 | 25 | | 2136 | 211 | 2011-02-14 20:07:00.000000 | 5000 | | 2947 | 211 | 2011-02-21 20:29:25.000000 | 22 | | 2945 | 211 | 2011-03-14 20:48:26.000000 | 22 |

I want to produce a year and month aggregation of the sum of the amounts, but where the month periods correspond to the customer's subscription period. This period has a specific start date, for example the 20th of the month.

The query can take the inputs:

  • customer id - the customer to generate the report for
  • start day of period - the day of the month on which the "month" should start

Here is a query that naively groups by month without the custom period:

SELECT extract(YEAR FROM date) AS year, extract(MONTH FROM date) AS month, sum(amount) AS amount FROM events WHERE cust_id = 211 GROUP BY 1, 2 ORDER BY year, month;

Example output of this naive query:

| year | month | amount | |------|-------|--------| | 2010 | 6 | 20 | | 2010 | 7 | 60 | | 2011 | 2 | 5022 | | 2011 | 3 | 22 |

But, given that start day for customer 211 is the 20th of the month, I want the actual output to be:

| year | month | amount | |------|-------|--------| | 2010 | 5 | 20 | from May 20 - June 19 | 2010 | 6 | 35 | from June 20 - July 19 | 2010 | 7 | 25 | from July 20 - Aug 19 | 2011 | 1 | 5000 | from Jan 20 - Feb 19 | 2011 | 2 | 44 | from Feb 20 - March 19

I'm at a loss for how to go from my naive query to the query I need to generate this output.

Improve this question
4
  • Why does June 18 falls into the range of June 20 - July 19? Commented May 5, 2017 at 13:06
  • It shouldn't, instead it should fall into May 20 - June 19. I've updated the answer accordingly. Commented May 5, 2017 at 13:39
  • Does the start day vary from customer to customer? Or is it always the 20th? Commented May 5, 2017 at 15:27
  • It varies from customer to customer. Commented May 5, 2017 at 16:23

2 Answers 2

Reset to default
0

First it calculates month according to next formula:

Month = If day < 20 then, if month = 1 then month = 12 else previous month

Obviously you can change day 20 by no matter which other day.

NOTE: As far as I understand, this date 2010-06-18 20:10:00.000000 should returns Month=5 due Day=18

with calcMonth as ( SELECT extract(YEAR FROM date) AS year, case when extract(day from date) < 20 then (case when extract(MONTH FROM date) = 1 then 12 else extract(MONTH FROM date) - 1 end) else extract(MONTH FROM date) end AS month, amount, cust_id FROM events ) SELECT year, month, sum(amount) AS amount FROM calcMonth WHERE cust_id = 211 GROUP BY 1, 2 ORDER BY year, month; 
 year | month | amount :--- | :---- | -----: 2010 | 5 | 20 2010 | 6 | 35 2010 | 7 | 25 2011 | 1 | 5000 2011 | 2 | 44

dbfiddle here

These are values returned by CTE:

 SELECT date as Date, extract(YEAR FROM date) AS Year, extract(day from date) AS Day, extract(MONTH FROM date) AS Month, case when extract(day from date) < 20 then (case when extract(MONTH FROM date) = 1 then 12 else extract(MONTH FROM date) - 1 end) else extract(MONTH FROM date) end AS CalMonth, Amount FROM events 
 date | year | day | month | calmonth | amount :------------------ | :--- | :-- | :---- | :------- | -----: 2010-06-18 20:10:00 | 2010 | 18 | 6 | 5 | 20 2010-07-18 05:53:00 | 2010 | 18 | 7 | 6 | 35 2010-07-27 05:45:00 | 2010 | 27 | 7 | 7 | 25 2011-02-14 20:07:00 | 2011 | 14 | 2 | 1 | 5000 2011-02-21 20:29:25 | 2011 | 21 | 2 | 2 | 22 2011-03-14 20:48:26 | 2011 | 14 | 3 | 2 | 22

dbfiddle here

Improve this answer
2
  • Great! I will try this out, can you explain what the if day = 1 then 12 else previous bit means? Why 12? Commented May 5, 2017 at 12:53
  • Sorry, if Month=1 then Month=12, it's a typo. Commented May 5, 2017 at 12:54
0

May be you need something like this:

SELECT extract(YEAR FROM (date - '20 day'::interval)) AS year, extract(MONTH FROM (date - '20 day'::interval)) AS month, sum(amount) AS amount FROM events WHERE cust_id = 211 GROUP BY 1, 2 ORDER BY year, month;
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.