I have a table with orders like this:

date, domain, total
2022-01-07, test.com, 120

The business requirement is that these are yearly subscriptions, and I need to look at how much they will theoretically bring in every month. So the output should be the following:

date, domain, total
2022-01-07, test.com, 10
2022-02-07, test.com, 10
2022-03-07, test.com, 10
2022-04-07, test.com, 10
2022-05-07, test.com, 10
2022-06-07, test.com, 10
2022-07-07, test.com, 10
2022-08-07, test.com, 10
2022-09-07, test.com, 10
2022-10-07, test.com, 10
2022-11-07, test.com, 10
2022-12-07, test.com, 10

Essentially, each order should be split into 12 lines, with the starting date being the date of the order and the last date being date 12 month. Also, the amount has to be divided by 12 for each month.

I am using postgreSQL.

CodePudding user response：

You can do:

select t.date   (m.n * interval '1 month'), domain, total / 12
from t
cross join generate_series(0, 11) m (n)

Result:

 ?column?             domain    ?column? 
 -------------------- --------- -------- 
 2022-01-07 00:00:00  test.com  10       
 2022-02-07 00:00:00  test.com  10       
 2022-03-07 00:00:00  test.com  10       
 2022-04-07 00:00:00  test.com  10       
 2022-05-07 00:00:00  test.com  10       
 2022-06-07 00:00:00  test.com  10       
 2022-07-07 00:00:00  test.com  10       
 2022-08-07 00:00:00  test.com  10       
 2022-09-07 00:00:00  test.com  10       
 2022-10-07 00:00:00  test.com  10       
 2022-11-07 00:00:00  test.com  10       
 2022-12-07 00:00:00  test.com  10       

See example at db<>fiddle.

CodePudding user response：

demo:db<>fiddle

You can use generate_series() for that:

SELECT 
    gs::date as my_new_date,
    domain,
    total / 12 as my_new_total
FROM mytable,
    generate_series(mydate, mydate   interval '11 months', interval '1 month') gs

Parameters for generate_series():

1. The date of your record (start)
2. The date of your record 12 months (end)
3. Interval length (1 month)

Note:

1. date is not a good name of a column. First, it is a reserved keyword of Postgres; Second because it does not exactly describe what date is meant.
2. This approach does not work correctly when it comes to the end of the month. Think about January 31st. What is the next month? February 31st does not even exist. Here you'll need to specify your requirements better.