FORUM Data Analytics How can I do to calculate working days between two dates?

# How can I do to calculate working days between two dates?

Hello, I have a questions and it is…

I have `created_date` and `resolved_date` as columns in `tickets` table. In other table I have sat_calendar with all days since 2020-06-01 and I identified Sunday, Saturdays and Holidays in `description` column.

But now I need to calculate difference between `created_date` and `resolve_date` in working_days.

I did new table with row_number to working_day but I couldn’t do put ‘the previuos’ number if the created or resolved date is some holiday.

Could someone help me please?

#### REPLIES

Hi @Fernanda

What an awesome question! It is one of those SQL puzzles, but I see how it’s useful in your research.

First of all, we need a way to determine if a given day is a working day (Monday - Friday) or not (Saturday - Sunday).

## Day of the week

Surprisingly, this is the easiest step of our puzzle and we’ve seen this many times in the course. I’m referring to the `DATE_PART(part, timestamp)` function. In the course, we’ve used the `DATE_PART` function mostly with `day` or `month` arguments, but there’re many of them actually:

 `part` argument meaning century Timestamp’s century. day Timestamp’s day (1-31). decade Timestamp’s decade (the year divided by 10). dow Timestamp’s day of the week (0-6). doy Timestamp’s day of the year (1-365/366). epoch The number of seconds since 1970-01-01 00:00:00-00. hour Timestap’s hour (0-23). microseconds Timestap’s microseconds (seconds multiplied by 1 000 000). millennium It’s a loooong time till the 4th one… milliseconds Timestap’milliseconds microseconds (seconds multiplied by 1 000). minute Timestap’s minute (0-59). month Timestap’s month (1-12). quarter Timestap’s quarter of the year (1-4). second Timestap’s seconds (0-59). week Timestap’s week (1-53). year Timestap’s year.

We’re interested in the `dow` argument – it’ll help us filter out weekends later. Here’s an example query:

``````SELECT
date_part('dow', '2023-06-05'::date), -- 1 | Mon
date_part('dow', '2023-06-06'::date), -- 2 | Tue
date_part('dow', '2023-06-07'::date), -- 3 | Wed
date_part('dow', '2023-06-08'::date), -- 4 | Thu
date_part('dow', '2023-06-09'::date), -- 5 | Fri
date_part('dow', '2023-06-10'::date), -- 6 | Sat
date_part('dow', '2023-06-11'::date), -- 0 | Sun
date_part('dow', '2023-06-12'::date)  -- 1 | Mon
``````

## A helper table with dates

All right, onto the next step – how to count dates? I see that you already have a table with dates, but let’s take it to another level and imagine we don’t have it.

You’ve probably seen how can we generate a table with consecutive dates in the course, so I’ll just paste the helper table query here:

``````WITH numbers AS (
SELECT generate_series(1, 1000) AS number
), dates AS (
SELECT
now()::date - number * '1 day'::interval AS d
FROM numbers
)

SELECT *
FROM dates
``````

## Sample ticket table

Now let’s improvise the `tickets` table in a way that it’ll help us test our solution:

``````WITH tickets AS (
SELECT 1 AS id, '2023-06-05' AS created_date, '2023-06-09' AS resolved_date
UNION ALL
SELECT 2, '2023-06-02', '2023-06-11'
UNION ALL
SELECT 3, '2023-06-03', '2023-06-10'
UNION ALL
SELECT 4, '2023-01-01', '2021-06-08'
)
``````

As you can see, the first 3 tickets were done within the same working week (I guess our method will remove dates where people worked extra hours during weekends ), so we should get `5` as the number of working days.

For the last one, I’ve used this calculator website and it tells me that there’re 114 working days.

## Putting it all together

Now the idea is to join the `tickets` and the `dates` table, so for every ticket we’ll attach all dates between the ticket’s `created_date and `resolved_date`:

``````WITH numbers AS (
SELECT generate_series(1, 1000) AS number
), dates AS (
SELECT
('2023-06-11'::date - number * '1 day'::interval)::date AS d
FROM numbers
), tickets AS (
SELECT 1 AS id, '2023-06-05'::date AS created_date, '2023-06-09'::date AS resolved_date
UNION ALL
SELECT 2, '2023-06-02'::date, '2023-06-11'::date
UNION ALL
SELECT 3, '2023-06-03'::date, '2023-06-10'::date
UNION ALL
SELECT 4, '2023-01-01'::date, '2023-06-08'::date
)

SELECT *
FROM tickets t
INNER JOIN dates d
ON d.d >= t.created_date
AND d.d <= t.resolved_date
ORDER BY t.id ASC
``````

Note, that I’ve modified our CTE-e a bit to ensure that all date columns are properly typed (otherwise, our database engine will complain that it can’t compare text and dates).

The only thing left is to filter out weekend dates and count:

``````WITH numbers AS (
SELECT generate_series(1, 1000) AS number
), dates AS (
SELECT
('2023-06-11'::date - number * '1 day'::interval)::date AS d
FROM numbers
), tickets AS (
SELECT 1 AS id, '2023-06-05'::date AS created_date, '2023-06-09'::date AS resolved_date
UNION ALL
SELECT 2, '2023-06-04'::date, '2023-06-11'::date
UNION ALL
SELECT 3, '2023-06-03'::date, '2023-06-10'::date
UNION ALL
SELECT 4, '2023-01-01'::date, '2023-06-08'::date
)

SELECT
t.id AS ticket_id,
COUNT(*) AS days_to_resolution
FROM tickets t
INNER JOIN dates d
ON d.d >= t.created_date
AND d.d <= t.resolved_date
AND date_part('dow', d.d) BETWEEN 1 AND 5
GROUP BY 1
ORDER BY 1 ASC
``````

5, 5, 5, 114, it’s working

It was a great puzzle, @Fernanda, until the next one