Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page