BigQuery/Data Studio - How can I add one business day to a field?

Viewed 51

I have an issue in BQ/DataStudio I'm trying to work out.

I want to add 1 business day to the @DS_END_DATE field, but I've only been able to add one calendar day OR subtract one business day using this code:


  CREATE TEMPORARY FUNCTION working_days_diff(the_date DATE, num_of_days INT64) AS
  (
    DATE_SUB(CASE WHEN EXTRACT(DAYOFWEEK FROM the_date) IN (2,3,4,5,6,7)
      THEN
        CASE WHEN (EXTRACT(DAYOFWEEK FROM the_date) - MOD(num_of_days, 5)) > 1
        THEN DATE_SUB(the_date, INTERVAL MOD(num_of_days, 5) DAY)
        ELSE DATE_SUB(the_date, INTERVAL (MOD(num_of_days, 5) + 2) DAY)
        END
      ELSE
        DATE_SUB(the_date, INTERVAL (MOD(num_of_days, 5) +1) DAY)
    END, INTERVAL DIV(num_of_days, 5) WEEK) 
  );

Is there any way I can modify this to add one business day (or five?). Thank you!

0 Answers
Related