Solved

T-SQL Calculate Window Times (including Holidays)

Posted on 2014-02-27
14
282 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 40

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 40

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 40

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 40

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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 40

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 40

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 40

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

911 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

17 Experts available now in Live!

Get 1:1 Help Now