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?
LVL 6
ITHelper80Asked:
Who is Participating?
 
khdaniConnect With a Mentor Commented:
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

0
 
Nathan RileyConnect With a Mentor FounderCommented:
0
 
8080_DiverConnect With a Mentor Commented:
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. ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.