Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47

    Unanswered: Calculating Time in Hold

    I have a report based on a query (below) that reports the Average Time Spent in QA-Hold. Time is recorded in a table as Date and Time, and pulled out and seperated into hoursdays, hourshours, and hoursminutes, sumed for each record, than averaged.
    My problem is that I want to exclude weekends, and also possibly nights(say 1800 to 0500). How can I do this? Thanks, Keith

    SELECT [QC Log].[Part#], [QC Log].Quan, [QC Log].DateQC, [QC Log].[INSR#], [QC Log].[Accept/Reject], [QC Log].Vendor, [QC Log].DateReceived, [QC Log].TimeRecieved, [QC Log].TimeQC, 24*([DateQC]-[DateReceived]) AS hoursdays, Hour([TimeQC])-Hour([TimeRecieved]) AS hourshour, Minute([TimeQC])-Minute([TimeRecieved]) AS Expr2, [Expr2]/60 AS hoursminute, [hoursdays]+[hourshour]+[hoursminute] AS TotalTime
    FROM [QC Log]
    WHERE ((([QC Log].DateQC) Between [Forms]![DateRange].[Start] And [Forms]![DateRange].[End]))
    ORDER BY [QC Log].DateQC, [QC Log].[INSR#];

  2. #2
    Join Date
    Nov 2002
    Posts
    150

    Re: Calculating Time in Hold

    You can use the weekday function to eliminate weekends: WHERE SOMEDATE <> WEEKDAY(1) AND SOMEDATE <> WEEKDAY(7)

    For time range, have you tried using the hour function: WHERE HOUR(SOMETIME) < 18 AND HOUR(SOMETIME) > 5.

  3. #3
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47

    Re: Calculating Time in Hold

    Originally posted by FirstAndGoal4
    You can use the weekday function to eliminate weekends: WHERE SOMEDATE <> WEEKDAY(1) AND SOMEDATE <> WEEKDAY(7)

    For time range, have you tried using the hour function: WHERE HOUR(SOMETIME) < 18 AND HOUR(SOMETIME) > 5.
    Thanks, looks like this will filter records. To be more precise, what I wan't to do is eliminate weekends and nights in my calculation of time spent in the Hold. If a part comes in on Friday at 1200, and goes out on Monday at 1200, my query would return 72 hours. I want it to reurn 8 hours if I excluded nights from 1600 to 0800 -- or 24 hours if I excluded only Sat. and Sun. Could these functions somehow be used to accomplish this?

  4. #4
    Join Date
    Nov 2002
    Posts
    150

    Re: Calculating Time in Hold

    Originally posted by NightZen
    Thanks, looks like this will filter records. To be more precise, what I wan't to do is eliminate weekends and nights in my calculation of time spent in the Hold. If a part comes in on Friday at 1200, and goes out on Monday at 1200, my query would return 72 hours. I want it to reurn 8 hours if I excluded nights from 1600 to 0800 -- or 24 hours if I excluded only Sat. and Sun. Could these functions somehow be used to accomplish this?
    Ish. Don't think you can accomplish this with a simple query. You will probably have to write a function to figure all that out.

Posting Permissions

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