0

I have input table where the Order amount is paid in bulk ( refer Order Amount column ). I need to divide this bulk amount equally based on the number of months from Start Date to End Date.

Click here for Input Data

This is my expected result where for every order id the amount is equally split into number of months from start date to end date

Click here for expected output

Manoj Pr
  • 3
  • 2

1 Answers1

0

Breaking it down into a few steps:

  • First we need the number of months the payment is spread out over

    extract(year from end_date-start_date)*12+extract(month from end_date-start_date) as order_length

  • We now know the amount for each month is order_amount/order_length

  • Next it depends what other tables you have access to - if you don't already have a calendar table they come in handy so I recommend making one this will walk you through

  • Join your calendar table on where the month in the table is between the start and end dates in your order

  • You can then use the crosstab function to pivot on the month from your calendar table to get months across the top and order numbers along the side

Lio Elbammalf
  • 196
  • 1
  • 12
  • Hi @Lio Elbammaf thanks for taking time to answer. But can you explain it in details as I am pretty new to this – Manoj Pr Mar 16 '23 at 13:42
  • @ManojPr Which part are you stuck on and what have you written so far? – Lio Elbammalf Mar 16 '23 at 15:33
  • Elbammaf I don't know how to proceed with these Steps - Join your calendar table on where the month in the table is between the start and end dates in your order - You can then use the crosstab function to pivot on the month from your calendar table to get months across the top and order numbers along the side – Manoj Pr Mar 16 '23 at 17:17
  • I created a table with these queries, please guide me through: create table orders ( order_month date, order_id text, order_amount int, start_date date, end_date date) INSERT INTO orders values ('2022-01-01','order1', 3000, '2022-01-01', '2022-03-31'), ('2022-01-01','order2', 6000, '2022-01-01', '2022-06-30'), ('2022-02-01','order3', 4000, '2022-02-01', '2022-05-31'), ('2022-02-01','order4', 1000, '2022-02-01', '2022-03-31'); – Manoj Pr Mar 16 '23 at 18:07
  • @ManojPr You also need to create a calendar table - an example is linked in my answer. To join you want to so something like select * from orders o left join calendar c on c.date>=o.start_date and c.date<=o.end_date – Lio Elbammalf Mar 17 '23 at 09:57
  • Elbammaf thank you for guiding me. It worked like a charm. Means a lottt – Manoj Pr Mar 19 '23 at 07:14