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 |
+------------+-------------+-----------------+

What defines a business day? Is every weekday a business day, or do you have a calendar of holidays that also don't count as business days? — Elliott Brossard, May 02 '19 at 16:09
@ElliottBrossard We can just say for the sake of this that business days are strictly Monday through Friday — , May 02 '19 at 16:13
this might help you :https://stackoverflow.com/questions/252519/count-work-days-between-two-dates — Ashok, May 02 '19 at 16:17

score 6 · Accepted Answer · answered May 02 '19 at 18:36

6

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`

answered May 02 '19 at 18:36

khan

7,005
15
48
70

This will misstate the value in a couple of cases where the start or end date fall on one of the weekend days. I updated to account for this I added an answer that accounts for this. – Brendan Buhmann Aug 17 '22 at 21:19

score 3 · Answer 2 · answered May 02 '19 at 16:33

I think there is a clever solution to be had if you consider the number of weeks between the two dates, but here is a brute-force approach in the meantime:

CREATE TEMP FUNCTION BusinessDateDiff(start_date DATE, end_date DATE) AS (
  (SELECT COUNTIF(MOD(EXTRACT(DAYOFWEEK FROM date), 7) > 1)
   FROM UNNEST(GENERATE_DATE_ARRAY(
       start_date, DATE_SUB(end_date, INTERVAL 1 DAY))) AS date)
);

For your input, I get:

CREATE TEMP FUNCTION BusinessDateDiff(start_date DATE, end_date DATE) AS (
  (SELECT COUNTIF(MOD(EXTRACT(DAYOFWEEK FROM date), 7) > 1)
   FROM UNNEST(GENERATE_DATE_ARRAY(
       start_date, DATE_SUB(end_date, INTERVAL 1 DAY))) AS date)
);

WITH OrdersTable AS (
  SELECT
    DATE '2019-03-29' AS order_date,
    DATE '2019-04-03' AS pickup_date UNION ALL
  SELECT
    '2019-03-29',
    '2019-04-02'
)
SELECT
  order_date,
  pickup_date,
  BusinessDateDiff(order_date, pickup_date) AS order_to_pickup
FROM OrdersTable
ORDER BY pickup_date
+------------+-------------+-----------------+
| order_date | pickup_date | order_to_pickup |
+------------+-------------+-----------------+
| 2019-03-29 |  2019-04-02 |               2 |
| 2019-03-29 |  2019-04-03 |               3 |
+------------+-------------+-----------------+

Yes! I think this is giving me what I need! Thank you! – May 02 '19 at 16:57 — , May 02 '19 at 16:57

Eric Keen · Answer 3 · 2019-05-02T16:58:01.307

Here is a working methodology for calculating working days between dates based on work in the Looker Discourse community here. The original example is for Redshift so I have adapted it for BigQuery below.

SELECT
  CAST(-1*(DATE_DIFF(DATE '2019-01-01', DATE '2019-01-31', DAY) - ((FLOOR(DATE_DIFF(DATE '2019-01-01', DATE '2019-01-31', DAY) / 7) * 2) +
        CASE
          WHEN EXTRACT(DAYOFWEEK  FROM  DATE '2019-01-01') - EXTRACT(DAYOFWEEK  FROM  DATE '2019-01-31') IN (1,  2,  3,  4,  5) AND EXTRACT(DAYOFWEEK  FROM  DATE '2019-01-31') != 0 THEN 2
          ELSE 0
        END +
        CASE
          WHEN EXTRACT(DAYOFWEEK  FROM  DATE '2019-01-01') != 0 AND EXTRACT(DAYOFWEEK  FROM  DATE '2019-01-31') = 0 THEN 1
          ELSE 0
        END +
        CASE
          WHEN EXTRACT(DAYOFWEEK  FROM  DATE '2019-01-01') = 0 AND EXTRACT(DAYOFWEEK  FROM  DATE '2019-01-31') != 0 THEN 1
          ELSE 0 END)) AS int64) AS weekdays

Applying this to your dataset we get:

SELECT
  order_date,
  pickup_date,
  CAST(-1*(DATE_DIFF(order_date, pickup_date, DAY) - ((FLOOR(DATE_DIFF(order_date, pickup_date, DAY) / 7) * 2) +
        CASE
          WHEN EXTRACT(DAYOFWEEK  FROM  order_date) - EXTRACT(DAYOFWEEK  FROM  pickup_date) IN (1,  2,  3,  4,  5) AND EXTRACT(DAYOFWEEK  FROM  pickup_date) != 0 THEN 2
          ELSE 0
        END +
        CASE
          WHEN EXTRACT(DAYOFWEEK  FROM  order_date') != 0 AND EXTRACT(DAYOFWEEK  FROM  pickup_date) = 0 THEN 1
            ELSE 0
          END +
          CASE
            WHEN EXTRACT(DAYOFWEEK  FROM  order_date) = 0 AND EXTRACT(DAYOFWEEK  FROM  pickup_date) != 0 THEN 1
            ELSE 0 END)) AS int64) AS weekdays
  FROM
    `orders.table`

score 1 · Answer 4 · answered May 31 '21 at 14:29

An alternative solution. Remove the numbers of weekend days:

SELECT
    order_date,
    pickup_date,
    date_diff(pickup_date, order_date, day)
        - date_diff(pickup_date, order_date, WEEK(SUNDAY)) 
        - date_diff(pickup_date, order_date, WEEK(SATURDAY)) as order_to_pickup
FROM `orders.table`

score 0 · Answer 5 · answered Aug 17 '22 at 21:22

Extending on the answer from @khan for where dates land on certain combinations of Saturday/Sunday:

CASE 
  WHEN Completed_Date is null THEN null 
  when date_diff(completed_date,create_date, week) > 0 
    then date_diff(completed_date,create_date, day) - (date_diff(completed_date,create_date,week) * 2)
  else
    date_diff(completed_date,create_date, day) 
  end +
  CASE WHEN EXTRACT(DAYOFWEEK FROM Create_Date) = 1 and EXTRACT(DAYOFWEEK FROM Completed_Date) = 7  THEN -1
  WHEN EXTRACT(DAYOFWEEK FROM Create_Date) != 1 and EXTRACT(DAYOFWEEK FROM Completed_Date) = 1 THEN 1
  WHEN EXTRACT(DAYOFWEEK FROM Create_Date) = 7 and EXTRACT(DAYOFWEEK FROM Completed_Date) != 7 THEN 1
  ELSE 0 END

score -1 · Answer 6 · answered Apr 04 '23 at 09:01

Here is my solution for counting working days between two dates:

DECLARE in_date_from DATE DEFAULT "2023-03-06";
DECLARE in_date_to DATE DEFAULT "2023-03-19";

SELECT
  DATE_DIFF(in_date_to, in_date_from, DAY) + 1 -
    DATE_DIFF(in_date_to, in_date_from, WEEK(SATURDAY)) -
      DATE_DIFF(in_date_to, in_date_from, WEEK(SUNDAY)) -
        IF(EXTRACT(DAYOFWEEK FROM in_date_from) IN (1,7), 1, 0) AS `working_days`;

DATE_DIFF but only counting business days

6 Answers6

Linked

Related