Working day function

Question

The query below in datediff is bringing the difference of days correctly, but I just want to bring the amount of working days.

I already have a business day function that excludes Saturdays, Sundays and holidays. However I am having difficulties to put this function in the consultation below in datediff. How do I do?

SELECT UC.UsuRazaoSocial [Cliente - Razão Social],
       UC.UsuNome [Cliente - Nome Fantasia],
       T.TarID [Tarefa],
       T.TarTitulo [Título],
       MAX(CONVERT(DATE,A.AtivData,103)) [Data Última Providência],
       T.TarNumAtiv [Número da Providência],
       S.StatusDesc [Status],
       DATEDIFF(DAY,MAX(A.AtivData),GETDATE()) Dias
FROM Tarefa T
LEFT JOIN Usuario UC ON UC.UsuID = T.UsuIDCliente
LEFT JOIN Atividade A ON A.TarID = T.TarID
LEFT JOIN Status S ON S.CodStatus = T.TarStatus
WHERE T.ProjID IS NOT NULL
GROUP BY UC.UsuRazaoSocial,
         UC.UsuNome,
         T.TarID,
         T.TarTitulo,
         T.TarNumAtiv,
         S.StatusDesc
ORDER BY UC.UsuRazaoSocial

Answer 1

Whereas you have a holiday chart:

IF OBJECT_ID('calcular_dias_uteis', 'FN') IS NULL
BEGIN
  EXEC('CREATE FUNCTION calcular_dias_uteis() RETURNS INT AS BEGIN RETURN 1 END');
END;
GO

ALTER FUNCTION calcular_dias_uteis(@inicio DATE,
                                   @fim    DATE)
RETURNS INT
AS
BEGIN
  DECLARE @dias TABLE(dia           DATE,
                      feriado       INT,
                      fim_de_semana INT);
  DECLARE @quantidade INT;

  WITH dias AS (
    SELECT @inicio AS dia
    UNION ALL
    SELECT DATEADD(DAY, 1, d.dia)
      FROM dias d
     WHERE d.dia < @fim
  )
  INSERT INTO @dias(dia)
  SELECT d.dia
    FROM dias d;

  -- Verifica se o dia cai no fim de semana
  UPDATE d
     SET d.fim_de_semana = CASE
                             WHEN DATEPART(WEEKDAY, d.dia) IN (1, 7) THEN 1
                             ELSE 0
                           END
    FROM @dias d;

  -- Verifica se o dia cai em algum feriado
  UPDATE d
     SET d.feriado = CASE
                       WHEN EXISTS(SELECT 1
                                     FROM feriados f
                                    WHERE f.dia = d.dia) THEN 1
                       ELSE 0
                     END
    FROM @dias d;

  SELECT @quantidade = COUNT(1)
    FROM @dias d
   WHERE d.fim_de_semana = 0
     AND d.feriado = 0;

  RETURN @quantidade;
end;
go

Applying in your query:

SELECT uc.usurazaosocial [cliente - razão social],
       uc.usunome [cliente - nome fantasia],
       t.tarid [tarefa],
       t.tartitulo [título],
       MAX(CONVERT(date, a.ativdata, 103)) [data última providência],
       t.tarnumativ [número da providência],
       s.statusdesc [status],
       calcular_dias_uteis(a.ativdata, GETDATE()) dias
  FROM tarefa t
       LEFT JOIN usuario uc ON uc.usuid = t.usuidcliente
       LEFT JOIN atividade a ON a.tarid = t.tarid
       LEFT JOIN status s ON s.codstatus = t.tarstatus
 WHERE t.projid IS NOT NULL
 GROUP BY uc.usurazaosocial,
          uc.usunome,
          t.tarid,
          t.tartitulo,
          t.tarnumativ,
          s.statusdesc
 ORDER BY uc.usurazaosocial