I am doing expense analysis with Bigquery. I have a table with start_date
, end_date
, expense
and I have to bring the expense to all days in the range between start_date and end_date.
My table looks like:
Start Date | End Date | Expense code | Amount | Ref_code |
---|---|---|---|---|
01/11/2021 | 13/11/2021 | 3000 | 70,000.00 | 3001 |
01/11/2021 | 30/11/2021 | 2000 | 3,000.00 | 2005 |
My expectation should be:
Start Date | End Date | Expense code | Amount | Ref_code | Daily_date | Daily_expense |
---|---|---|---|---|---|---|
01/11/2021 | 13/11/2021 | 3000 | 70,000.00 | 3001 | 01/11/2021 | 5384.615385 |
01/11/2021 | 13/11/2021 | 3000 | 70,000.00 | 3001 | 02/11/2021 | 5384.615385 |
01/11/2021 | 13/11/2021 | 3000 | 70,000.00 | 3001 | 03/11/2021 | 5384.615385 |
... | ... | ... | ... | ... | ... | ... |
01/11/2021 | 13/11/2021 | 3000 | 70,000.00 | 3001 | 13/11/2021 | 5384.615385 |
01/11/2021 | 30/11/2021 | 2000 | 3000 | 2005 | 01/11/2021 | 100 |
01/11/2021 | 30/11/2021 | 2000 | 3000 | 2005 | 02/11/2021 | 100 |
... | ... | ... | ... | ... | 30/11/2021 | 100 |
CodePudding user response:
Consider below approach
select *,
round(Amount / (1 date_diff(End_Date,Start_Date, day)), 2) as Daily_expense
from data,
unnest(generate_date_array(Start_Date, End_Date)) Daily_date
if applied to sample data in your question - output is