Hoping someone can tell me where I am going wrong here, I started with a Business Hours query found at Calculate business hours between two dates, it's the second top answer because the top answer was not providing the correct output upon testing. The issue I am having is when I cross over to a new day and the end datetime exceeds the Business Day End. Here is my test:
select dbo.BusinessSeconds('09:00:00','17:00:00','2014-12-24 16:59:59','2014-12-26 18:00:00');
So if I go from 1 second before COB to the start of the next day, I get 1 second, which is correct. If I go from the same start to the end of the day the next day, I get 28801, or 1 full day and 1 second - which is also correct. However, if I extend the date end time to 17:00:01 through 17:59:59 it also includes those seconds but if I hit 18:00:00, the time truncates back to the original 17:00:00 end of day. So I will get correct data for any given end time unless it is during the 17:00:00-17:59:59 timeframes and then I potentially get up to an extra hour of time that I should not have.
Any help would be greatly appreciated as I have a deliverable on this by tomorrow.
ALTER Function [dbo].[BusinessSeconds](@BusinessDayStart TIME,@BusinessDayEnd TIME,@StartDate DATETIME,@EndDate DATETIME) Returns Int
AS
Begin
--TEST: select dbo.BusinessSeconds('09:00:00','17:00:00','2014-12-24 16:59:59','2014-12-26 18:00:00');
Declare @WorkMin INT = 0 -- Initialize counter
Declare @Reverse BIT -- Flag to hold if direction is reverse
Declare @StartHour TIME = @BusinessDayStart -- Start of business hours (can be supplied as an argument if needed)
Declare @EndHour TIME = @BusinessDayEnd -- End of business hours (can be supplied as an argument if needed)
Declare @Holidays Table (HDate DateTime) -- Table variable to hold holidayes
-- If dates are in reverse order, switch them and set flag
If @StartDate>@EndDate
Begin
Declare @TempDate DateTime=@StartDate
Set @StartDate=@EndDate
Set @EndDate=@TempDate
Set @Reverse=1
End
Else Set @Reverse = 0
-- Get country holidays from table based on the country code (Feel free to remove this or modify as per your DB schema)
-- Insert Into @Holidays(HDate) select DATEADD(month,((LEFT(CAL_ID,4)-1900)*12)+LEFT(RIGHT(CAL_ID,4),2)-1,RIGHT(CAL_ID,2)-1) from B_CALENDAR where CAL_DAY_PK = 263 and HDATE >= DateAdd(dd, DateDiff(dd,0,@StartDate), 0)
Insert Into @Holidays (HDate) Select HolidayDate from V_BUS_HOL Where HolidayDate >= DateAdd(dd, DateDiff(dd,0,@StartDate), 0)
If CAST(@StartDate as TIME) < @StartHour Set @StartDate = CAST(CAST(@StartDate as DATE) as DATETIME) + CAST(@StartHour as DATETIME) -- If Start time is less than start hour, set it to start hour
If CAST(@StartDate as TIME) >= DATEADD(HOUR,1,@EndHour) Set @StartDate = CAST(CAST(DATEADD(DAY,1,@StartDate) as DATE) as DATETIME) + CAST(@StartHour as DATETIME) -- If Start time is after end hour, set it to start hour of next day
If CAST(@EndDate as TIME) >= DATEADD(HOUR,1,@EndHour) Set @EndDate = CAST(CAST(@EndDate as DATE) as DATETIME) + CAST(@EndHour as DATETIME) -- If End time is after end hour, set it to end hour
If CAST(@EndDate as TIME) < @StartHour Set @EndDate = CAST(CAST(DATEADD(DAY,-1,@EndDate) as DATE) as DATETIME) + CAST(@EndHour as DATETIME) -- If End time is before start hour, set it to end hour of previous day
If @StartDate>@EndDate Return 0
-- If Start and End is on same day
If DateDiff(Day,@StartDate,@EndDate) <= 0
Begin
If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7 -- If day is between sunday and saturday
If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 -- If day is not a holiday
If @EndDate<@StartDate Return 0 Else Set @WorkMin=DATEDIFF(SECOND, @StartDate, @EndDate) -- Calculate difference
Else Return 0
Else Return 0
End
Else Begin
Declare @Partial int=1 -- Set partial day flag
While DateDiff(Day,@StartDate,@EndDate) > 0 -- While start and end days are different
Begin
If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7 -- If this is a weekday
Begin
If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 -- If this is not a holiday
Begin
If @Partial=1 -- If this is the first iteration, calculate partial time
Begin
Set @WorkMin=@WorkMin + DATEDIFF(SECOND, @StartDate, DateAdd(hour, DATEPART(HOUR,@EndHour), DateDiff(DAY, 0, @StartDate)))
Set @StartDate=DateAdd(hour, DATEPART(HOUR,@StartHour)+24, DateDiff(DAY, 0, @StartDate))
Set @Partial=0
End
Else Begin -- If this is a full day, add full minutes
Set @WorkMin=@WorkMin + (DATEPART(HOUR,@EndHour)-DATEPART(HOUR,@StartHour))*60
Set @StartDate = DATEADD(DD,1,@StartDate)
End
End
Else Set @StartDate = DATEADD(DD,1,@StartDate)
End
Else Set @StartDate = DATEADD(DD,1,@StartDate)
End
If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7 -- If last day is a weekday
If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 -- And it is not a holiday
If @Partial=0 Set @WorkMin=@WorkMin + DATEDIFF(SECOND, @StartDate, @EndDate) Else Set @WorkMin=@WorkMin + DATEDIFF(SECOND, DateAdd(hour, DATEPART(HOUR,@StartHour), DateDiff(DAY, 0, @StartDate)), @EndDate)
End
If @Reverse=1 Set @WorkMin=-@WorkMin
Return @WorkMin
End
