How do I calculate times over multiple records?

[Guest]

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?

 

[John Spencer]

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.

 

[Guest]

Thanks. I have not been able to get this working in Access. I do have one
question. Why are we summing the minimum value of the status date?

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.

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?

 

[Guest]

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?

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.

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?

 

[John Spencer]

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.

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?

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.

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?

 

[Guest]

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.

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.

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?

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.

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?

 

[John Spencer]

The unfortunate thing is that queries with coordinated subqueries are often
slow, especially if you are running them for a large number of records.

I would be sure I had indexes on Order Number, Status Date, and Status
fields. Beyond that I would try to limit the number of records returned by
the main query by using the where clause.

How many records are there in your table?

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.

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.

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?

:

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.

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?

 

[Guest]

There is an index on the table. Currently there are 9,758,144 records in the
table. My plan was to use another query off of another table to narrow down
the number of records.

I have a query set up that runs off of the order table. The order table is
linked to the order status log table (The table I need to do the hold time
calculation on). By doing this I have a subset of records to read through
from the order status log table and do the hold time calculation.

To try out the code sample you so graciously provided to me a couple days
ago I used one order number in particular and the query still didn't perform
well. I feel like I'm close and the answer is in front of me but I just
can't see it.

The unfortunate thing is that queries with coordinated subqueries are often
slow, especially if you are running them for a large number of records.

I would be sure I had indexes on Order Number, Status Date, and Status
fields. Beyond that I would try to limit the number of records returned by
the main query by using the where clause.

How many records are there in your table?

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.

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.


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?

:

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.

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?