#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    2
    Rep Power
    0

    Calculating working days between 2 dates


    Hi,

    I found the following to return number of mon-fri days between 2 dates but need help to amend it so that it takes in to account public holidays from the Holiday table.



    SELECT
    top 100
    application_id,
    application_date as "Start date",
    status_date as "End Date",
    (DATEDIFF(dd, application_date, status_date) + 1) -(DATEDIFF(wk, application_date, status_date) * 2) -(
    CASE WHEN DATENAME(dw, status_date) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, status_date) = 'Saturday' THEN 1 ELSE 0 END) as "Difference"
    from applications

    Holiday table (holiday_date):

    2007-12-26 00:00:00.000
    2008-01-01 00:00:00.000
    2008-03-21 00:00:00.000
    2008-03-24 00:00:00.000



    Thanks in advance

    Vijay
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    by far the easiest solution is to change your holidays table into a calendar table, which has one row per date

    an additional column which indicates whether the date is a weekday will come in very handy

    and of course a column for the date being a holiday
    Code:
    thedate      wkday   holiday          
    2008-03-16     1        0
    2008-03-17     2        1
    2008-03-18     3        0
    2008-03-19     4        0
    2008-03-20     5        0
    2008-03-21     6        1
    2008-03-22     7        0
    2008-03-23     1        0
    2008-03-24     2        1
    2008-03-25     3        0
    now your query is really easy --
    Code:
    SELECT TOP 100
           application_id
         , application_date  AS "Start date"
         , status_date       AS "End Date"
         , COUNT(*) - 1      AS "Business Days"
      FROM applications
    INNER
      JOIN calendar
        ON calendar.thedate BETWEEN application_date AND status_date
       AND calendar.wkday BETWEEN 2 and 5
       AND calendar.holiday = 0
    GROUP
        BY application_id
         , application_date 
         , status_date  
    ORDER
        BY "Business Days" DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    2
    Rep Power
    0
    Thanks very much r937.

    The only issue is that the connection is read-only and won't be able to create our own user tables.

    Is the datediff / dateadd code usable? I appreciate it might not be the best solution!

    Thanks again,

    Vijay
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by v1j4y
    Is the datediff / dateadd code usable?
    only if you can subtract out the holidays which occur between those dates

    think about using COUNT(*) in a subquery, and subtracting that from your initial calculation

    as for your read-only connection, what happens when the holiday table is out of date? whom do you tell?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo