Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How to compute delta times and select on them?

Discussion in 'Information Technology' started by Siegfried Heintze, Jul 28, 2006.

  1. Siegfried Heintze

    Siegfried Heintze
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Computers Information Technology Nov 13, 2011
    SciTech Supercomputer Predicts Revolution Breaking News Sep 11, 2011
    Dr. Strangestlove (Or How I learned To Stop Worrying And Love My Computer) Inspirational Stories May 22, 2011
    SciTech Digging Deeper, Seeing Farther: Supercomputers Alter Science Breaking News Apr 27, 2011
    SciTech Scientists Create World's Smallest Computer Breaking News Feb 26, 2011

  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    "Siegfried Heintze" <siegfried@heintze.com> 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
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    "Siegfried Heintze" <siegfried@heintze.com> 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
     
  5. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    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

    "Siegfried Heintze" <siegfried@heintze.com> 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
    >
     

Share This Page