I am trying to identify sum of quantity for each group if date ranges are overlapping. I am using postgresql to solve for it. For example
| id | group | start_date | end_date | quantity |
|---|---|---|---|---|
| 1 | a | 2020-09-11 | 2020-10-09 | 50 |
| 1 | a | 2020-09-11 | 2020-10-31 | 20 |
| 1 | a | 2020-11-01 | 2020-12-01 | 7 |
| 1 | a | 2020-11-15 | 2020-11-20 | 6 |
| 2 | b | 2020-10-06 | 2020-10-30 | 10 |
| 2 | b | 2020-10-09 | 2022-10-17 | 5 |
| 2 | b | 2020-10-15 | 2022-10-26 | 3 |
What I am trying to achieve is the following:
Expected Output: "edited"
| id | group | start_date | end_date | quantity |
|---|---|---|---|---|
| 1 | a | 2020-09-11 | 2020-10-31 | 70 |
| 1 | a | 2020-11-01 | 2020-12-01 | 13 |
| 2 | b | 2020-10-06 | 2020-10-30 | 18 |
would appreciate your help!