Knowledge Drop

Create a SQL Function to count only weekdays between two dates

  • 15 June 2021
  • 1 reply
  • 207 views

Last tested: Apr 26, 2020

 

The problem:

Let says that we want to count the number of weekdays between two dates (including start and end date without counting Saturdays and Sundays) and we would like to create a SQL function so that other developers would use it.

The solution:

If our database dialect uses custom Data Definition Language (DDL) commands, we can create a function that will be used for this connection.

  • Step 1:
    Create a Persistent Derived Table that will use the create_process parameter.

view: pdt_functions {

derived_table: {

sql_trigger_value: SELECT 1 ;;

create_process: {

sql_step:

CREATE OR REPLACE FUNCTION count_workdays(start_date DATE, end_date DATE)

RETURNS integer AS $$

BEGIN

RETURN (SELECT count(*) AS count_days_no_weekend

FROM generate_series(start_date

, end_date

, interval '1 day') the_day

WHERE extract('ISODOW' FROM the_day) <6);

END;

$$ LANGUAGE PLPGSQL

;;

}

}

}

 

This function must be adapted based on the database dialect, this example is based on Postgres.

We are using sql_trigger_value: SELECT 1 ;; because we want to sent this query to the database only one time; in other words since the result of SELECT 1 will never change then this PDT will only be triggered once (unless we make some change in our function or add another function and would; we can then change the trigger value).

We create this function in a derived table because we want to take advantage of the version control and it allows us to see what function we've created; this can be helpful for documentation so that other developers can see the function's definition or add other functions.

  • Step 2:
    Once the pdt_functions has been triggered without any error meaning our function has been created in the database server, we can use it as we use other functions of our database.

dimension: number_of_days {

sql: count_workdays(${rental_date_date},${returned_date_date}) ;;

}

 

If we don't want to create a function, here's another alternative to count weekdays between two dates: https://help.looker.com/hc/en-us/articles/360023861113-How-to-Count-Only-Weekdays-Between-Two-Dates

 

This content is subject to limited support.                

 

 

 


1 reply

Hi,
Once I create function using this method, can I use it to create other derived tables/views or it can only be used in dimension?

Reply