Re: How do I calculate times over multiple records?
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Wed, 11 Oct 2006 15:46:26 -0400
UNTESTED SQL follows.
SELECT [Order Number]
, DateDiff("n", [Status Date],
SUM((SELECT Min([Status Date]
FROM YourTable as T
WHERE T.[Order Number] = YourTable.[Order Number]
AND T.[Status Date] > YourTable.[Status Date]
AND T.Status = "In Process"))) as TotalMinutes
FROM YourTable
WHERE Status = "On Hold"
GROUP BY [Order Number]
Divide by 60 to get hours and fractions of hours.
Excluding Weekends and Holidays is more complex. At a minimum, you need a
Holidays table.
"Jim Moberg" <JimMoberg@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CE6F280B-4DFF-4E71-B44D-C648C1A7C171@xxxxxxxxxxxxxxxx
Hi,
I'm trying to write a query that will calculate two different things: 1)
Amount of time an order is on hold. 2) Amount of time an order was in
process during weekends and holidays.
The first one is tricky because order statuses are stored in a separate
log
table. Our data is stored something like this:
record number order number Status Status Date
1 abc123 In Process 2/2/2006 10:30 AM
2 abc123 On Hold 2/4/2006 10:30 AM
3 abc123 In Process 2/6/2006 9:30 AM
4 abc123 On Hold 2/7/2006 9:30 AM
5 abc123 In Process 2/8/2006 9:30 AM
So the total hold time would be 71 hours. How does one accomplish
calculating this in Access?
Then How does one calculate any time in process that may have been over a
holiday or weekend?
.
- Prev by Date: Re: Extract data from a field using a delimiter character
- Next by Date: Re: Group By Problem - show row even when count = 0
- Previous by thread: Using SUBSTRING in query
- Next by thread: Re: How do I calculate times over multiple records?
- Index(es):
Relevant Pages
|
