How to compute delta times and select on them?

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13997-how-compute-delta-times-select-them.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13997
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



*

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

Try
SELECT A.*, A.COMPLETEDTIME - A.STARTIME AS EXETIME FROM MyTable A
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13997
WHERE A.COMPLETEDTIME < Now() -1
AND A.COMPLETEDTIME > A.STARTIME +1/24
ORDER BY EXTEIME

barring any spello's

HTH

Pieter

"Siegfried Heintze" wrote in message
news:%23oNrSBPsGHA.3324@TK2MSFTNGP04.phx.gbl...
>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13997
> start time and the completed time)?
>
> Thanks,
> Siegfried
>

Try
SELECT A.*, A.COMPLETEDTIME - A.STARTIME AS EXETIME FROM MyTable A
WHERE A.COMPLETEDTIME < Now() -1
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13997
AND A.COMPLETEDTIME > A.STARTIME +1/24
ORDER BY EXTEIME
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13997

barring any spello's

HTH

Pieter

"Siegfried Heintze" wrote in message
news:%23oNrSBPsGHA.3324@TK2MSFTNGP04.phx.gbl...
>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
>



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4367 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

Siegfried

Let Access write it for you.

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13997
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:

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

"Siegfried Heintze" wrote in message
news:%23oNrSBPsGHA.3324@TK2MSFTNGP04.phx.gbl...
>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13997
> 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
>