Home > other >  How to do cost analysis from start_date to end_date SQL?
How to do cost analysis from start_date to end_date SQL?

Time:11-01

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

enter image description here

  • Related