Question 114632 in PostgreSQL Question posted in
The official documentation can be found here.

How to divide annual amount to months in postgresql

I have an annual amount for each subscription with start_date

structure;

userid startDate(dd-mm-yyyy)     amount
1      01-10-2020                120
1      01-10-2021                240
2      01-08-2020                60

I want to divide annual amount to months equally.

Output table should be like;

userid startDate(dd-mm-yyyy)     amount
1      01-10-2020                10
1      01-11-2020                10
1      01-12-2020                10
1      01-01-2021                10
1      01-02-2021                10
1      01-03-2021                10
1      01-04-2021                10
1      01-05-2021                10
1      01-06-2021                10
1      01-07-2021                10
1      01-08-2021                10
1      01-09-2021                10

1      01-10-2021                20
1      01-11-2021                20
1      01-12-2020                20
1      01-01-2021                20
1      01-02-2021                20
1      01-03-2021                20
1      01-04-2021                20
1      01-05-2021                20
1      01-06-2021                20
1      01-07-2021                20
1      01-08-2021                20
1      01-09-2021                20

2      01-08-2020                5
2      01-09-2020                5
2      01-10-2020                5
2      01-11-2020                5
2      01-12-2020                5
2      01-01-2021                5
2      01-02-2021                5
2      01-03-2021                5
2      01-04-2021                5
2      01-05-2021                5
2      01-06-2021                5
2      01-07-2021                5

Where should I start, how can I achieve this on postgresql?

Tags:

2

Answers


  1. 0 
    select userid
      ,generate_series(startdate, startDate + interval '11 month', '1 month')
      ,amount/12
from   t
    userid generate_series ?column?
    1 2020-10-01 00:00:00 10
    1 2020-11-01 00:00:00 10
    1 2020-12-01 00:00:00 10
    1 2021-01-01 00:00:00 10
    1 2021-02-01 00:00:00 10
    1 2021-03-01 00:00:00 10
    1 2021-04-01 00:00:00 10
    1 2021-05-01 00:00:00 10
    1 2021-06-01 00:00:00 10
    1 2021-07-01 00:00:00 10
    1 2021-08-01 00:00:00 10
    1 2021-09-01 00:00:00 10
    1 2021-10-01 00:00:00 20
    1 2021-11-01 00:00:00 20
    1 2021-12-01 00:00:00 20
    1 2022-01-01 00:00:00 20
    1 2022-02-01 00:00:00 20
    1 2022-03-01 00:00:00 20
    1 2022-04-01 00:00:00 20
    1 2022-05-01 00:00:00 20
    1 2022-06-01 00:00:00 20
    1 2022-07-01 00:00:00 20
    1 2022-08-01 00:00:00 20
    1 2022-09-01 00:00:00 20
    2 2020-08-01 00:00:00 5
    2 2020-09-01 00:00:00 5
    2 2020-10-01 00:00:00 5
    2 2020-11-01 00:00:00 5
    2 2020-12-01 00:00:00 5
    2 2021-01-01 00:00:00 5
    2 2021-02-01 00:00:00 5
    2 2021-03-01 00:00:00 5
    2 2021-04-01 00:00:00 5
    2 2021-05-01 00:00:00 5
    2 2021-06-01 00:00:00 5
    2 2021-07-01 00:00:00 5

    Fiddle

    Login or Signup to reply.
  2. 0

    Another approach, you may try a recursive query as the following:

    WITH RECURSIVE CTE AS
  (
    SELECT userid, startDate, amount/12 AS amount, 1 AS cn
    FROM table_name
    UNION ALL
    SELECT userid, startDate + interval '1 month', amount, cn+1
    FROM CTE WHERE cn+1 <= 12
  )
SELECT userid, startDate, amount FROM CTE
ORDER BY userid, startDate, cn

    See a demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search