DATE_DIFF but only counting business days

Question

I have a fairly simple query; it looks as such:

SELECT
order_date,
pickup_date,
DATE_DIFF(pickup_date,order_date, day) order_to_pickup
FROM
`orders.table`

The only is issue is, I need to be calculating the date difference in BUSINESS days, not all days.

So instead of the above query returning:

+------------+-------------+-----------------+
| order_date | pickup_date | order_to_pickup |
+------------+-------------+-----------------+
| 3/29/19    | 4/3/19      |               5 |
| 3/29/19    | 4/2/19      |               4 |
+------------+-------------+-----------------+

I want it to return:

+------------+-------------+-----------------+
| order_date | pickup_date | order_to_pickup |
+------------+-------------+-----------------+
| 3/29/19    | 4/3/19      |               2 |
| 3/29/19    | 4/2/19      |               3 |
+------------+-------------+-----------------+

Answer 1

This should be the simplified, non-bruteforce solution that @Elliott Brossard was mentioning:

select
  order_date,
  pickup_date,
  case 
    when date_diff(pickup_date, order_date, week) > 0 
      then date_diff(pickup_date, order_date, day) - (date_diff(pickup_date, order_date, week) * 2)
    else
      date_diff(pickup_date, order_date, day) 
  end
from `orders.table`