Post reply

Calculations based on Business Hours on weekdays

  • November 11, 2010 at 9:57 am

    #142617

    Hello All,

    I have a very peculiar problem. I have designed a report which calculates response time to a ticket.

    Example:

    Ticket Open: 9 am

    Ticket Closed: 4 pm

    Response Time = 7 hrs.

    I face a problem when:

    Ticket Open: 4 pm

    Ticket closed: 9 am next day

    Here it calculates all the hours passed between. However the response time should only be 2 hrs.

    How can i use the clock where it starts at 8 am and stops at 5 pm and is only from Monday thru Friday.

    Thanks,

    Abhijit Chaudhary

  • November 11, 2010 at 10:51 am

    #1249615

    chaudharyabhijit

    Without the table definition, sample data it is most difficult to attempt to answer your question. Please click on the first link in my signature block to read how to provide table definition, sample data in a manner such that those who want to assist you can give you a tested solution.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • November 11, 2010 at 10:54 am

    #1249616

    The best way to do this kind of this is a time table and a calendar table.

    The calendar table stores days, including which ones are business days. The time table stores valid hours.

    If you cross join those two, and then select how many rows are between the stop and start dates in another table, you get business hours.

    This solution can correct for things like standard lunch breaks, holidays as well as weekends, et al. It also performs well. It does require some upkeep, to make sure your holidays are current for any given year, but that's pretty easy to set up.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 11, 2010 at 11:11 am

    #1249623

    I was thinking about writing a function with exception, where i can set the time/day start and end.

    Any thoughts on this.

  • November 11, 2010 at 11:12 am

    #1249624

    Gsquared your suggestions sounds wonderful.

    But either way human intervention would be needed periodically to track holidays.

    i wish there was some way of eliminating that.

  • November 11, 2010 at 11:24 am

    #1249630

    Since holidays are subject to change, and in many cases are business-specific, yeah, it'll require human intervention at some level. However, just about every job I've ever had, the HR department issues a holiday list at the beginning of the year. Give them a tool to manage the holidays in the table, and you solve both of those things. Link that to the company calendar in Exchange/Sharepoint/whatever, and you have an automatic holiday calendar for every employee, which is a nice bonus to the whole thing.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply