sqlgoogle-bigquerydateadd

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

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

enter image description here

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.

enter image description here

Solution

  • 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

    enter image description here