SQL Query average time DO NOT take into account Weekends and holidays

Question

I need to average the time it takes since creation (a. Date of creation) until completion (s. Data_status_finalized) field.

Not able to count weekends and holidays.

The following query is working, however, takes into account every day of the period.

SELECT AVG(DATEDIFF(HH, a.Data_Criacao, s.Data_Status_Finalizado)) 
                                        FROM RTRC_Processo p
                                            Left join RTRC_Solucao s on (s.Id_Processo_RTRC = p.Id )
                                            Left Join RTRC_Abertura a on (a.Id_Processo_RTRC = p.Id) 
                                       WHERE 1=1 
                                        and s.Status ='F' 
                                        AND p.Data_Exclusao is null

                                        and YEAR(s.Data_Status_Finalizado) = 2019
                                        and MONTH(s.Data_Status_Finalizado)= 08

Answer 1

Create the function fn_DiasUteis

CREATE FUNCTION [dbo].fn_DiasUteis
(
    @fromdate Datetime,
    @todate Datetime
)
RETURNS TABLE AS RETURN
(

SELECT
    (DATEDIFF(dd, @fromdate, @todate) + 1)
   -(DATEDIFF(wk, @fromdate, @todate) * 2)
   -(CASE WHEN DATEPART(dw, @fromdate) = 1 THEN 1 ELSE 0 END)
   -(CASE WHEN DATEPART(dw, @fromdate) = 7 THEN 1 ELSE 0 END)
   -(CASE WHEN DATEPART(dw, @todate) = 1 THEN 1 ELSE 0 END)
   -(CASE WHEN DATEPART(dw, @todate) = 7 THEN 1 ELSE 0 END)
  As NoOfDiasUteis

)

Create the table feriados and populate it

CREATE TABLE feriados (
   dia datetime 
)



insert into feriados values ( '09/07/2019' )
--insert into feriados values ( '10/12/2019' )
--insert into feriados values ( '11/02/2019' )
insert into feriados values ( '11/15/2019' )
insert into feriados values ( '12/25/2019' )

And you can consult working days between two such dates:

declare @fromdate date;
declare @todate date;
declare @diasuteis int;

set @fromdate  = '09/01/2019'
set @todate = '12/31/2019'


set @diasuteis = ( select * from dbo.fn_DiasUteis( @fromdate, @todate )) 
- (select COUNT(*) from feriados where dia >= @fromdate and dia <= @todate)

print @diasuteis

75

We have 75 working days by the end of the year.

Observing

To the popular the table of holidays does not include the days that fall Saturday or Sunday, because they will be considered doubly.

Answer 2

Here is an SQL expression, in the Postgresql DBMS, that provides the working days of a period. I considered that there is a table with the registration of holidays and other days that should not be considered (amendments, optional points, etc):

SELECT dia FROM
    (SELECT ('2019-09-01'::date+s.a*'1 day'::interval) AS dia
       FROM generate_series(0, '2019-09-31'::date -
                          '2019-09-01'::date, 1) AS s(a)) foo
  WHERE EXTRACT(DOW FROM dia) BETWEEN 1 AND 5
EXCEPT
SELECT dia FROM tab_feriado;