CODEWITHSUNDEEP
sqlsql-server
abs786123
abs786123

Reputation: 609

Business Hours Logic and exceptions to Opening Hours

I Have created a date dimension from using the following:

https://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho

This is just a standard one and I have added 2 columns for OpeningTime and Closing Time with the value of Opening being 08:00:00 and Closing being 18:00:00.

I also have a computed field that works out the difference in minutes between the start time and end time as a non persistent computed field.

I have the following Logic below, but before give you this, let me set the scene of showing the context of the situation of the business, so they are normally open from 8am to 6pm Mon - Fri and also Sat at 8am - 1pm

However there can be exception to the rules as they can open for longer or even on sundays. The Code i have below for some reason falls over by showing less on a sunday given that on rare occasions if they were to be open on a sunday for argument sake 8am to 1pm. Obviously for whatever extending opening hours they are open for even on a Sunday, I would manually have to add it this to the Dim_Date calender which for starttime and enddate I have 00:00:00 for both giving me a 0 for datediff in min by default unless the business tell me otherwise.

But the following Code does NOT take that into account and calculates less than what it should be, something wrong with the code if someone can please provide a solution, basically I just want the code to be flexible so I only make changes to the calender even for extended hours or unusual business hours and the code to reflect that. Thank you. 

declare @Date1 datetime = '2017-08-01 08:00:00'
declare @Date2 datetime = '2017-08-07 09:10:00'


declare @StartTime time = cast(@Date1 as time)
declare @EndTime time = cast(@Date2 as time)
declare @CCStartTime time = (select StartTime from dim_date where id = convert(nvarchar(8),@Date1,112))
declare @CCEndTime time = (select EndTime from dim_date where id = convert(nvarchar(8),@Date2,112))


declare @ActualStart time = (select case 
            when datename(DW,@Date1)='Sunday' then '00:00:00'
            when @StartTime between '00:00:00' and @CCStartTime then @CCStartTime
            when @StartTime between  @CCStartTime and @CCEndTime then @StartTime
            when @StartTime between @CCEndTime and  '23:59:59' then @CCEndTime end)

declare @ActualEnd time = (select case 
            when datename(DW,@Date2)='Sunday' then '00:00:00'
            when @EndTime between '00:00:00' and @CCStartTime then @CCStartTime
            when @EndTime between @CCStartTime and @CCEndTime then @EndTime
            when @EndTime between @CCEndTime and '23:59:59' then @CCEndTime end)


declare @DiffrenceStart int = isnull(DATEDIFF(minute,@CCStartTime,@ActualStart),0)

declare @DiffrenceEnd int = isnull(DATEDIFF(minute,@ActualEnd,@CCEndTime),0)

/*
select @StartTime as StartDate
select @EndTime as EndDate
select @CCStartTime as CCStartDate
select @CCEndTime as CCEndDate
select @ActualStart as ActualStart
select @ActualEnd as ActualEnd
select abs(@DiffrenceStart) as DiffrenceStart 
select abs(@DiffrenceEnd) as DifrenceEnd
*/



select sum(Min)- (@DiffrenceStart + @DiffrenceEnd)
from dim_date
where id between convert(nvarchar(8),@Date1,112) and convert(nvarchar(8),@Date2,112)

Upvotes: 4

Views: 437

Answers (1)

Horaciux
Horaciux

Reputation: 6477

Look at this:

declare @ActualEnd time = (select case 
            when datename(DW,@Date2)='Sunday' then '00:00:00'

souldn't it be:

declare @ActualEnd time = (select case 
            when datename(DW,@Date2)='Sunday' then '23:59:59'

Upvotes: 2

Related Questions