How to compute delta times and select on them?

[Siegfried Heintze]

I have a table with columns of type Date/Time labled dtStart and
dtCompleted.

How would I write a SELECT statement that would select all the rows that are
older than 24 hours (based on the completed time) and have an execution time
greater than 1 hour and order by the longest execution time (where execution
time is computed by taking the difference between the start time and the
completed time)?

Thanks,
Siegfried

 

[Pieter Wijnen]

Try
SELECT A.*, A.COMPLETEDTIME - A.STARTIME AS EXETIME FROM MyTable A
WHERE A.COMPLETEDTIME < Now() -1
AND A.COMPLETEDTIME > A.STARTIME +1/24
ORDER BY EXTEIME

barring any spello's

HTH

Pieter

 

[Pieter Wijnen]

Try
SELECT A.*, A.COMPLETEDTIME - A.STARTIME AS EXETIME FROM MyTable A
WHERE A.COMPLETEDTIME < Now() -1
AND A.COMPLETEDTIME > A.STARTIME +1/24
ORDER BY EXTEIME

barring any spello's

HTH

Pieter

I have a table with columns of type Date/Time labled dtStart and
dtCompleted.

How would I write a SELECT statement that would select all the rows that
are older than 24 hours (based on the completed time) and have an
execution time greater than 1 hour and order by the longest execution time
(where execution time is computed by taking the difference between the
start time and the completed time)?

Thanks,
Siegfried

--

 

[Jeff Boyce]

Siegfried

Let Access write it for you.

Open a new query in design mode. Add the table that contains these
fields/columns.

Add the dtCompleted field to the "grid". In the criterion row below
dtCompleted, add a selection criterion something like:
<Now()-1

Add a calculated field to the grid, something like:
ExecutionTime: DateDiff("h",[dtStart],[dtCompleted])
and in the criterion row, use:

1

(You might want to take the difference in minutes and use a criterion >60).

Regards

Jeff Boyce
Microsoft Office/Access MVP