Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    30

    Unanswered: calculate business hours between two dates

    Does anyone have any idea how I could calculate the business hours between two dates (M-F 9-5). Ideally it would also skip holidays, but I will take any help I can get.


    Thanks,

    Rob

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Create a table of sequential numbers in your database. This is very hand to have, and can help you solve a lot of date-span related problems:

    CREATE TABLE [dbo].[sequential_numbers] ([value] [int] NOT NULL)
    GO

    Populate the table with sequential values, starting with zero:

    DECLARE @value int
    Set @value = 0
    While @value <= 8760
    BEGIN
    INSERT INTO sequential_numbers (value) SELECT @value where not exists (select * from sequential_numbers where value = @value)
    SET @value = @value + 1
    END
    GO

    --Then, assuming you have a table (Holidays) that stores non-business days:
    SELECT count(*) * 8 BusinessHours
    FROM sequential_numbers
    LEFT OUTER JOIN Holidays on DateAdd(dd, value, @StartDate) = Holidays.HolidayDate
    WHERE DateAdd(dd, value, @StartDate) <= @EndDate
    AND DateName(dw, DateAdd(dd, value, @StartDate)) not in ('Saturday', 'Sunday')
    AND Holidays.HolidayDate is null

    blindman

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    you can accomplish the same withouth creating a permanent table:

    create function dbo.fn_CartesianProduct() returns table as
    return (
    select top 100 percent id = (a0.id + a1.id + a2.id + a3.id) from
    (
    select 0 id union select 1 union select 2 union
    select 3 union select 4 union select 5 union
    select 6 union select 7 union select 8 union select 9
    ) a0,
    (
    select 0 id union select 10 union select 20 union
    select 30 union select 40 union select 50 union
    select 60 union select 70 union select 80 union select 90
    ) a1,
    (
    select 0 id union select 100 union select 200 union
    select 300 union select 400 union select 500 union
    select 600 union select 700 union select 800 union select 900
    ) a2,
    (
    select 0 id union select 1000 union select 2000 union
    select 3000 union select 4000 union select 5000 union
    select 6000 union select 7000 union select 8000 union select 9000
    ) a3
    order by 1
    )
    go

    select * from dbo.fn_CartesianProduct()

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Cool code.

    I've accomplished the same thing using a user-defined function that returns a table of sequential numbers between two values, but not using your algorithm , for databases where I was not allowed to create a table.

    The sequential_numbers table has come in handy so many times, that I just put it in all my databases.

    blindman

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    the advantage that I have with this function is that I can do unlimited number of calculations without having to do any io, including generating a calendar, for example, from 01/01/1753 through 06/25/2277 (you just need to add another sub-query into the function above).

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    The sequential_numbers table has come in handy so many times, that I just put it in all my databases.
    extremely handy, yes

    i call it the Integers table

    see Finding all the dates between two dates (free registration may be required)

    rudy
    http://r937.com/

  7. #7
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27
    figured i'd chime in to say thanks (esp. to blindman)...this thread def just made my life easier. and i wanted to bump it up since it is very useful. thanks everyone, i will certainly be frequenting this site from now on.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •