Knowledge Drop
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
LinkedIn
Twitter
