I have my table like this:

WITH my_table (start_date, end_date) AS (
    values 
    ('2021-10-01','2021-10-05'),
    ('2021-10-01','2021-10-03'),
    ('2021-10-02','2021-10-10'),
    ('2021-10-02','2021-10-08'),
    ('2021-10-02','2021-10-05')
) 
SELECT *
FROM my_table

I want to count working days (from Monday to Friday). Desired table should look like this:

Thanks for helping me!

CodePudding user response：

I want to count days from Monday to Friday

You can generate sequence between the dates and use day_of_week to filter out the not needed days (see array functions):

-- sample data
WITH dataset(start_date, end_date) AS (
 values     (date '2021-10-01', date '2021-10-05'),
    (date '2021-10-01', date '2021-10-03'),
    (date '2021-10-02', date '2021-10-10'),
    (date '2021-10-02', date '2021-10-08'),
    (date '2021-10-02', date '2021-10-05')
)

-- query
select start_date,
       end_date,
       cardinality(filter(
               sequence(start_date, end_date, interval '1' day),
                d -> day_of_week(d) not in (6,7)
           )) business_days
from dataset;

Output:

I want to count working days

But this is much harder and not sure that it can be done without separate "dictionary" table which will cover holidays and such