SQL SERVER - Function Adding hours to a datetime field with the result within working hours

I have the function below and it works fine, but now I need to improve it : 1-I need to append the lunch time. ( in lunch time the company don't work) @StartOfLunch = 12.5, @EndOfLunch = 13.5, 2- i have a table of DONT WORK (holidays / vacations / ...) ( holiday/vacations the company don't work) 3- i have a table for EXTRA WORK ( in some cases the company make extra-hours and work in days that normally do not work) Example of table for don't-work and extra-work name data_start data_end hour_start hour_end extra ( 1 - is for work / 0 - for don't work) .... --------------------------------------------------------------------------- ALTER FUNCTION [dbo].[addhoras] (@Date DATETIME, @DateAdd DATETIME) RETURNS DATETIME AS --Select [login to view URL] ('2018-01-23 10:00:00.000' , '1900-01-01 02:00:00.000') --DECLARE @Date DATETIME = '2018-01-23 10:00:00.000'; --DECLARE @DateAdd DATETIME = '1900-01-01 02:00:00.000'; BEGIN DECLARE @StartOfDay FLOAT = 9.5 ; DECLARE @EndOfDay FLOAT = 17.5 ; DECLARE @Datefinal DATETIME --fix up start date --before start of day, move to start of day IF ((CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24) < @StartOfDay) BEGIN SET @Date = DATEADD(mi, @StartOfDay * 60, DATEDIFF(dd,0,@Date)) END --after close of day, move to start of next day IF ((CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24) > @EndOfDay) BEGIN SET @Date = DATEADD(mi, @StartOfDay * 60, DATEDIFF(dd,0,@Date)) + 1 END --move to monday if on weekend WHILE DATENAME(dw, @Date) IN ('Saturday','Sunday') BEGIN SET @Date = @Date + 1 END --get the number of hours to add and the total hours per day DECLARE @HoursPerDay FLOAT DECLARE @HoursAdd FLOAT SET @HoursAdd = DATEDIFF(hh, '1900-01-01 00:00:00.000', @DateAdd) SET @HoursPerDay = @EndOfDay - @StartOfDay --date the time of geiven day DECLARE @CurrentHours FLOAT SET @CurrentHours = CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24 --if we stay in the same day, all is fine IF (@CurrentHours + @HoursAdd <= @EndOfDay) BEGIN SET @Date = @Date + @DateAdd END ELSE BEGIN --remove part of day SET @HoursAdd = @HoursAdd - (@EndOfDay - @CurrentHours) --,ove to next day SET @Date = DATEADD(dd,0, DATEDIFF(dd,0,@Date)) + 1 --loop day WHILE @HoursAdd > 0 BEGIN --add day but keep hours to add same IF (DATENAME(dw,@Date) IN ('Saturday','Sunday')) BEGIN SET @Date = @Date + 1 END ELSE BEGIN --add a day, and reduce hours to add IF (@HoursAdd > @HoursPerDay) BEGIN SET @Date = @Date + 1 SET @HoursAdd = @HoursAdd - @HoursPerDay END ELSE BEGIN --add the remainder of the day SET @Date = DATEADD(mi, (@HoursAdd + @StartOfDay) * 60, DATEDIFF(dd,0,@Date)) SET @HoursAdd = 0 END END END END SET @Datefinal = (SELECT @Date) Return @Datefinal END