Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Cincinnati, Ohio
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query for date range (2003)

    Hello. One of our support teams has an on-call rotation that transitions every Monday morning at 8:00AM. We have a list of who is on call for each week (resourceID, OnCallStartDate). I would like to create a query that returns who is on call based upon the current date and time. I'm not sure who to structure it so it returns the correct resourceID regardless of day/time of week. Any help would be appreciated. Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query for date range (2003)

    How did you intend to use this query? Run it periodically, once a day, every hour, or what? Do the start dates for the team members overlap, and is the period for a calendar week, a business week, or what? You would also need to give us a clue as to what table or tables you're querying to get the information.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Cincinnati, Ohio
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query for date range (2003)

    We would like to use the query to display the name of the resource who is on call at the time. We have an existing form that lists any on call incidents/open issues and it was asked if the active resource name could be displayed as "Currently on call:". This form is opened various time through every day as people check the status of open issues. The Monday 8:00AM start dates/times do not overlap and cover a calendar week. The queried table is tblOnCall containing OnCallID (int, autonumber), ResourceID (int), OnCallStartDate (date/time). I hope this helps. Would adding a second field (i.e. OnCallStopDate) make this more feasible? Thank you Charlotte!

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query for date range (2003)

    Try this:

    SELECT tblOnCall.ResourceID
    FROM tblOnCall
    WHERE (((tblOnCall.OnCallStartDate)<=Now()) AND ((tblOnCall.[OnCallStartDate]+7)>Now()));

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query for date range (2003)

    That might cause problems depending on how the startdate is stored and how the time value is handled, since Now includes the time. You might need to use Date instead to deal with only the date.
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query for date range (2003)

    If you have a record for each week for each person, then you don't need a stop date, but what do you do about illness, vacation, etc.? Do you simply change the start date or remove the record or what? I would be very careful of using times in checking availablity because if it is 7:30 AM, do you want to know who is ending their shift in half and hour or who will be available?
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Cincinnati, Ohio
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query for date range (2003)

    Charlotte, Pat,
    The sql worked. In the event of a change is needed (illness, vacation, etc.) we will modify the record. Kludgy but it will work for now. Thank you both very much for your help. -scott

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query for date range (2003)

    The reason i used Now and not Date is that he had defined OnCallStartDate (date/time) which i took to contain the time as well.

    If that is not required that a test with Date is appropriate as you suggested.

Posting Permissions

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