Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    11

    Unanswered: Santa wants a day off

    Right now, using MS SQL 2k, we use the following query to get "number of business days".

    case
    when DATEDIFF ( dd , dbo.table_case.creation_time , dbo.table_close_case.close_date )
    - DATEDIFF ( wk, dbo.table_case.creation_time , dbo.table_close_case.close_date )*2 <0 then 0
    else DATEDIFF ( dd , dbo.table_case.creation_time , dbo.table_close_case.close_date )
    - DATEDIFF ( wk, dbo.table_case.creation_time , dbo.table_close_case.close_date )*2
    end

    It doesn't really do anything except for subtract weekends. But instead of being happy with that, the Boss says "Ok, now pull out holidays as well". Any thoughts on how to approach that?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd say that this is not easy to implement worldwide, because holidays in your country are probably different than holidays in my country, which are again different from ... etc.

    The only way I see (until someone enlightens me) is to create a table which will store holiday dates and include this table into your query, such as

    ... AND date_column NOT IN (SELECT date_column FROM holiday_table)

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ive alwas found the problem with working day calendars is at what level you identify holidays - do you create a personal calendar, where it is different for each employee, or do you use an overall company calendar. do you care about personal holidays, or just national holidays and weekends

    A very effective calendar implementation I saw 20+ years ago worked at unit level. effectively it was a flat file which each month represented by 31 x 4 digit values, each value reprsenting that day working day. If it was a holiday or weekend it took the next working day value. it was used for a production control system. so to find when a part should be scheduled you read the 4 digit working day value from the calendar and did what you required

    eg
    required start date=required date-time to produce
    forecastcompletion date=timenow+time left on job + delays
    ....etc

    Becasue we were using flat files (No SQL) we a few other columns to ease locating the correct date using indexed lookups, and also a mechanism for storing a production week

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •