How to only add business days to a date in BigQuery?-CodePudding

For a given date I want to add business days to it. For example, if today is 10-17-2022 and I have a field that is 8 business days. How can I add 8 business days to 10-17-2022 which would be 10-27-2022.

Current Data:

BUSINESS_DAYS	Date
8	10-11-2022
10	10-13-2022
9	10-12-2022

Desired Output Data

BUSINESS_DAYS	Date	FINAL_DATE
8	10-11-2022	10-21-2022
10	10-13-2022	10-27-2022
9	10-12-2022	10-25-2022

As you can see we are skipping all weekends. We can ignore holidays for now.

Update: Using The suggest logic I got the following answer. I changed the names up. I used:

   DATE_ADD(A.PO_SENT_DATE  , INTERVAL
  (CAST(PREDICTED_LEAD_TIME AS INT64) 
     (date_diff(A.PO_SENT_DATE  , DATE_ADD(A.PO_SENT_DATE  , INTERVAL CAST(PREDICTED_LEAD_TIME AS INT64) DAY), week)* 2)) 
  DAY) as FINAL_DATE

Update2: Using the following:

  DATE_ADD(`Date`, INTERVAL
  (BUSINESS_DAYS 
     (date_diff( DATE_ADD(`Date`, INTERVAL BUSINESS_DAYS DAY),`Date`, week) * 2)) 
  DAY) as FINAL_DATE

There are instances where the result falls on the weekend. See screenshot below. 10-22-2022 falls on a Saturday.

CodePudding user response：

Consider below simple solution

select *,
  ( select day
    from unnest(generate_date_array(date, date   (div(business_days, 5)   1) * 7)) day
    where not extract(dayofweek from day) in (1, 7)
    qualify row_number() over(order by day) = business_days   1
  ) final_date 
from your_table

if applied to sample data in your question

with your_table as (
  select 8 business_days, date '2022-10-11' date union all
  select 10, '2022-10-13' union all
  select 9, '2022-10-12' 
)

output is

CodePudding user response：

The solution from @mikhailberlyant is really really cool, and very innovative. However if you have a lot of rows in your table and value of "business_days" column varies a lot, query will be less efficient especially for larger "business_days" values as implementation needs to generate entire range of array for each row, unnest it, and then do manipulation in that array.

This might help you do calculation without any array business:

select day, add_days as add_business_days, 
DATE_ADD(day, INTERVAL cast(add_days  2*ceil((add_days -(5-(
  (case when EXTRACT(DAYOFWEEK FROM day) = 7 then 1 else EXTRACT(DAYOFWEEK FROM day) end)
  -1)))/5) (case when EXTRACT(DAYOFWEEK FROM day) = 7 then 1 else 0 end) as int64) DAY) as final_day
  from 
  (select parse_date('%Y-%m-%d', "2022-10-11") as day, 8 as add_days)