Calculate usage without loops

[Onno]

Hi, I have this db-structure:

tblActionTypes
1 ..
2 ..
3 = BatchStart
4 = BatchEnd
5 = CaseStart
6 = CaseEnd
etc

tblUsage
[AutoIncrID] [ActionTime] [ActionType] [Name] [TransActionID]

In this tblUsage, apps from all over the plant log their usage data,
mainly start & stops of some different action type. The
[TransActionID] contains a generated GUID that is the same for 2 rows
that belong together. (i.e.a BatchStart and BatchEnd that belong
together have the same TransActionID. This way I can easily match
them.

The table can look something like this:
1 2008-09-03 14:33:11 3 MyBatch1 <guid-AA>
2 2008-09-03 14:35:11 4 MyBatch1 <guid-AA>
3 2008-09-03 14:35:12 5 MyCase1 <guid-BB>
4 2008-09-03 14:35:13 3 MyBatch2 <guid-CC>
5 2008-09-03 14:36:00 6 MyCase1 <guid-BB>
6 2008-09-03 14:39:12 4 MyBatch2 <guid-CC>
7 2008-09-03 16:00:00 3 MyBatch1 <guid-DD>
8 2008-09-03 16:00:11 4 MyBatch1 <guid-DD>

I want to create a report that contains an overview of the running
time of all distinct batches, i.e. the sum of all BatchEnd-BatchStart
ActionTimes for all distinct batches in a period. How can I do this
with any kind of decent performance? Given the above example, the
report looks like this:
[Name] [NrRuns] [Time]
MyBatch1 2 ...
MyBatch2 1 ...

I do this now via loops in VBA, but this is horribly slow. My current
code is something like this:

distinctBatches = DAO.RecordSet(SELECT DISTINCT NAME FROM tblUsage
WHERE [ActionType]=3
AND [ActionTime]
within date range)

While not distinctBatches.EOF
thisBatchExecutions = Recordset(SELECT [ActionTime], [ActionType],
[TransactionID] FROM tblUsage
WHERE
([ActionType]=3 OR [ActionType=4]) AND within date range
AND
[Name] = distinctBatches!Name)
Do While Not thisBatchExecutions.EOF
If thisBatchExecutions!ActionType = 3 Then ' On a start
endTime = DLookup("[ActionTime]", "tblUsage", _ ' Loopup end
time
"[ActionType]=4 AND within date
range
AND [TransactionID]
=thisBatchExecutions!transactionID)
if found, add endTimes, etc.
End if
Loop
Loop

I can’t figure out how to do this without the loops using SQL.
Can anyone offer some assistance?

TIA,
Onno

 

[KARL DEWEY]

Try this query --
SELECT tblUsage.Name, Count(tblUsage.Name) AS CountOfName,
Format(Sum([tblUsage_1].[ActionTime]-[tblUsage].[ActionTime]),"hh:nn:ss") AS
Batch_Time
FROM tblUsage INNER JOIN tblUsage AS tblUsage_1 ON tblUsage.TransActionID =
tblUsage_1.TransActionID
WHERE (((tblUsage.ActionType)=3) AND ((tblUsage_1.ActionType)=4))
GROUP BY tblUsage.Name;

 

[KARL DEWEY]

Or this --
SELECT tblUsage.Name, Count(tblUsage.Name) AS CountOfName,
Format(Sum([tblUsage_1].[ActionTime]-[tblUsage].[ActionTime]),"hh:nn:ss") AS
Batch_Time
FROM tblUsage INNER JOIN tblUsage AS tblUsage_1 ON tblUsage.TransActionID =
tblUsage_1.TransActionID
WHERE (((tblUsage.ActionType)=3) AND ((tblUsage_1.ActionType)=4)) OR
(((tblUsage.ActionType)=5) AND ((tblUsage_1.ActionType)=6))
GROUP BY tblUsage.Name;

 

[Onno]

Wow.. this is pure magic.... I bow for you. Many thanks! I am getting
results that differ a bit from my original implementation..will
investigate further.
I think it's because I've applied my datefilter at the wrong table.
Should it be on tblUsage or tblUsage_1?

Thanks again!
Onno

Try this query --
SELECT tblUsage.Name, Count(tblUsage.Name) AS CountOfName,
Format(Sum([tblUsage_1].[ActionTime]-[tblUsage].[ActionTime]),"hh:nn:ss")AS
Batch_Time
FROM tblUsage INNER JOIN tblUsage AS tblUsage_1 ON tblUsage.TransActionID=
tblUsage_1.TransActionID
WHERE (((tblUsage.ActionType)=3) AND ((tblUsage_1.ActionType)=4))
GROUP BY tblUsage.Name;

--
KARL DEWEY
Build a little - Test a little

Hi, I have this db-structure:

tblActionTypes
1 ..
2 ..
3 = BatchStart
4 = BatchEnd
5 = CaseStart
6 = CaseEnd
etc

tblUsage
[AutoIncrID] [ActionTime] [ActionType] [Name] [TransActionID]

In this tblUsage, apps from all over the plant log their usage data,
mainly start & stops of some different action type. The
[TransActionID] contains a generated GUID that is the same for 2 rows
that belong together. (i.e.a BatchStart and BatchEnd that belong
together have the same TransActionID. This way I can easily match
them.

The table can look something like this:
1 2008-09-03 14:33:11  3  MyBatch1 <guid-AA>
2 2008-09-03 14:35:11  4  MyBatch1 <guid-AA>
3 2008-09-03 14:35:12  5  MyCase1 <guid-BB>
4 2008-09-03 14:35:13  3  MyBatch2 <guid-CC>
5 2008-09-03 14:36:00  6  MyCase1 <guid-BB>
6 2008-09-03 14:39:12  4  MyBatch2 <guid-CC>
7 2008-09-03 16:00:00  3  MyBatch1 <guid-DD>
8 2008-09-03 16:00:11  4  MyBatch1 <guid-DD>

I want to create a report that contains an overview of the running
time of all distinct batches, i.e. the sum of all BatchEnd-BatchStart
ActionTimes for all distinct batches in a period. How can I do this
with any kind of decent performance? Given the above example, the
report looks like this:
[Name]       [NrRuns]    [Time]
MyBatch1   2               ...
MyBatch2   1               ...

I do this now via loops in VBA, but this is horribly slow. My current
code is something like this:

distinctBatches = DAO.RecordSet(SELECT DISTINCT NAME FROM tblUsage
WHERE [ActionType]=3
                                                 AND [ActionTime]
within date range)

While not distinctBatches.EOF
   thisBatchExecutions = Recordset(SELECT [ActionTime], [ActionType],
[TransactionID] FROM tblUsage
                                                    WHERE
([ActionType]=3 OR [ActionType=4]) AND  within date range
                                                                  AND
[Name] = distinctBatches!Name)
   Do While Not thisBatchExecutions.EOF
      If thisBatchExecutions!ActionType = 3 Then  ' On a start
         endTime = DLookup("[ActionTime]", "tblUsage", _  ' Loopup end
time
                                      "[ActionType]=4 AND within date
range
                                       AND [TransactionID]
=thisBatchExecutions!transactionID)
         if found, add endTimes, etc.
      End if
   Loop
Loop

I can’t figure out how to do this without the loops using SQL.
Can anyone offer some assistance?

TIA,
Onno

 

[KARL DEWEY]

Like this --
SELECT tblUsage.Name, Count(tblUsage.Name) AS CountOfName,
Format(Sum([tblUsage_1].[ActionTime]-[tblUsage].[ActionTime]),"hh:nn:ss") AS
Batch_Time
FROM tblUsage INNER JOIN tblUsage AS tblUsage_1 ON tblUsage.TransActionID =
tblUsage_1.TransActionID
WHERE (((tblUsage.ActionType)=3) AND ((tblUsage_1.ActionType)=4) AND
((tblUsage.ActionTime) Between [Enter start date] And [Enter end date])) OR
(((tblUsage.ActionType)=5) AND ((tblUsage_1.ActionType)=6) AND
((tblUsage.ActionTime) Between [Enter start date] And [Enter end date]))
GROUP BY tblUsage.Name;

--
KARL DEWEY
Build a little - Test a little

Wow.. this is pure magic.... I bow for you. Many thanks! I am getting
results that differ a bit from my original implementation..will
investigate further.
I think it's because I've applied my datefilter at the wrong table.
Should it be on tblUsage or tblUsage_1?

Thanks again!
Onno

Try this query --
SELECT tblUsage.Name, Count(tblUsage.Name) AS CountOfName,
Format(Sum([tblUsage_1].[ActionTime]-[tblUsage].[ActionTime]),"hh:nn:ss") AS
Batch_Time
FROM tblUsage INNER JOIN tblUsage AS tblUsage_1 ON tblUsage.TransActionID =
tblUsage_1.TransActionID
WHERE (((tblUsage.ActionType)=3) AND ((tblUsage_1.ActionType)=4))
GROUP BY tblUsage.Name;

--
KARL DEWEY
Build a little - Test a little

Hi, I have this db-structure:

tblActionTypes
1 ..
2 ..
3 = BatchStart
4 = BatchEnd
5 = CaseStart
6 = CaseEnd
etc

tblUsage
[AutoIncrID] [ActionTime] [ActionType] [Name] [TransActionID]

In this tblUsage, apps from all over the plant log their usage data,
mainly start & stops of some different action type. The
[TransActionID] contains a generated GUID that is the same for 2 rows
that belong together. (i.e.a BatchStart and BatchEnd that belong
together have the same TransActionID. This way I can easily match
them.

The table can look something like this:
1 2008-09-03 14:33:11 3 MyBatch1 <guid-AA>
2 2008-09-03 14:35:11 4 MyBatch1 <guid-AA>
3 2008-09-03 14:35:12 5 MyCase1 <guid-BB>
4 2008-09-03 14:35:13 3 MyBatch2 <guid-CC>
5 2008-09-03 14:36:00 6 MyCase1 <guid-BB>
6 2008-09-03 14:39:12 4 MyBatch2 <guid-CC>
7 2008-09-03 16:00:00 3 MyBatch1 <guid-DD>
8 2008-09-03 16:00:11 4 MyBatch1 <guid-DD>

I want to create a report that contains an overview of the running
time of all distinct batches, i.e. the sum of all BatchEnd-BatchStart
ActionTimes for all distinct batches in a period. How can I do this
with any kind of decent performance? Given the above example, the
report looks like this:
[Name] [NrRuns] [Time]
MyBatch1 2 ...
MyBatch2 1 ...

I do this now via loops in VBA, but this is horribly slow. My current
code is something like this:

distinctBatches = DAO.RecordSet(SELECT DISTINCT NAME FROM tblUsage
WHERE [ActionType]=3
AND [ActionTime]
within date range)

While not distinctBatches.EOF
thisBatchExecutions = Recordset(SELECT [ActionTime], [ActionType],
[TransactionID] FROM tblUsage
WHERE
([ActionType]=3 OR [ActionType=4]) AND within date range
AND
[Name] = distinctBatches!Name)
Do While Not thisBatchExecutions.EOF
If thisBatchExecutions!ActionType = 3 Then ' On a start
endTime = DLookup("[ActionTime]", "tblUsage", _ ' Loopup end
time
"[ActionType]=4 AND within date
range
AND [TransactionID]
=thisBatchExecutions!transactionID)
if found, add endTimes, etc.
End if
Loop
Loop

I can’t figure out how to do this without the loops using SQL.
Can anyone offer some assistance?

TIA,
Onno

 

[Onno]

Karl, many thanks. This works as a charm and is a zillion times faster
than my VBA approach.
One problem left: the time sum wraps when it's over 24 hours. I guess
I can fix that by using DataDiff and storing in seconds (did that in
my VBA solution and pretty-formatted when I print to an Exel report):

SELECT tblUsage.Name, Count(tblUsage.Name) AS CountOfName, Sum(DateDiff
("s", [tblUsage].[ActionTime], [tblUsage_1].[ActionTime])) AS
Batch_Time
FROM tblUsage INNER JOIN tblUsage AS tblUsage_1 ON
tblUsage.TransActionID=tblUsage_1.TransActionID
WHERE ((tblUsage.ActionType=3) AND (tblUsage_1.ActionType=4) AND
(tblUsage.ActionTime BETWEEN #1/1/2009# AND #1/31/2009 23:59:59#))
GROUP BY tblUsage.Name;

Regards,
Onno