Solved

T-SQL Calculate Window Times (including Holidays)

Posted on 2014-02-27
14
281 Views
Last Modified: 2014-03-09
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.
0
Comment
Question by:Kyle Abrahams
  • 7
  • 5
14 Comments
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 50 total points
ID: 39893522
Most efficient would be an in-line table-valued function.

I'm guessing the different customer start and end times are some type of config table as well, right?  If they have to be passed in specifically as variables, that will restrict the code a lot more.

Btw, you don't need and shouldn't want the "ID" column in the Holidays table.  It's wasted space and far less efficient as the clustered key.  Just make the EffectiveDate itself -- or, if needed, (CompanyCode, EffectiveDate) -- the PK.
0
 
LVL 39

Author Comment

by:Kyle Abrahams
ID: 39894933
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39895032
"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.
0
 
LVL 39

Author Comment

by:Kyle Abrahams
ID: 39895145
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.
0
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 0 total points
ID: 39895284
And final query:

Anyone see any flaws?

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'

--FINALS
select cast(
       sum
       (
			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 numeric(18,5)) / 3600000 as offHours,
     
     cast(sum(
				case when
							a.EffectiveDate is not null 
					 then	ms 
					 else   0 end
			  ) as numeric(18,5)
		 ) / 3600000 as HolidayHours  ,
     

     cast(
     sum(
          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
						 AND
						 f.day_of_week > @endday 
					 )
     
			 ) and
			  a.effectiveDate is null
		then ms 
		else 0 end
	   ) as numeric(18,5)) / 3600000  as NormalHours
from 
f_table_date(@startDate, @endDate) f
left join ivcnysw010a.ivcilocal.dbo.appCompanyHolidays a on f.date = a.effectivedate and a.ishalfday = 0
cross join #hrs cross join #mins     
where 
     cast(f.date +   cast(convert(varchar,hr) + ':' + convert(varchar,mi) as time) as smalldatetime)>=  @startDate
and  cast(f.date +   cast(convert(varchar,hr) + ':' + convert(varchar,mi) as time) as smalldatetime) <  @endDate

Open in new window


note that the MS in #mins should be 60,000 not 1000:

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, 60000
  set @i = @i + 1
end

Open in new window

0
 
LVL 39

Author Comment

by:Kyle Abrahams
ID: 39900585
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 26

Expert Comment

by:Zberteoc
ID: 39900583
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39900586
See my previous comment.
0
 
LVL 39

Author Comment

by:Kyle Abrahams
ID: 39900612
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39901159
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...
0
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 0 total points
ID: 39901221
It's never my intention to cheat anyone out of points.  The points mean nothing to me as I'm also a qualified expert.  

In your determination, what would be the point value that you would give for that comment?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39901636
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!
0
 
LVL 39

Author Closing Comment

by:Kyle Abrahams
ID: 39915656
Accepting my statement as the solution, partial points to Scott for indicating an inline table should be used.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now