Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates in queries (A2K / SQL Server 2K)

    Hello All,
    I have a service metric that I am trying to put together. The measurement checks the difference between two dates in a table and outputs the number of hours to another field. What I need to due is exclude Saturday and Sunday from the calculation initially.

    Later, I need to also exclude holidays and then finally stop the measurement at 8pm on Friday night and start again at 8am on Mondays.

    Has anyone done this sort of thing before?

    Thanks,
    Mark

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates in queries (A2K / SQL Server 2K)

    Have a look at posts <!post=39610,39610>39610<!/post> and <!post=67566,67566>67566<!/post> and at this item on the Word MVP site.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in queries (A2K / SQL Server 2K)

    If you're up for a little VBA code writing, I'd suggest creating a function that uses the two dates as arguments. Use a loop to step from the first date to the last date one day at a time. For each date, determine the weekday (i.e. use the Weekday(dteDate) function that returns 1 for Sunday, ...7 for Saturday). If 2 through 6 (Mon-Fri), add 24 hours to your metric.

    For your Friday/Monday embellishment, check for 6 (Friday) separately and add 20 instead; check for 2 (Monday) separately and add 16 instead.

    To handle holidays, I usually set up a little 'Holidays' table that I load with dates I want excluded (each company/country is different, of course). I then have an IsHoliday(dteDate) boolean function that attempts to find a match between the dteDate argument and the values in the table. If a match is found, the function returns true. Once this function is available, it can be called for each date in the loop and the other logic (described above) can be applied only if the function returns false.

    Hopefully this provides a framework for a possible solution. If you need coding details, the links provided in the other response should give you some ideas. Otherwise, holler and I'm sure the loungers here will come to your rescue.

    By the way, do you want to treat any day after a holiday like a Monday and any day before a Holiday like a Friday? If so, that'll add a little more french pastry to the code.

    Hope this helps.

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in queries (A2K / SQL Server 2K)

    Thanks to Hans and Tom,
    I will try this stuff. It is important to note that I store both dates as long format and calculate the number of hours in between. I am not sure if this will throw a wrench in the works or not. As far as holidays go, I just want to exclude them, I don't want to do anything fancier then that right now.

    Thanks,
    Mark

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in queries (A2K / SQL Server 2K)

    Long dates should work okay as long as you're using the same algorithm for representing the date as an integer as Access uses (i.e., as long as lngYourDate = Date() when your date is today). Otherwise, I'd suggest adding/subtracting an appropriate offset (the difference between the two) at the beginning of the routine and work with a Date/Time variable (to make the Weekday function happy).

Posting Permissions

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