K
In my way of seeing, an efficient way to determine the days, would be, first, to calculate the days skilled that have passed between both dates and this calculation subtract the account of the special days you have recorded in the table.So, let's step by step:Timeline:We started with the basics. Function datediff He's gonna calculate the difference of days between two dates. It is important here to note that the given calculation is not fully inclusive. For example, datediff('20190507', '20190508') returns the value of 1 day. If we want a totally inclusive value, we must add 1. This will be so if, for example, we are calculating the days worked by one person, and not the days between one date and another. From here on, the solution will assume that we are making a totally inclusive calculation.declare @FechaIni date = '20190507';
declare @FechaFin date = '20190513';
select DATEDIFF(day, @FechaIni, @FechaFin) DiasCalendario;
Return:DiasCalendario
6
(1 row affected)
Calculation of business days:We're going to assume that we work Monday through Friday, and therefore, we're interested. Get out. of the day account, weekends (2 days per week). This calculation is simple if we find a way to know how many weekends have passed between two dates. Luckily, if the same function 'datediff' we pass the parameter weekTo give us the result SQL Server calculates the difference between the week number of both dates and not based on the number of days. Therefore, we will know how many Sundays there are between the two dates. We can then subtract this number multiplied by two of the calendar days to know the working days.declare @FechaIni date = '20190507';
declare @FechaFin date = '20190513';
select DATEDIFF(day, @FechaIni, @FechaFin) + 1 --días calendario
- DATEDIFF(WEEK, @FechaIni, @FechaFin) * 2 --fines de semana
DiasHabiles
;
This returns us:DiasHabiles
5
(1 row affected)
To complete this part of the answer, the formula of working days would fail if any of the dates is Sunday, the complete calculation that works for any date would be:declare @FechaIni date = '20190507';
declare @FechaFin date = '20190513';
select datediff(day, @FechaIni, @FechaFin) + 1 --días calendario
- datediff(week, @FechaIni, @FechaFin) * 2
- case when (datepart(dw, @FechaIni) + @@datefirst + 5) % 7 = 6 then 1 else 0 end
+ case when (datepart(dw, @FechaFin) + @@datefirst + 5) % 7 = 6 then 1 else 0 end --Fines de semana
DiasHabiles
;
It continues to return, in the example dates, the same value.Rest of special daysFinally, assuming that we have a special day table, which I will simulate with a CTE, we finally subtract the number of special day occurrences between both dates.The selection is simple, something like this will do the job:select count(1) from DiasEspeciales where Fecha between @FechaIni and @FechaFin
Already integrated into the code, it would remain so1:declare @FechaIni date = '20190507';
declare @FechaFin date = '20190513';
with
DiasEspeciales as (
select cast('20190101' as date) Fecha
union all select '20190508'
union all select '20190510'
union all select '20191225'
)
select datediff(day, @FechaIni, @FechaFin) + 1 --días calendario
- datediff(week, @FechaIni, @FechaFin) * 2
- case when (datepart(dw, @FechaIni) + @@datefirst + 5) % 7 = 6 then 1 else 0 end
+ case when (datepart(dw, @FechaFin) + @@datefirst + 5) % 7 = 6 then 1 else 0 end -- fines de semana
- coalesce((select count(1) from DiasEspeciales where Fecha between @FechaIni and @FechaFin), 0) -- días especiales
DiasLaborales
;
Which gives us the following result:DiasLaborales
3
(1 row affected)
TestingSince the result obtained is 3 days, and not 4 as specified in the question, we will check to see if we are well in the answer:Fecha Día Especial Contador
============ ========== =========== ===========
07/mayo/2019 Martes NO 1
08/mayo/2019 Miércoles SI
09/mayo/2019 Jueves NO 2
10/mayo/2019 Viernes SI
11/mayo/2019 Sabado
12/mayo/2019 Domingo
13/mayo/2019 Lunes NO 3
As we can see, the answer 3 days It's right, and not the 4th.WarningIn the special dates table there should be no weekend dates. If there is a possibility, those dates would have to be excluded if not those days will be subtracted twice. That improvement that, in my opinion, should have a system made for the real world, remains as an exercise for the reader.1: I have intentionally added a couple more dates to the CTE of special dates, just to make the example more complete.