Calculate Average Number of Days without Weekends or Holidays

Posted on 2009-04-16
Last Modified: 2012-05-06
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?
Question by:ITHelper80
    LVL 3

    Accepted Solution

    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

    LVL 11

    Assisted Solution

    by:N R
    LVL 22

    Assisted Solution

    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. ;-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
    A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now