How to aggregate transactional records by month year but based on subscription period not calendar months? 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.
First it calculates month according to next formula:

Month = If day &lt; 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) &lt; 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) &lt; 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