CODEWITHSUNDEEP
google-bigquery
wirjo
wirjo

Reputation: 1

How would I get the difference between two dates in days and excluding weekends?

How would I get the difference between two dates in days and excluding weekends?

For context, this is for the purposes of Service Level Agreement (SLA) monitoring between the date that the ticket is created and closed.

Upvotes: 0

Views: 2835

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below example is for BigQuery Standard SQL 

#standardSQL
CREATE TEMP FUNCTION CUSTOM_DATE_DIFF(startDate DATE, endDate DATE) AS ((
  SELECT
    1 + DATE_DIFF(endDate, startDate, DAY) -
    2 * DATE_DIFF(endDate, startDate, WEEK) -
    IF(EXTRACT(DAYOFWEEK FROM startDate) = 1, 1, 0) -
    IF(EXTRACT(DAYOFWEEK FROM endDate) = 7, 1, 0)
));
WITH test AS (
  SELECT DATE '2018-05-01' startDate, DATE '2018-05-11' endDate
)
SELECT 
  startDate, 
  endDate, 
  CUSTOM_DATE_DIFF(startDate, endDate) diff
FROM test

Upvotes: 2

Related Questions