Doubt SQL Working Days Query

Question

Good night!

Guys, I need to know a following result. I want to bring the working days grouped by month and year from April to June of this year 2017. Example:

Ano    Mês    Dias Uteis
2017  Abril      19
2017  Maio       18
2017  Junho      17

NOTE: There is already a function in our bank called dias_uteis that performs the calculation normally of the working days and holidays. I tried to do with the query below, however, unsuccessfully.

 select (dbo.dias_uteis('01-04-2017','30-06-2017')) diasuteis, 
 DATEPART(month, dbo.dias_uteis('01-04-2017','30-06-2017') ) Mês,
 DATEPART(YEAR, dbo.dias_uteis('01-04-2017','30-06-2017')) Ano 
 group by DATEPART(YEAR, dbo.dias_uteis('01-04-2017','30-06-2017')), 
 DATEPART(MONTH, dbo.dias_uteis('01-04-2017','30-06-2017'))

Answer 1

I didn’t quite understand that function you said you had, I tried to do it in the simplest way possible.

SELECT YEAR(dias_uteis) AS 'Ano',
MONTH(dias_uteis) AS 'Mes',
dias_uteis AS 'Dias Uteis'
FROM dbo
WHERE dias_uteis BETWEEN '01-04-2017' AND '30-06-2017'
GROUP BY MONTH(dias_uteis);

Answer 2

Select dbo.dias_uteis('01-04-2017','30-04-2017') abril,
       dbo.dias_uteis('01-05-2017','31-05-2017') maio,
       dbo.dias_uteis('01-06-2017','30-06-2017') junho

I believe this is it

Answer 3

On the issue of working days I suggest reading the article "Business day operations on SQL Server"that details ways to get the calculation, including with the optional use of calendar table.

Here is the code that receives as values the period to be issued. From these values the months are generated and calculated the number of working days for each monthly period.

-- 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;

--
with cteData as (
SELECT @DataInicial as InícioMês,
      case when eomonth(@DataInicial) > @DataFinal
           then @DataFinal 
           else eomonth(@DataInicial) end as FinalMês
union all
SELECT dateadd(day, +1, FinalMês), 
       case when eomonth(dateadd(day, +1, FinalMês)) > @DataFinal
            then @DataFinal 
            else eomonth(dateadd(day, +1, FinalMês)) end
  from cteData
  where FinalMês < @DataFinal
)
SELECT year(InícioMês) as Ano, month(InícioMês) as Mês,
       dbo.dias_uteis (InícioMês, FinalMês) as [Dias úteis]
  from cteData;

_FIM:
go

The code uses the function eomonth(), available as of 2012 version of SQL Server.

Version 3 of the code also works for cases where the review period does not involve full months. For example, you can search from 5/3/2017 to 20/6/2017 or even by 10/2/2017 to 20/2/2017.