Split rows in a table based on date fields SQL-CodePudding

I have a table on BigQuery of invoices containing some payments covering multiple months of the year. The aim is to split theses records in order calculate my monthly revenue.

Here's an example :

For an invoice (1 record) of 5000 USD covering a period starting from the 1st of January 2022 until the 1st of October 2022 (10 months) I want to get 10 rows of 500 USD.

Table : enter image description here

Expected output : enter image description here

CodePudding user response：

Consider below approach

select id_invoice, amount / count(*) over(partition by id_invoice) amount, min(day) start, max(day) `end`
from your_table t, unnest(generate_date_array(start, `end`)) day
group by id_invoice, t.amount, date_trunc(day, month)

if applied to sample data in your question - output is

CodePudding user response：

You might consider below as well.

WITH sample_table AS (
  SELECT 123 id_invoice, 5000 amount, DATE '2022-01-01' start, DATE '2022-10-31' `end`
)
SELECT id_invoice,
       amount / (DATE_DIFF(`end`, start, MONTH)   1) AS amount,
       start_of_month AS start,
       LAST_DAY(start_of_month) AS `end`
  FROM sample_table, UNNEST(GENERATE_DATE_ARRAY(start, `end`, INTERVAL 1 MONTH)) start_of_month;