Sample Header Ad - 728x90

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