Doubt SQL Query d

  

I'm wanting to perform the calculation in the sql query where the result is the number of open calls divided by the number of weekdays chosen period.

Here's a different approach to the code provided. I hope you get what you need. The code assumes that the issuing period will have full months. That is, the period will begin at the beginning of the month and end at the end of the month.

-- código #1 v3
--> informe a data inicial e final (formato dd/mm/aaaa)
declare @DataInicial date, @DataFinal date;
set @DataInicial= convert(date, '1/4/2017', 103);
set @DataFinal= convert(date, '30/6/2017', 103);

-- garante sequência nos parâmetros
IF @DataInicial > @DataFinal
  goto _FIM;

-- configuração para a função datename retornar em português
set language Brazilian;

--
with cteSolicAcum as (
SELECT year(S.SolData) as Ano, month(S.SolData) as Mês,
       count(S.SolData) as qtdAbertura,
       count(S.SolDataFechamento) as qtdFechamento
  from Solicitacao as S 
       inner join usuario as U on U.UsuID = S.UsuIDResponsavel 
  where S.UsuIDResponsavel = 29
        and S.ProID = 2 
        and SolData between @DataInicial and @DataFinal
  group by year(S.SolData), month(S.SolData)
)
SELECT Ano, Mês,
       datename(month, datefromparts(Ano, Mês, 1)) as Mês_Extenso,
       qtdAbertura as Abertura,
       cast((cast(qtdAbertura as float) / 
             dbo.dias_uteis(datefromparts(Ano, Mês, 1),
                            eomonth(datefromparts(Ano, Mês, 1))
                           )) as decimal (7,2)) as [Média de Abertura Diária],
       qtdFechamento as Fechamento
  from cteSolicAcum;

_FIM:
go

I'll help you with the query to return the number of working days of the month.

declare @startDate datetime = '01/04/2017'
declare @endDate datetime = '30/04/2017'
DECLARE @totaldays INT; 
DECLARE @weekenddays INT;
declare @feriados int;

SELECT @feriados = count(*)
FROM Feriado
where FerData between @startDate and @endDate

SET @totaldays = DATEDIFF(DAY, @startDate, @endDate) 
SET @weekenddays = ((DATEDIFF(WEEK, @startDate, @endDate) * 2) + -- get the number of weekend days in between
                       CASE WHEN DATEPART(WEEKDAY, @startDate) = 1 THEN 1 ELSE 0 END + -- if selection was Sunday, won't add to weekends
                       CASE WHEN DATEPART(WEEKDAY, @endDate) = 6 THEN 1 ELSE 0 END)  -- if selection was Saturday, won't add to weekends

select (@totaldays - @weekenddays - coalesce(@feriados,0))

It would still be interesting for you to create a record with the holidays, and also to shorten them on weekdays.

I imagine you want the average number of calls opened per year / month, so it would look like this:

SELECT
    DATEPART(YEAR, S.SolData) Ano,
    DATEPART(month, S.SolData) Mes, 
    Count(S.SolID) / 
    dbo.dias_uteis(
        (S.SolData - DAY(S.SolData) + 1),
        DATEADD(DD, -DAY(DATEADD(M, 1, S.SolData)), DATEADD(M, 1,S.SolData))
    ) as media
FROM Solicitacao S 
INNER JOIN usuario U on U.UsuID = S.UsuIDResponsavel 
    AND S.UsuIDResponsavel = 29
WHERE S.ProID = 2 
    AND CONVERT(DATE,S.SolData) BETWEEN '01/04/2017' AND '26/06/2017' 
GROUP BY DATEPART(YEAR, S.SolData), DATEPART(month, S.SolData)

I certainly had no way to test the code and I did not have access to the table structure either. Hope it helps.

Edit:

Just for testing, it's not the right way, but do a test:

SELECT
    DATEPART(YEAR, S.SolData) Ano,
    DATEPART(month, S.SolData) Mes, 
    (Count(S.SolID) / 
    Count(distinct cast(S.SolData As Date))) as media
FROM Solicitacao S 
INNER JOIN usuario U on U.UsuID = S.UsuIDResponsavel 
    AND S.UsuIDResponsavel = 29
WHERE S.ProID = 2 
    AND CONVERT(DATE,S.SolData) BETWEEN '01/04/2017' AND '26/06/2017' 
GROUP BY DATEPART(YEAR, S.SolData), DATEPART(month, S.SolData)