I need to calulate actual hours that fall into 3 buckets:
- Regular Hours
- Off Hours
- Holiday Hours
My start time and end time are inconsistent and are coming from columns in a table. (EG: I'm doing this for different customers . . . for one customer the window could be Friday 5:00 PM to Monday 7:30 AM and then for another customer Friday 8:00 PM to Monday 10:00 AM)
The Holidays are defined by us in a holiday table.
I'm looking for a generic table function (or 3 scalar functions) which will return RegularHours, OffHours, HolidayHours
The holidays table is defined as:
CREATE TABLE [dbo].[appCompanyHolidays](
[CompanyHolidayID] [int] IDENTITY(1,1) NOT NULL,
[EffectiveDate] [datetime] NOT NULL,
CONSTRAINT [PK_appCompanyHolidays] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Just some starter code for you to work with:
declare @StartDate smallDateTime
declare @EndDate smallDateTime
set @StartDate = '2/7/2014 9:30 AM'
set @endDate = '2/24/2014 8:30 AM'
declare @startDay int
declare @endday int
declare @starttime time
declare @endtime time
select @startDay = 5, @endDay = 7, @starttime = '20:00', @endTime ='7:30'
select @startDate startdate, @endDate enddate, @startday startDay, @endDay endDay, @startTime StartTime, @endTime endTime
I've done a business hour calculation but never one that's flexible based on the customer. If anyone has any insights feel free to post.