• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 863
  • Last Modified:

Calculate Average Number of Days without Weekends or Holidays

I have a ticket database that has a startdate column and a completedate column.
Im looking for a way to calculate the time between the two dates without weekends and holidays. (I have a seperate table with the current holidays)

Is is also possible to sum all the averages for all tickets?
3 Solutions
more detailed scheme of those tables will help answer more correctly,
but i guess the query would be something like:

select dateadd(d, (select dateadd(d,startdate,-completedate)), -holidays.offs)
from ticket, 
(select count(daysoff) as offs from holidays where daysoff > startdate and daysoff < completedate) as holidays
where startdate=value1 and completedate=value2

Open in new window

Nathan RileyFounder/CTOCommented:
This is one of those times when having a Calendar table comesin handy.  
You can create a table (Calendar) that has a list of the dates you are concerned with and then a select set of columns for things like Quarter, WorkDay, Holiday, Weekend, PayPeriod, etc., that you fill appropriately.  
For the Quarter, I would put the fiscal quarter that the date is in (some fiscal years start on September 1, some on January 1, some on July 1 ;-).  
For the PayPeriod, the answer should be obvious.  
For the WorkDay, Holiday, and Weekend entries, I would use a 1 for "YES" and a 0 for "NO".  That way, you can just sum the appropriate column after joining to the Calendar table using a BETEWEN on the dates.  (This gives all kinds of flexibilities to handling other tasks.  For instance, it can be used in finding the next Business day that is 15 days from a given date. ;-)

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now