asked on

T-SQL Calculate Window Times (including Holidays)

Hi All,

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 
(
	[CompanyHolidayID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Open in new window

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

Open in new window

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.

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kyle Abrahams, PMP

ASKER

Hi Scott,

Correct that customer start and end times are in a config. They don't have to be passed in as variables. Thanks for the advice on the company table . . . our custom ORM (inherited)requires we have an int identity PK field.

My first approach is using the f_table_date found here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

cross join that with #hrs (hrs int) and #mins (mi int, ms, int)
where hrs = 0-23 and mi = 0-59 with ms being 1000. This way I can just do the sum (case) x3 statement.

Code creation for #hrs and #mins:

declare @i int
set @i = 0

create table #hrs (hr int)

while @i <= 23
begin
 insert into #hrs select @i
 set @i = @i + 1
end

create table #mins (mi int, ms int)
set @i = 0
while @i < 60
begin
  insert into #mins select @i, 1000
  set @i = @i + 1
end

Open in new window

I'll be working on this throughout the day, but will be checking back and posting my progress.

Zberteoc

"Btw, you don't need and shouldn't want the "ID" column in the Holidays table. "

You don't even need a index on that table if it will be in the range of hundreds of rows, maybe even less. But if you do then definitively should be an int or even tinyint incremental ID just to keep things safe. Index on other columns not needed. This is a small table and there is no issue on performance.

Kyle Abrahams, PMP

ASKER

Off hour non-holiday query:

declare @StartDate smallDateTime 
declare @EndDate smallDateTime 
declare @startDay int
declare @endday int
declare @starttime time
declare @endtime time

select @startDay = 6, @endDay = 2, @starttime = '20:00', @endTime ='7:30', 
	@StartDate = '7/1/2014 9:30 AM',
	@endDate =  '7/15/2014 8:30 AM'


select
f.Date, hr, mi,
case when 
	(
     (
     f.Day_Of_Week = @startDay and
     cast(convert(varchar,hr) + ':' + convert(varchar,mi) as time) >= @startTime 
     ) or
    (
	  f.Day_Of_Week = @endDay and
      cast(convert(varchar,hr) + ':' + convert(varchar,mi) as time) <= @endTime
     )
     or
     f.day_of_week > @startday
     or
     f.day_of_week < @endday 
     ) and
     a.effectiveDate is null
     then ms else 0 end as offHoursMS
from 
f_table_date(@startDate, @endDate) f
left join appCompanyHolidays a on f.date = a.effectivedate and a.ishalfday = 0
cross join #hrs cross join #mins     
where 	case when 
    (
     (
     f.Day_Of_Week >= @startDay and
     cast(convert(varchar,hr) + ':' + convert(varchar,mi) as time) >= @startTime 
     ) or
    (
	  f.Day_Of_Week <= @endDay and
      cast(convert(varchar,hr) + ':' + convert(varchar,mi) as time) <= @endTime
     )
       or
     f.day_of_week > @startday
     or
     f.day_of_week < @endday
     )
      and
     a.effectiveDate is null
    
     then ms else 0 end  > 0

Open in new window

I did this to encompass July 4th being a friday and a holiday. Results seem correct but let me know if you see anything.

Next up is the holiday query which should be a lot easier.

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kyle Abrahams, PMP

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for ged325's comment #a39895284

for the following reason:

Figured out.

Zberteoc

You can't answer "figured out" and ignore the time and work that has been done by the experts to help you "figuring out". You have to give some point to who's answer you think helped you the most for that. You can also split point if you consider fit.

Zberteoc

See my previous comment.

Kyle Abrahams, PMP

ASKER

When you look at the final query as compared to what the suggestions posted by other experts were I didn't feel enough help was given to warrant awarding points. The only useful peace of information to solving the problem at hand was Scott's suggestion of using an inline table . . . something which I was already on to. I've also asked if anyone saw any flaws in the final query and got no response. It's in the mods hands, will wait for a decision.

Zberteoc

So we will wait for the admin decision. I still think you're wrong.

That is why there is the possibility to give as many point you want from 1 to 500, plus the grade of A, B or C so that you can appreciate the level of help you got. As long as there is any bit of info that set you on the right track the expert who gave you that info deserves to be compensated for the attention, time and effort invested to answer you. We're not talking about many after all, as we are volunteers, but even we would...

SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zberteoc

That is up to you really but for sure not zero. Ask yourself how much would you expect if you gave that answer.

As a hint you mentioned that Scott's suggestion pointed you to the right direction. That is definitely worth some points.

Maybe is just me but I would never close a question with 0 points awarded if somebody bothered to answer something, unless of course they did that only to mention the weather. :)

Cheers!

Kyle Abrahams, PMP

ASKER

Accepting my statement as the solution, partial points to Scott for indicating an inline table should be used.