Dear carlosdanielsousa,
Here is the bug:
You need to move the code
if dbo.ufninc_DataAMeiaNoite(@VarCalcCreateDate) in (select dbo.ufninc_DataAMeiaNoite (EffectiveIntervalStart) from dbo.CalendarRuleBase where ExtentCode=2 and SubCode=5)
set @VarCalcCreateDate = dbo.ufninc_DataInicioTrab(@VarCalcCreateDate + 1)
7 lines down its present position in your code, ie just after
set @VarCalcCreateDate = dbo.ufninc_DataInicioTrab(@VarCalcCreateDate + 1)
end
Because of this the value @DataInicio is never getting equal to @DataFim and it is stuck into an infinite loop.
Other than this you seems to have done it impeccably and undrestood the essence. Congrats!
Secondly, in the course of debug I found out another bug which originated from my script.
Replace
if DATEPART(dw, @VarCalcCreateDate) = 1
with
if DATEPART(dw, @VarCalcCreateDate) = 7
(This mistake happened because in my workplace the weekly off days are Thursday and Friday. I translated it for the SQLServerCentral script to make it Saturday, Sunday. The mistake happened in the course of this change.)
Let us know your feedback.
Regards,
Maz
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]