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

Question

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.

Does this answer your question? [DATE\_DIFF but only counting business days](https://stackoverflow.com/questions/55956297/date-diff-but-only-counting-business-days) — lemon, Oct 17 '22 at 19:02
I want to add business days to the date field, do not need the difference in business days — Jaskeil, Oct 17 '22 at 19:04
you can use the solution from Lamu, to calculate the number of dates you actually have to add — nbk, Oct 17 '22 at 19:20
Could someone apply that logic to this question? I am not full understanding how to apply that logic to my problem. — Jaskeil, Oct 17 '22 at 19:23

score 1 · Accepted Answer · answered Oct 17 '22 at 20:17

1

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

answered Oct 17 '22 at 20:17

Mikhail Berlyant

165,386
8
154
230

I dont need to do any date_adds? Sorry but I am not understanding how to apply this solution – Jaskeil Oct 17 '22 at 20:59
lol. you have exact that tag in your question - `dateadd` . as of how to apply? just run it against your real table (replace `your_table` with actual reference – Mikhail Berlyant Oct 19 '22 at 04:01

score 0 · Answer 2 · answered Oct 17 '22 at 22:19

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)

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

2 Answers2