Difference between two dates in business days? Google Bigquery

Question

How do I calculate the difference between two dates in business days in Google Bigquery?

I want to replicate this example below:

I have tried these examples but they do not give the expected results:

DATE_DIFF but only counting business days

I also used this logic,ionand it did not work:

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)
);

Mohammad · Answer 1 · 2022-07-12T15:54:23.513

getting desired result as follows:


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

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

WITH OrdersTable AS (
  SELECT  DATE '2022-06-28' AS DELIVERY_DATE,
          DATE '2022-08-17' AS ORIGINAL_ETA_DATE
    UNION ALL
  SELECT DATE '2022-07-01' AS DELIVERY_DATE,
         DATE '2022-07-14' AS ORIGINAL_ETA_DATE
    UNION ALL  
  SELECT  DATE '2022-06-30' AS DELIVERY_DATE,
          DATE '2022-07-08' AS ORIGINAL_ETA_DATE
      UNION ALL
  SELECT  DATE '2022-06-30' AS DELIVERY_DATE,
          DATE '2022-07-08' AS ORIGINAL_ETA_DATE
      UNION ALL
  SELECT  DATE '2022-06-29' AS DELIVERY_DATE,
          DATE '2022-07-06' AS ORIGINAL_ETA_DATE
      UNION ALL
  SELECT  DATE '2022-06-27' AS DELIVERY_DATE,
          DATE '2022-07-01' AS ORIGINAL_ETA_DATE
      UNION ALL
  SELECT  DATE '2022-06-30' AS DELIVERY_DATE,
          DATE '2022-07-05' AS ORIGINAL_ETA_DATE
     UNION ALL
  SELECT  DATE '2022-06-30' AS DELIVERY_DATE,
          DATE '2022-06-28' AS ORIGINAL_ETA_DATE
                       )

SELECT
  DELIVERY_DATE,
  ORIGINAL_ETA_DATE,
  case when DELIVERY_DATE < ORIGINAL_ETA_DATE then 
            BusinessDateDiff(DELIVERY_DATE, ORIGINAL_ETA_DATE)
      when DELIVERY_DATE > ORIGINAL_ETA_DATE then 
            BusinessDateDiff1(ORIGINAL_ETA_DATE, DELIVERY_DATE) 
      else 0 end AS BUSINESS_DAYS
FROM OrdersTable

[![Desired Result][1]][1]


  [1]: https://i.stack.imgur.com/efmw3.png

score 1 · Accepted Answer · answered Jul 12 '22 at 18:16

Consider below

create temp function BusinessDateDiff(delivery DATE, eta DATE) AS ((
  select if(delivery > eta, 1, -1) * count(*) 
  from unnest(generate_date_array(
    least(delivery, eta), greatest(delivery, eta) - 1
  )) day
  where not extract(dayofweek from day) in (1, 7)
));
select *, 
  BusinessDateDiff(DELIVERY_DATE, ORIGINAL_ETA_DATE) as BUSINESS_DAYS
from your_table

if applied to sample data as in your question - output is

Difference between two dates in business days? Google Bigquery

2 Answers2