Group by arbitary monthly time period
0
votes
2
answers
92
views
I want to group the following data by an user defined period:
+------------+--------+
| DATE | Amount |
+------------+--------+
| 2019-03-12 | 300 |
| 2019-03-15 | 1500 |
| 2019-03-25 | 2500 |
| 2019-03-25 | 3000 |
| 2019-04-04 | 5000 |
| 2019-04-27 | 10000 |
+------------+--------+
However, the start and end of the period does not have to align with the start and end of a calendar month, e.g.:
User A: period start first of month, period end last of month
User B: period start 15th of the month, period end 14th of the following month
User C: period start 7th of the month, period end 6th of the following month
So:
User A:
+--------------+--------+
| Period Start | Amount |
+--------------+--------+
| 2019-01-01 | 0 |
| 2019-02-01 | 0 |
| 2019-03-01 | 7300 |
| 2019-04-01 | 15000 |
| 2019-05-01 | 0 |
| 2019-06-01 | 0 |
| ... | ... |
+--------------+--------+
User B:
+--------------+--------+
| Period Start | Amount |
+--------------+--------+
| 2019-01-15 | 0 |
| 2019-02-15 | 300 |
| 2019-03-15 | 12000 |
| 2019-04-15 | 10000 |
| 2019-05-15 | 0 |
| 2019-06-15 | 0 |
| ... | ... |
+--------------+--------+
User C:
+--------------+--------+
| Period Start | Amount |
+--------------+--------+
| 2019-01-07 | 0 |
| 2019-02-07 | 0 |
| 2019-03-07 | 12300 |
| 2019-04-07 | 10000 |
| 2019-05-07 | 0 |
| 2019-06-07 | 0 |
| ... | ... |
+--------------+--------+
Can this be done in a vendor-agnostic way? If not please show me how it can be done in PostgreSQL 10.x and - if possible - in HSQLDB 2.4.x.
Asked by Marius K.
(103 rep)
Aug 15, 2019, 11:33 AM
Last activity: Aug 18, 2019, 03:11 PM
Last activity: Aug 18, 2019, 03:11 PM