locked
Working days of the month RRS feed

  • Question

  • Hi there,

    I am just wondering how do we find out working days of the month? it is easy to exclude sat and sunday but the problem is Bank holiday. I am in UK so it is only 8 bank holiday in particular month. Is there any logic to do it?

    I can think of creating one table with entry of bank holiday and exclude them. any other suggestion?

    Thanks,
    Vijay
    Wednesday, August 5, 2009 11:33 AM

Answers

All replies

  • That is a fair way of doing it.  You might want to give a look at a write-up on a calendar table here:

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

    Here are some other posts that might be helpful / related:


       http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/96d88e83-4e1f-491b-8d93-93352b424a95/
       http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/347f1548-5f5a-49b4-9dd6-4ed6d97dcfd9/
       http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/f1fb9f74-94e0-4654-8a8a-4d78d992fe50/
       http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/2ac7e603-a8e8-4609-8219-8a61ecd455c9/
          Umachandar Jayachandran

       http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1349.entry
          Louis Davidson's Blog for loading a calendar table

       http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/f3486b39-24b0-43cc-ab9c-328e0886f3e4/
          Louis Davidson
          Kent Waldrop

       http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/16a4b0a1-370d-4b82-8a4d-75cefbe933e8/
          Louis Davidson

       Examples:

          http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/9e9ee8e0-815a-43b1-ad92-d289f884df19/
             Umachandar Jayachandran
    Wednesday, August 5, 2009 11:37 AM
  • Use an auxiliary table.

    Wednesday, August 5, 2009 11:38 AM
  • Yes it is good idea to create bankholiday table and save daynumber in that table.

    but you have fix days as bank holiday then you might use query to exclude those


    Shamas Saeed MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    Wednesday, August 5, 2009 11:39 AM
  • Hi Vijay

    A Holiday table is a very common way of dealing with this requirement, particularly where you have an international element to your data. ie if you need to figure out if it's a holiday in the UK or US etc.

    A number table is also convenient for calculating the number of working days in a date range.

    Itzik Ben-Gan recommends the Richard T. Snodgrass book Developing Time-Oriented Database Applications in SQL, available in pdf at http://www.cs.arizona.edu/~rts/publications.html 

    HTH

    Ewan


    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, August 5, 2009 11:44 AM
  • http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

    this link is useful but all bank holiday is for USA...is there something availble for UK dates?
    Wednesday, August 5, 2009 11:56 AM
  • Yes, you can get the UK holiday dates from the following resources and populate your calendar table:
    http://en.wikipedia.org/wiki/Public_holidays_in_the_United_Kingdom
    http://www.woodlands-junior.kent.sch.uk/customs/questions/bankholidays.html
    Plamen Ratchev
    • Marked as answer by Zongqing Li Wednesday, August 12, 2009 6:33 AM
    Wednesday, August 5, 2009 12:50 PM