sqlpostgresql

Insert calculated date - as a new row - below another date and not in separate column


DB Fiddle

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:

  1. Calculate an expire_date which is always 12 months after the collected_date
  2. Put the expire_date in the row below the collected_date. Not in a separate column!
  3. Put total 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?


Solution

  • 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;
    

    Demo