Re: How do I calculate times over multiple records?
- From: Jim Moberg <JimMoberg@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 12 Oct 2006 13:41:02 -0700
Well... I got the code to run but not with the SUM in place. Also, it runs
with no end in sight and I have to kill Access manually. With the SUM in
place Access is telling me I didn't include the datediff function as part of
an aggregate function.
If you have any more thoughts feel free to let me know.
"John Spencer" wrote:
I goofed. The sum statement was in the wrong place. I should have been.
doing a sum on the DateDiff Calculation. I hope I got this version correct.
If I still am having a problem, try breaking this down into steps. That is
drop the aggregation and drop the datediff and see if you can get just the
sub-query to run correctly.
(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 DateNeededForCalc
Once that is working, then try adding in the datediff, and then add in the
sum.
This is often the way I build complex queries. It takes a bit more work,
but I often get to the end solution quicker and with more confidence in the
accuracy of my solution. Just 'cause it works, don't make it right.
SELECT [Order Number]
, SUM (DateDiff("n", [Status Date],
(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]
The subquery is a coordinated subquery (it uses information from the main
query) and therefore runs once for each row returned by the outer query.
"Jim Moberg" <JimMoberg@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F0666D98-125E-4E47-AD0D-7F78AA355C5C@xxxxxxxxxxxxxxxx
One other question. a sub query usually gets executed first but in this
case
does it get executed second to the outer query since it's in the datediff
function?
"John Spencer" wrote:
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?
- Follow-Ups:
- Re: How do I calculate times over multiple records?
- From: John Spencer
- Re: How do I calculate times over multiple records?
- References:
- Re: How do I calculate times over multiple records?
- From: John Spencer
- Re: How do I calculate times over multiple records?
- From: John Spencer
- Re: How do I calculate times over multiple records?
- Prev by Date: Re: Duplicates numbered
- Next by Date: Re: Combining 84 tables with one query...help!
- Previous by thread: Re: How do I calculate times over multiple records?
- Next by thread: Re: How do I calculate times over multiple records?
- Index(es):
Relevant Pages
|
