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

makaroni4

over 1 year Reply

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