Doubt SQL query d

Question

I am wanting to perform the calculation in sql query where the result is the number of open calls divided by the number of working days chosen period. Ex: They were opened 200 calls in the month and there are 17 working days in the month of May, then you will have to realize that average.

NOTE: There is already a working day function listed below:

USE [0800net_PRD]
GO
/****** Object:  UserDefinedFunction [dbo].[dias_uteis]    Script Date: 30/06/2017 11:06:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION  [dbo].[dias_uteis]  (@DATA_INICIAL DATETIME, @DATA_FINAL DATETIME)
RETURNS INT
AS
BEGIN
    DECLARE @CONT INT 

    SET @DATA_INICIAL = (SELECT CAST(@DATA_INICIAL AS DATE) )
    SET @DATA_FINAL = (SELECT CAST(@DATA_FINAL AS DATE ))
    SET @CONT = 0

    WHILE (@DATA_INICIAL <= @DATA_FINAL)
     BEGIN
        IF (DATEPART(DW, @DATA_INICIAL ) IN(1,7) OR @DATA_INICIAL IN (SELECT CAST(FerData AS DATE) FROM Feriado))
         BEGIN
          SET @DATA_INICIAL = @DATA_INICIAL+1
         END

        IF DATEPART(DW, @DATA_INICIAL ) NOT IN(1,7) 
         BEGIN
          SET @CONT = @CONT+1
          SET @DATA_INICIAL = @DATA_INICIAL+1
         END 
     END
RETURN @CONT
END

Below is the query I’m trying to accomplish this.

select
s1.Ano ,s1.Mês , CASE  s1.Mês
         WHEN 1 THEN 'Janeiro'
         WHEN 2 THEN 'Fevereiro'
         WHEN 3 THEN 'Março'
         WHEN 4 THEN 'Abril'
         WHEN 5 THEN 'Maio'
         WHEN 6 THEN 'Junho'
         WHEN 7 THEN 'Julho'
         WHEN 8 THEN 'Agosto'
         WHEN 9 THEN 'Setembro'
         WHEN 10 THEN 'Outubro'
         WHEN 11 THEN 'Novembro'
         WHEN 12 THEN 'Dezembro'
       END AS Mês_Extenso, s1.[Média de Abertura Diária], s2.fechamento
from
(select COUNT(SolData)/(SUM(dbo.dias_uteis('01/04/2017', '26/06/2017') + SUM(dbo.dias_uteis('01/04/2017', '26/06/2017'))))[Média de Abertura Diária],
DATEPART(month, SolData) Mês, DATEPART(YEAR, SolData) Ano 
from Solicitacao S inner join usuario U on U.UsuID = S.UsuIDResponsavel and S.UsuIDResponsavel = 29
where S.ProID = 2 and CONVERT(DATE,SolData) BETWEEN '01/04/2017' and '26/06/2017'  group by DATEPART(YEAR, SolData), DATEPART(month,SolData)) s1
inner join
(select COUNT(SolDataFechamento) Fechamento, DATEPART (month, SolDataFechamento) Mês, DATEPART(YEAR, SolDataFechamento) Ano
from Solicitacao S inner join usuario U on U.UsuID = S.UsuIDResponsavel and S.UsuIDResponsavel = 29
where S.ProID = 2 and CONVERT(DATE, SolDataFechamento) BETWEEN '01/04/2017' and '26/06/2017'  group by DATEPART(YEAR, SolDataFechamento), DATEPART(MONTH, SolDataFechamento)) s2
on s1.Mês=s2.Mês and s1.Ano = s2.Ano
order by 
s1.Ano, s1.Mês

OBS: When executing also gives an error message "It is not possible to execute an aggregation function in an expression that contains an aggregation or a sub-query."

Answer 1

I am wanting to perform the calculation in sql query where the result is the number of open calls divided by the number of working days chosen period.

This is a different approach to the code provided. I hope it meets your needs. The code assumes that the issue period will have full months. That is, the period will start at the beginning of month and end of 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

Answer 2

I will 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 to create a register with the holidays, to also decrease them of the working days.

Answer 3

I imagine you want the average number of open calls per year/month, so that would be:

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 also had no access to the structure of the tables. I hope it helps.

Edit:

Just for testing, it’s not the right way, but take 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)