Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Selectively summing  hours from a date range

Posted on 2011-10-12
7
Medium Priority
?
254 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:OutOnALimbAlways
  • 5
  • 2
7 Comments
 
LVL 4

Accepted Solution

by:
Mattijs33 earned 1500 total points
ID: 36958946
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
 

Author Comment

by:OutOnALimbAlways
ID: 36959002
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
 

Author Comment

by:OutOnALimbAlways
ID: 36959869
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:OutOnALimbAlways
ID: 36959873
businesshours = tblempinfo
0
 
LVL 4

Assisted Solution

by:Mattijs33
Mattijs33 earned 1500 total points
ID: 36960193
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
 

Author Comment

by:OutOnALimbAlways
ID: 36963071
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
 

Author Closing Comment

by:OutOnALimbAlways
ID: 36963162
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard 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.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 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