Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Unanswered: Computing Business Hours

    I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am. Call Center Hours are 8-5.

    Essentially I have a number of different alternatives, and have found some potential solutions, including:

    www.dbforums.com/arch/7/2003/9/914261

    However, my situation has a couple of additional twists to the standard 8hrs of business M-F. The call center is open different hours depending upon the day of the week. For example, 8-5 M, 10-7 T, 8-5 W Th F, 10-2 Sat, 10-12 Sun

    Additionally, I would like to remove Holiday's from the calculation for service level as well.

    I have explored a number of different table DTD's, but none seem to be a perfect fit for determining the number of "open" hours between when an interaction arrived, and when it was handled.

    The DTD I have for the Holiday table is as follows:

    CREATE Table Holidays (HolidayDate DateTime)
    GO
    Insert Into Holidays (HolidayDate) Values ('12-25-2004')


    Please let me know what you feel would be the DTD for storing the business hours and also the query for extracting the number of Open hours between two dates

    Thank you in advance

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    your original question was

    I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am.

    Given your examples of business hours for Monday and Saturday what would your answer look like for the above question?

    16 hours for Monday?
    0 hours for Saturday?

    What happens if one of these days is a holiday?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Mar 2004
    Posts
    7
    Originally posted by Paul Young
    your original question was

    I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am.

    Given your examples of business hours for Monday and Saturday what would your answer look like for the above question?

    16 hours for Monday?
    0 hours for Saturday?

    What happens if one of these days is a holiday?
    Based upon the previous example (8-5) the service level would be 3 hrs. Based upon the "flexible" (and actual scenario) it would have been 1 hour. Call center closed at 5pm and opened at 10am on Tuesday.

    NET: I am trying to compute the number of hours the call center is open during a range of time.

    Thanks

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    okay, lets stick to Monday then...

    if the call is received at 04:00 pm and the call center closes at 05:00pm then there was one hour on Monday that the call could have been worked. The call center opened on Tuesday at 10:00 am and the call was process during the first our hour so add an additional hour, that's two hours in total. I am missing something as I don't see three or one hours as the answer.

    I am almost finished with an example but would like to present a close answer. could you help with the above question?
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Mar 2004
    Posts
    7
    Originally posted by Paul Young
    okay, lets stick to Monday then...

    if the call is received at 04:00 pm and the call center closes at 05:00pm then there was one hour on Monday that the call could have been worked. The call center opened on Tuesday at 10:00 am and the call was process during the first our hour so add an additional hour, that's two hours in total. I am missing something as I don't see three or one hours as the answer.

    I am almost finished with an example but would like to present a close answer. could you help with the above question?

    Sorry, the original question posed two scenarios:

    Typical 8-5M-F
    Reality (variable hours) I.E. Monday8a-5p , 10a-7p T, 8-5 W Th F, 10-2 Sat, 10-12 Sun.

    Thus, the 3hrs versus 1hr.

    Let's eliminate the "Typical" scenario, as I have examples of that. What I am struggling with is the reality (variable) open hours scenario, and how to best report SLA's when the center is open.

    Sorry for the confuzzzzzion

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    okay, still not sure I understand how you are calculating days but maybe you can use this:

    Code:
    declare @HolidayCalendar table(Holiday datetime)
    declare @BusinessHours table(DOW tinyint, OpenAt smallint, CloseAt smallint)
    
    declare @dtFrom       datetime
          , @dtTemp       datetime
          , @dtTo         datetime
          , @DOW          tinyint
          , @HolidayCount tinyint
          , @Hours        tinyint
    
    insert into @HolidayCalendar values('17-Mar-2004')
    insert into @BusinessHours values(1, 1000, 1200)
    insert into @BusinessHours values(2, 0800, 1700)
    insert into @BusinessHours values(3, 1000, 1900)
    insert into @BusinessHours values(4, 0800, 1700)
    insert into @BusinessHours values(5, 0800, 1700)
    insert into @BusinessHours values(6, 0800, 1700)
    insert into @BusinessHours values(7, 1000, 1400)
    
    select @dtFrom       = '17-Mar-2004 12:00'
         , @dtTo         = '18-Mar-2004 10:00'
         , @HolidayCount = 0
         , @Hours        = 0
    
    select '                 Start', @dtFrom as '@dtFrom', @dtTo as '@dtTo', @Hours as 'Hours'
    
    --
    -- If the problem was solved in one day then only need to calculate hours for a single day
    --
    if datediff(day,@dtFrom,@dtTo) = 0 begin
      set @Hours = datediff(hour,@dtFrom,@dtTo)
    end else begin
      --
      -- Test for @dtFrom being a holiday, whould need to move time to start of first business day after the holiday
      --
      if exists(select * from @HolidayCalendar where convert(varchar(10),@dtFrom,120) = Holiday) begin
        while exists(select * from @HolidayCalendar where convert(varchar(10),@dtFrom,120) = Holiday) begin
          --
          -- Move @dtFrom 1 day closer to a business day
          --
          set @dtFrom = dateadd(day,1,convert(varchar(10),@dtFrom,120))
        end
      end
      
      select '     @dtFrom = Holiday', @dtFrom as '@dtFrom', @dtTo as '@dtTo', @Hours as 'Hours'
      --
      -- Test for @dtFrom being after business hours, would want to move to start of next day.
      --
      if exists(select * from @BusinessHours where DOW = datepart(weekday,@dtFrom) and datepart(hour,@dtFrom) > (CloseAt/100)) begin
        set @dtFrom = dateadd(day,1,convert(varchar(10),@dtFrom,120))
      end
      
      select '   @dtFrom after hours', @dtFrom as '@dtFrom', @dtTo as '@dtTo', @Hours as 'Hours'
      --
      -- Test for @dtFrom being before business hours, would want to move to start of this business day.
      --
      if exists(select * from @BusinessHours where DOW = datepart(weekday,@dtFrom) and datepart(hour,@dtFrom) < (OpenAt/100)) begin
        select @dtFrom = dateadd(hour,(OpenAt/100),convert(varchar(10),@dtFrom,120)) 
          from @BusinessHours 
         where DOW = datepart(weekday,@dtFrom)
      end
      
      select '  @dtFrom before hours', @dtFrom as '@dtFrom', @dtTo as '@dtTo', @Hours as 'Hours'
      --
      -- Calculate the number of hours for the day the ploblem was submmited 
      -- provided the adjusted dates, @dtFrom & @dtTo are not on the same day
      --
      if datediff(day,@dtFrom,@dtTo) > 0 begin
        set @dtTemp = convert(varchar(10),@dtFrom,120)
        select @Hours = datediff(hour,@dtFrom,dateadd(hour,(CloseAt/100),@dtTemp)) 
          from @BusinessHours 
         where DOW = datepart(weekday,@dtFrom) 
      end
      select '     Hours for 1st day', @dtFrom as '@dtFrom', @dtTemp as '@dtTemop', @dtTo as '@dtTo', @Hours as 'Hours'
      --
      -- Step through each day, if it is a holiday skip else add the number of business hours for that day
      --
      set @dtTemp = dateadd(day,1,convert(varchar(10),@dtFrom,120))
      while datediff(day,@dtTemp,@dtTo) > 0 begin
        if not exists(select * from @HolidayCalendar where Holiday = @dtTemp) begin
          select @Hours = @Hours + (CloseAt - OpenAt) / 100
            from @BusinessHours
           where DOW = datepart(weekday,@dtTemp)
        end
        set @dtTemp = dateadd(day,1,@dtTemp)
      end
    
      select 'Hours for days between', @dtFrom as '@dtFrom', @dtTemp as '@dtTemop', @dtTo as '@dtTo', @Hours as 'Hours'
      --
      -- Calculate the number of hours for the day the problem was resolved
      --
      set @dtTemp = convert(varchar(10),@dtTo,120)
      select @Hours = @Hours + datediff(hour,dateadd(hour,(OpenAt/100),@dtTemp),@dtTo)
        from @BusinessHours 
       where DOW = datepart(weekday,@dtTo) 
      select '     Hours for Lst day', @dtFrom as '@dtFrom', @dtTemp as '@dtTemop', @dtTo as '@dtTo', @Hours as 'Hours'
      
    end
    
    select '                    End', @dtFrom as '@dtFrom', @dtTo as '@dtTo', @Hours as 'Hours'
    this is NOT the optimal code but any changes made would depend on your implamintation. I hope this get you going in the right direction.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Mar 2004
    Posts
    7

    Talking

    THANK YOU SO MUCH! I haven't fully digested this yet, but at first glance it appears to be EXACTLY what I needed.

    Thank you again.

    Mike



    Originally posted by Paul Young
    okay, still not sure I understand how you are calculating days but maybe you can use this:

    Code:
    declare @HolidayCalendar table(Holiday datetime)
    declare @BusinessHours table(DOW tinyint, OpenAt smallint, CloseAt smallint)
    
    declare @dtFrom       datetime
          , @dtTemp       datetime
          , @dtTo         datetime
          , @DOW          tinyint
          , @HolidayCount tinyint
          , @Hours        tinyint
    
    insert into @HolidayCalendar values('17-Mar-2004')
    insert into @BusinessHours values(1, 1000, 1200)
    insert into @BusinessHours values(2, 0800, 1700)
    insert into @BusinessHours values(3, 1000, 1900)
    insert into @BusinessHours values(4, 0800, 1700)
    insert into @BusinessHours values(5, 0800, 1700)
    insert into @BusinessHours values(6, 0800, 1700)
    insert into @BusinessHours values(7, 1000, 1400)
    
    select @dtFrom       = '17-Mar-2004 12:00'
         , @dtTo         = '18-Mar-2004 10:00'
         , @HolidayCount = 0
         , @Hours        = 0
    
    select '                 Start', @dtFrom as '@dtFrom', @dtTo as '@dtTo', @Hours as 'Hours'
    
    --
    -- If the problem was solved in one day then only need to calculate hours for a single day
    --
    if datediff(day,@dtFrom,@dtTo) = 0 begin
      set @Hours = datediff(hour,@dtFrom,@dtTo)
    end else begin
      --
      -- Test for @dtFrom being a holiday, whould need to move time to start of first business day after the holiday
      --
      if exists(select * from @HolidayCalendar where convert(varchar(10),@dtFrom,120) = Holiday) begin
        while exists(select * from @HolidayCalendar where convert(varchar(10),@dtFrom,120) = Holiday) begin
          --
          -- Move @dtFrom 1 day closer to a business day
          --
          set @dtFrom = dateadd(day,1,convert(varchar(10),@dtFrom,120))
        end
      end
      
      select '     @dtFrom = Holiday', @dtFrom as '@dtFrom', @dtTo as '@dtTo', @Hours as 'Hours'
      --
      -- Test for @dtFrom being after business hours, would want to move to start of next day.
      --
      if exists(select * from @BusinessHours where DOW = datepart(weekday,@dtFrom) and datepart(hour,@dtFrom) > (CloseAt/100)) begin
        set @dtFrom = dateadd(day,1,convert(varchar(10),@dtFrom,120))
      end
      
      select '   @dtFrom after hours', @dtFrom as '@dtFrom', @dtTo as '@dtTo', @Hours as 'Hours'
      --
      -- Test for @dtFrom being before business hours, would want to move to start of this business day.
      --
      if exists(select * from @BusinessHours where DOW = datepart(weekday,@dtFrom) and datepart(hour,@dtFrom) < (OpenAt/100)) begin
        select @dtFrom = dateadd(hour,(OpenAt/100),convert(varchar(10),@dtFrom,120)) 
          from @BusinessHours 
         where DOW = datepart(weekday,@dtFrom)
      end
      
      select '  @dtFrom before hours', @dtFrom as '@dtFrom', @dtTo as '@dtTo', @Hours as 'Hours'
      --
      -- Calculate the number of hours for the day the ploblem was submmited 
      -- provided the adjusted dates, @dtFrom & @dtTo are not on the same day
      --
      if datediff(day,@dtFrom,@dtTo) > 0 begin
        set @dtTemp = convert(varchar(10),@dtFrom,120)
        select @Hours = datediff(hour,@dtFrom,dateadd(hour,(CloseAt/100),@dtTemp)) 
          from @BusinessHours 
         where DOW = datepart(weekday,@dtFrom) 
      end
      select '     Hours for 1st day', @dtFrom as '@dtFrom', @dtTemp as '@dtTemop', @dtTo as '@dtTo', @Hours as 'Hours'
      --
      -- Step through each day, if it is a holiday skip else add the number of business hours for that day
      --
      set @dtTemp = dateadd(day,1,convert(varchar(10),@dtFrom,120))
      while datediff(day,@dtTemp,@dtTo) > 0 begin
        if not exists(select * from @HolidayCalendar where Holiday = @dtTemp) begin
          select @Hours = @Hours + (CloseAt - OpenAt) / 100
            from @BusinessHours
           where DOW = datepart(weekday,@dtTemp)
        end
        set @dtTemp = dateadd(day,1,@dtTemp)
      end
    
      select 'Hours for days between', @dtFrom as '@dtFrom', @dtTemp as '@dtTemop', @dtTo as '@dtTo', @Hours as 'Hours'
      --
      -- Calculate the number of hours for the day the problem was resolved
      --
      set @dtTemp = convert(varchar(10),@dtTo,120)
      select @Hours = @Hours + datediff(hour,dateadd(hour,(OpenAt/100),@dtTemp),@dtTo)
        from @BusinessHours 
       where DOW = datepart(weekday,@dtTo) 
      select '     Hours for Lst day', @dtFrom as '@dtFrom', @dtTemp as '@dtTemop', @dtTo as '@dtTo', @Hours as 'Hours'
      
    end
    
    select '                    End', @dtFrom as '@dtFrom', @dtTo as '@dtTo', @Hours as 'Hours'
    this is NOT the optimal code but any changes made would depend on your implamintation. I hope this get you going in the right direction.

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I tried to keep this overly simple and did not worry about efficiency. My goal was to get you thinking about the problem in a diffrent light. If you have a question about any section drop me a line or post it here.

    Please post back with your final solution.
    Last edited by Paul Young; 03-18-04 at 16:30.
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Mar 2004
    Posts
    7
    Paul,

    Thanks a million. I doubt I would have ever come up with the solution that you presented. I have tweeked it a little, and am posting the code change.

    Biggest Delta, is I removed the same day logic. This was making the assumption that an interaction starting and ending the same day was the duration of the interaction. This would have "dinged" the call center in three instances. Interaction came in before start of day and handled during day. If for some reason the day was a Holiday. Or, if the interaction was handled after business hours. (That time after close, but before the workers go home).

    Additionally, I converted the computations into Seconds.

    Finally, I handled when the interaction was handled with NO open business hours. I.E. Came in after close and handled before open.

    However, the logic you provided was invaluable.

    Thanks agian

    HERE'S THE CODE

    --okay, still not sure I understand how you are calculating days but maybe you can use this:



    --code:--------------------------------------------------------------------------------
    declare @HolidayCalendar table(Holiday datetime)
    declare @BusinessHours table(DOW tinyint, OpenAt smallint, CloseAt smallint)

    declare @dtFrom datetime
    , @dtTemp datetime
    , @dtTo datetime
    , @DOW tinyint
    , @HolidayCount tinyint
    , @Hours tinyint
    , @Seconds int

    insert into @HolidayCalendar values('17-Mar-2004')
    insert into @BusinessHours values(1, 1000, 1200)
    insert into @BusinessHours values(2, 0800, 1700)
    insert into @BusinessHours values(3, 1000, 1900)
    insert into @BusinessHours values(4, 0800, 1700)
    insert into @BusinessHours values(5, 0800, 1730)
    insert into @BusinessHours values(6, 0800, 1700)
    insert into @BusinessHours values(7, 1000, 1400)


    select @dtFrom = '17-Mar-2004 07:00:00' --3
    , @dtTo = '18-Mar-2004 07:00:00' --7
    , @HolidayCount = 0
    , @Hours = 0
    , @Seconds = 0

    select ' Start', @dtFrom as '@dtFrom', @dtTo as '@dtTo', @Hours as 'Hours', @Seconds As 'Seconds'

    --
    -- If the problem was solved in one day then only need to calculate hours for a single day
    --

    --
    -- Test for @dtFrom being a holiday, whould need to move time to start of first business day after the holiday
    --
    if exists(select * from @HolidayCalendar where convert(varchar(10),@dtFrom,120) = Holiday) begin
    while exists(select * from @HolidayCalendar where convert(varchar(10),@dtFrom,120) = Holiday) begin
    --
    -- Move @dtFrom 1 day closer to a business day
    --
    set @dtFrom = dateadd(day,1,convert(varchar(10),@dtFrom,120))
    end
    end

    select ' @dtFrom = Holiday', @dtFrom as '@dtFrom', @dtTo as '@dtTo', @Hours as 'Hours', @Seconds as 'Seconds'
    --
    -- Test for @dtFrom being after business hours, would want to move to start of next day.
    --
    if exists(select * from @BusinessHours where DOW = datepart(weekday,@dtFrom) and datepart(hour,@dtFrom) > (CloseAt/100)) begin
    set @dtFrom = dateadd(day,1,convert(varchar(10),@dtFrom,120))
    end

    select ' @dtFrom after hours', @dtFrom as '@dtFrom', @dtTo as '@dtTo', @Hours as 'Hours', @Seconds as 'Seconds'
    --
    -- Test for @dtFrom being before business hours, would want to move to start of this business day.
    --
    if exists(select * from @BusinessHours where DOW = datepart(weekday,@dtFrom) and datepart(hour,@dtFrom) < (OpenAt/100)) begin
    select @dtFrom = dateadd(hour,(OpenAt/100),convert(varchar(10),@dtFrom,120))
    from @BusinessHours
    where DOW = datepart(weekday,@dtFrom)
    end

    select ' @dtFrom before hours', @dtFrom as '@dtFrom', @dtTo as '@dtTo', @Hours as 'Hours', @Seconds as 'Seconds'
    --
    -- Calculate the number of hours for the day the ploblem was submmited
    -- provided the adjusted dates, @dtFrom & @dtTo are not on the same day
    --
    if datediff(day,@dtFrom,@dtTo) > 0 begin
    set @dtTemp = convert(varchar(10),@dtFrom,120)
    select @Hours = CASE WHEN (datediff(hour,@dtFrom,dateadd(hour,(CloseAt/100),@dtTemp)) < 0) Then 0
    ELSE datediff(hour,@dtFrom,dateadd(hour,(CloseAt/100),@dtTemp))
    END,
    @Seconds = CASE WHEN (datediff(ss,@dtFrom,dateadd(minute, Floor(CloseAt/100 * 60) + Right(CloseAt, 2),@dtTemp)) < 0) THEN 0
    ELSE datediff(ss,@dtFrom,dateadd(minute, Floor(CloseAt/100 * 60) + Right(CloseAt, 2),@dtTemp))
    END
    from @BusinessHours
    where DOW = datepart(weekday,@dtFrom)
    end
    select ' Hours for 1st day', @dtFrom as '@dtFrom', @dtTemp as '@dtTemop', @dtTo as '@dtTo', @Hours as 'Hours', @Seconds As 'Seconds'
    --
    -- Step through each day, if it is a holiday skip else add the number of business hours for that day
    --
    set @dtTemp = dateadd(day,1,convert(varchar(10),@dtFrom,120))
    while datediff(day,@dtTemp,@dtTo) > 0 begin
    if not exists(select * from @HolidayCalendar where Holiday = @dtTemp) begin
    select @Hours = @Hours + (CloseAt - OpenAt) / 100,
    @Seconds = @Seconds + Floor((CloseAt - OpenAt) / 100 * 60 * 60) + (Right((CloseAt - OpenAt), 2) * 60)
    from @BusinessHours
    where DOW = datepart(weekday,@dtTemp)
    end
    set @dtTemp = dateadd(day,1,@dtTemp)
    end

    select 'Hours for days between', @dtFrom as '@dtFrom', @dtTemp as '@dtTemop', @dtTo as '@dtTo', @Hours as 'Hours', @Seconds as 'Seconds'
    --
    -- Calculate the number of hours for the day the problem was resolved
    --
    Declare @SubResult int
    Declare @SubDate datetime
    Declare @SubREsult2 int

    set @dtTemp = convert(varchar(10),@dtTo,120)
    select @Hours = @Hours + CASE WHEN datediff(hour,dateadd(hour,(OpenAt/100),@dtTemp),@dtTo) < 0 THEN 0
    ELSE datediff(hour,dateadd(hour,(OpenAt/100),@dtTemp),@dtTo)
    END,
    @Seconds = @Seconds + CASE WHEN datediff(ss,dateadd(ss, Floor((OpenAt/100 * 60 * 60)) + (Right(OpenAt, 2) * 60), @dtTemp), @dtTo) < 0 THEN 0
    ELSE datediff(ss,dateadd(ss, Floor((OpenAt/100 * 60 * 60)) + (Right(OpenAt, 2) * 60), @dtTemp), @dtTo)
    END
    from @BusinessHours
    where DOW = datepart(weekday,@dtTo)
    select ' Hours for Lst day', @dtFrom as '@dtFrom', @dtTemp as '@dtTemop', @dtTo as '@dtTo', @Hours as 'Hours', @Seconds as 'Seconds'

    --end

    select ' End', @dtFrom as '@dtFrom', @dtTo as '@dtTo', @Hours as 'Hours', @Seconds As 'Seconds'

  10. #10
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Originally posted by BMWM3LUV
    Paul,

    Thanks a million. I doubt I would have ever come up with the solution that you presented. I have tweeked it a little, and am posting the code change.

    Biggest Delta, is I removed the same day logic. This was making the assumption that an interaction starting and ending the same day was the duration of the interaction. This would have "dinged" the call center in three instances. Interaction came in before start of day and handled during day. If for some reason the day was a Holiday. Or, if the interaction was handled after business hours. (That time after close, but before the workers go home).

    Additionally, I converted the computations into Seconds.

    Finally, I handled when the interaction was handled with NO open business hours. I.E. Came in after close and handled before open.

    However, the logic you provided was invaluable.

    Thanks agian

    HERE'S THE CODE
    Glad to help! I have been told I am not quite right, maybe that is why I enjoy working this type of problem.
    Paul Young
    (Knowledge is power! Get some!)

  11. #11
    Join Date
    Mar 2004
    Posts
    7
    If it makes you sleep better, you are ALL RIGHT in my book. I searched/begged/and pleaded for this solution for months.

    Finally, my work told me I didn't have any more time to "research" this. So, I ultimately had to tell the customer that this report was going to cost them 12k.

    Now that I have the query, I can put together the front end and re-sell it! If I turn a profit, I will make sure to share the wealth.

    Mike

  12. #12
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Originally posted by BMWM3LUV
    If it makes you sleep better, you are ALL RIGHT in my book. I searched/begged/and pleaded for this solution for months.

    Finally, my work told me I didn't have any more time to "research" this. So, I ultimately had to tell the customer that this report was going to cost them 12k.

    Now that I have the query, I can put together the front end and re-sell it! If I turn a profit, I will make sure to share the wealth.

    Mike
    Cool, Do you own an M3 or lust after one? If you you do then you own me a ride! I am a Mercedes Benz guy but wouldn't pass up a ride in a bemer!
    Paul Young
    (Knowledge is power! Get some!)

  13. #13
    Join Date
    Mar 2004
    Posts
    7
    Own 98 M3 4-door. Incredible car. Joys of being a computer geek.

  14. #14
    Join Date
    Mar 2004
    Posts
    45
    How about just as select statement? Also independent of datefirst setting.

    Code:
    CREATE TABLE WorkHours(
    DayNum tinyint PRIMARY KEY CHECK(DayNum BETWEEN 1 AND 7),
    StartTime smalldatetime CHECK(StartTime BETWEEN 0 AND 1),
    EndTime smalldatetime CHECK(EndTime BETWEEN 0 AND 1),
    DayName char(9))
    
    insert WorkHours
    select 1, '8:00 AM', '5:00 PM', 'Monday'
    union all select 2, '10:00 AM', '7:00 PM', 'Tuesday'
    union all select 3, '8:00 AM', '5:00 PM', 'Wednesday'
    union all select 4, '8:00 AM', '5:00 PM', 'Thursday'
    union all select 5, '8:00 AM', '5:00 PM', 'Friday'
    union all select 6, '10:00 AM', '2:00 PM', 'Saturday'
    union all select 7, '10:00 AM', '12:00 PM', 'Sunday'
    
    CREATE TABLE Holidays(
    Holiday datetime PRIMARY KEY CHECK(Holiday = CONVERT(char(8),Holiday,112)),
    Descr char(12))
    
    insert Holidays
    select '20040101','New Years'
    union all select '20040119', 'ML King Day'
    union all select '20040216', 'President''s'
    union all select '20040411', 'Easter'
    union all select '20040531', 'Memorial'
    union all select '20040704', 'Independence'
    union all select '20040906', 'Labor'
    union all select '20041011', 'Columbus'
    union all select '20041111', 'Veteran''s'
    union all select '20041125', 'Thanksgiving'
    union all select '20041225', 'Christmas'
    
    CREATE PROC p_ProcessHours @Opened datetime, @Closed datetime, @Hours int OUTPUT AS
    IF @Opened > @Closed RETURN -1
    SELECT @Hours = --weeks between dates
    (SELECT (DATEDIFF(d,@Opened,@Closed)/7)*SUM(DATEDIFF(hh,StartTime,EndTime))
     FROM WorkHours)
    - --holidays
    (SELECT ISNULL(SUM(DATEDIFF(hh,StartTime,EndTime)),0)
     FROM Holidays h JOIN WorkHours w ON DATEDIFF(d,0,h.Holiday)%7+1 = DATEDIFF(d,0,DayNum)
     WHERE Holiday > @Opened AND Holiday < @Closed)
    + --hours of dates in same week
    (SELECT SUM(DATEDIFF(hh,StartTime,EndTime))
     FROM
     (SELECT DayNum, StartTime, EndTime
      FROM WorkHours
     UNION ALL
      SELECT DayNum+7,StartTime,EndTime
      FROM WorkHours)x
      WHERE DayNum BETWEEN DATEDIFF(d,0,@Opened)%7+1 AND DATEDIFF(d,0,@Closed)%7
       + CASE WHEN DATEDIFF(d,0,@Opened)%7 > DATEDIFF(d,0,@Closed)%7 THEN 8 ELSE 1 END)
    - --hours not in opened date
    ISNULL(
    (SELECT DATEDIFF(hh,StartTime,CASE WHEN CONVERT(varchar,@Opened,14) < EndTime THEN CONVERT(varchar,@Opened,14) ELSE EndTime END)
     FROM WorkHours
     WHERE DayNum = DATEDIFF(d,0,@Opened)%7+1 AND DATEDIFF(hh,StartTime,CONVERT(varchar,@Opened,14)) > 0
     AND NOT EXISTS
     (SELECT *
      FROM Holidays
      WHERE Holiday = CONVERT(char(8),@Opened,112))),0)
    - --hours not in closed date
    ISNULL(
    (SELECT DATEDIFF(hh,CASE WHEN CONVERT(varchar,@Closed,14) > StartTime THEN CONVERT(varchar,@Closed,14) ELSE StartTime END,EndTime)
     FROM WorkHours
     WHERE DayNum = DATEDIFF(d,0,@Closed)%7+1 AND DATEDIFF(hh,CONVERT(varchar,@Closed,14),EndTime) > 0
     AND NOT EXISTS
     (SELECT *
      FROM Holidays
      WHERE Holiday = CONVERT(char(8),@Closed,112))),0)
    Hans.

Posting Permissions

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