CREATE TABLE vouchers (
id SERIAL PRIMARY KEY,
collected_date DATE,
collected_volume INT);
INSERT INTO vouchers(collected_date, collected_volume)VALUES
('2024-02-15', 1),
('2024-03-09', 900),
('2024-04-20', 300),
('2024-04-20', 800),
('2024-05-24', 400),
('2025-01-17', 200),
('2025-02-15', 800),
('2025-02-15', 150);
Expected Result
collected_date | collected_volume |
---|---|
2024-02-15 | 1 |
2025-02-15 | -1 |
2024-03-09 | 900 |
2025-03-09 | -900 |
2024-04-20 | 1100 |
2025-04-20 | -1100 |
2024-05-24 | 400 |
2025-05-24 | -400 |
2025-01-17 | 200 |
2026-01-17 | -200 |
2025-02-15 | 950 |
2026-02-15 | -950 |
To get from the raw data to the expected results these steps are required:
expire_date
which is always 12 months after the collected_date
expire_date
in the row below the collected_date
. Not in a separate column!collected_volume
per collected_date
as a negative number to the row with the expire_date
.If generated row with expire_date
and negative collected_volume
coincides with one of the original, base collected_date
rows, they should stay separate and not be summed up: ('2024-02-15',1)
case generates ('2025-02-15',-1)
which stays separate from ('2025-02-15',950)
.
So far I have been able to develop this query:
select collected_date as collected_date
, (collected_date + interval '12 months')::date as expire_date
, sum(collected_volume) as collected_volume
from vouchers
group by 1,2
order by 1,2;
However, I have no clue what I need to change to display the expire_date
below each collected_date
and not in a separate column.
Do you have any idea how to solve this?
If you just want to view this output, then define the aggregation in a CTE and use a union query:
WITH cte AS (
SELECT collected_date, SUM(collected_volume) AS collected_volume
FROM vouchers
GROUP BY collected_date
)
SELECT collected_date, collected_volume FROM cte
UNION ALL
SELECT collected_date, -1.0*collected_volume FROM cte
ORDER BY collected_date, collected_volume DESC;