Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Alpine Califormia

    Unanswered: query for after hours

    is there a way to setup a query that will show data for after hours and weekends? How would I create a query that would show you how many hang-ups and voice messages happened after hours.

    hours of operation are 6:00 AM - 4:30 PM would have to do this on the [Date & Time Left] field. ( this isn't my database, I was just told to create some reports on it)

    SELECT [Message Tracking].[Date & Time Left], [Message Tracking].[Date Returned], [Message Tracking].[Customer Type]
     FROM [Message Tracking

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    As ever its down to the where and that is goi g to look something like
    where weekday(ddateandtineleft) in (1,7) OR hour(dateandtimeleft) < 6 or  hour(Dateandtimeleft) > 16 or (hour(dateandtimeleft) =16 and minute(dateandtimeleft) > 30)
    Effectively you have 4 terms
    any saturday or sunday (lookup the weekday function to check its correct)
    hours before 6
    hours after 16
    hours = 16 AND minutes after 30
    the bracketing around the last terms is critical

    If you think this is going to be needed in several places then push this code into a function which returna true or false and use apropriately

    An alternative approach to take account of holidays is to have a calendar table or other mechanism which identifies working and or non working days
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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