Selectively summing  hours from a date range

Posted on 2011-10-12
Last Modified: 2012-06-21
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.
Question by:OutOnALimbAlways
    LVL 4

    Accepted Solution

    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.

    Author Comment

    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.

    Author Comment

    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.

    Author Comment

    businesshours = tblempinfo
    LVL 4

    Assisted Solution

    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 (

    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.


    Author Comment

    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
          set @testdate = (select @openDate + @loopknt)
          if DATENAME(dw, @testdate) IN ('sunday') set @sundaycount = @sundayCount + 1
          set @loopknt = @loopknt + 1
    --find legitimate days and subtract non-work days(sundays and holidays)
    select ((datediff(d, @opendate, @closedate) + 1) * @workHours) -
    (@workhours * @sundayCount) - (@workhours * @holidayCount)

    Author Closing Comment

    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.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video discusses moving either the default database or any database to a new volume.

    745 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

    19 Experts available now in Live!

    Get 1:1 Help Now