How to calculate ticket aging, excluding weekends-CodePudding

I am using date created and CURRENT date to calculate the ticket aging. I am struggling to exclude the weekends. When using the DOW function, it only exclude weekends on date created field, which does not provide accurate results.

Example of:

select CURRENT_DATE - date_created::date AS "Case Aging"
where EXTRACT(DOW FROM date_created::date) <> '0' 
AND EXTRACT(DOW FROM date_created::date) <> '6'
from Tickets;

CodePudding user response：

Build a list of days starting with date_created till today and count its elements excluding Saturdays and Sundays.

with the_table(date_created) as 
(
 values
 ('2022-03-01'::date), ('2022-04-11'), ('2022-10-01'), ('2022-12-06'::date)
) -- sample data

select date_created, (
   select count(*) - 1 
   from generate_series(date_created, current_date, interval '1 day') g
   where extract(ISODOW from g) < 6
 ) as ticket_age
from the_table;