Selectively summing hours from a date range

Hello,
I'm trying to use a sql query(s), perhaps along with a user defined function to find out how long a ' repair ticket' was held, given a date range and the fact that employees work from 8 to 5, and do not work on sundays or holidays. The holidays will come from a separate table--call it tblHolidays--listing specific dates, such as 2011-10-10 for columbus day.  The Hours will come from another table, call it tblEmpInfo with date fields StartTime and QuitTime (8 am and 5 pm respectively) For Example:  If you simply run
 select datediff(hh, '2011-10-07 8 am', '2011-10-11 5 pm')
you come up with 105  hours. But you need to disregard sunday, october 9, and also columbus day on 2011-10-10. Plus, the days are only 9 hours. So what we want is 9 hours on the 7'th + 9 hours on the 8'th + 9 hours on the 11'th = 27 hours -- not 105 hours.
OutOnALimbAlwaysAsked:
Who is Participating?
 
Mattijs33Commented:
You could count the complete days and multiply that by 9. When a holiday or sunday is in that range, you can exclude those from the number of days. When the first and last day are full days (e.g. from 1 pm - 5 pm), you can add those hours.
0
 
OutOnALimbAlwaysAuthor Commented:
Matt- sounds logical, but I'm fairly new to sql. Could I get some sort of sample query using the two dates I provided in my question, and assuming they started 8 am  on the 7'th and quit 5 pm on the 11'th? In fact, we can always assume they start at 8 and quit at 5.
0
 
OutOnALimbAlwaysAuthor Commented:
Ok, the best I can come up with in the way of sql is

declare @opendate smalldatetime -- variables may be replaced by table fields
declare @EndDate  smalldateTime
set @opendate = '2011-10-07'
set @EndDate = '2011-10-11'
select (datediff(d, @opendate, @EndDate) + 1) *
(select max(datediff(hh, startTime, EndTime)) from businessHours)

The result is 45; there are two 9 hour days I need to subtract to come up with the correct result of 27. These would be Sunday and columbus day.  The holiday table-- tblHolidays -- is simply one dateTime column called holidayDate populated by the specific dates of holidays, such as 2011-10-10

I used the max function because the table is set up (for some reason) with two equal records.
I have a suspicion sundays could be somehow taken care of with the @@datefirst function, but am not sure how to implement it.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
OutOnALimbAlwaysAuthor Commented:
businesshours = tblempinfo
0
 
Mattijs33Commented:
You can get the columbus day by counting the number of day that exist between the @opendate and @enddate
something like

select count(*) from tblHoldiays where HolidayDate > @opendate and HolidayDate< @endDate

Open in new window

Assuming HolidayDate is 2011-10-10.

With the sundays you could do something like this

You can get the day of the week with
CASE DATEPART(weekday,@dtDate)

Open in new window

When the result is 1, it is a sunday (http://blog.sqlauthority.com/2007/07/23/sql-server-udf-get-the-day-of-the-week-function/)

If you use that statement for both the begindate and enddate you will get to numbers.
We know that 1 is a sunday and 7 is a saterday. When the beginday is larger than the endday, than there is a sunday

When the datedaiff results more than 7 days, there is at least one sunday)
You need to get the number of weeks ((enddate - begindate) div 7). Add that result to the one above and you should get your desired result.

0
 
OutOnALimbAlwaysAuthor Commented:
Well, I ended up doing this, and got a little practice in Tsql !(Most of my experience is vb6 and access)
It seems to work ok.

declare @opendate smalldatetime --replace with field
declare @closedate  smalldateTime -- replace with field
declare @WorkHours int
declare @sundayCount int
declare @holidayCount int
declare @loopknt int
declare @testdate smalldatetime
--------------------------------------------
--select max-- for some reason there are two records in business hours
set @workHours = (select max(datediff(hh, startTime, EndTime)) from businessHours)
set @opendate = '2011-10-07' --test
set @closedate = '2011-10-11' --test
set @loopknt = 0
set @sundayCount = 0
-- calculate number of holidays betwee @opendate and @closedate
set @holidayCount = (select count(*) from tblHolidays where
HolidayDate between @openDate and @closedate)
set @testdate = convert(smalldatetime,'1900-01-01')
--------------------------------------------------
--Calculate number of sundays (should it be less than or less than or equal? test)
--add 1 to @opendate to list all the days; see if day was sunday.
while @testdate  <= @closedate
begin
      set @testdate = (select @openDate + @loopknt)
      if DATENAME(dw, @testdate) IN ('sunday') set @sundaycount = @sundayCount + 1
      set @loopknt = @loopknt + 1
end
--find legitimate days and subtract non-work days(sundays and holidays)
select ((datediff(d, @opendate, @closedate) + 1) * @workHours) -
(@workhours * @sundayCount) - (@workhours * @holidayCount)
0
 
OutOnALimbAlwaysAuthor Commented:
Thanks for the select count and the 9 hour idea. The whole thing had just seemed like too much, breaking it apart helped a lot, as I guess it always does.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.