If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to query on differences between times
I have a table with fields called start and stop of type date/time. These
represent the start and stop times various scheduled tasks. How would I write an sql query to select all the records that are older than 2 days? How would I write an sql query to select the record oldest start time? How would I write an sql query to select the task with the longest run execution time where the execution time is the difference between the stop and the start time? Thanks, Siegfried |
#2
|
|||
|
|||
How to query on differences between times
Try these --
How would I write an sql query to select all the records that are older than 2 days? SELECT StartStop.Data, StartStop.Start FROM StartStop WHERE (((StartStop.Start)Date()-2)); How would I write an sql query to select the record oldest start time? SELECT Min(StartStop.Start) AS MinOfStart, First(StartStop.Data) AS FirstOfData FROM StartStop; How would I write an sql query to select the task with the longest run execution time where the execution time is the difference between the stop and the start time? SELECT Max([Stop]-[Start]) AS Expr1, First(StartStop.Data) AS FirstOfData FROM StartStop; "Siegfried Heintze" wrote: I have a table with fields called start and stop of type date/time. These represent the start and stop times various scheduled tasks. How would I write an sql query to select all the records that are older than 2 days? How would I write an sql query to select the record oldest start time? How would I write an sql query to select the task with the longest run execution time where the execution time is the difference between the stop and the start time? Thanks, Siegfried |
#3
|
|||
|
|||
How to query on differences between times
Hi, Siegfried.
This sounds suspiciously like a homework assignment, but there's a good chance that it might not be. How would I write an sql query to select all the records that are older than 2 days? If "older than two days" means "more than 48 hours," then use the following: SELECT * FROM tblTasks WHERE (Start Now() - 2); You can replace Now() with Date(), but that will allow records of up to, but not including 72 hours old to still be "less than two days old," so I'd recommend using Now(). How would I write an sql query to select the record oldest start time? Try: SELECT MIN(Start) AS OldestStart, Task AS OldestTask FROM tblTasks WHERE (Start = (SELECT MIN(Start) FROM tblTasks)) GROUP BY Task; You might be tempted to use FIRST(Task) to find the oldest task, but that will return the value in the task field for the first record in the table, which might not be the task in the same record as MIN(Start). HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "Siegfried Heintze" wrote in message ... I have a table with fields called start and stop of type date/time. These represent the start and stop times various scheduled tasks. How would I write an sql query to select all the records that are older than 2 days? How would I write an sql query to select the record oldest start time? How would I write an sql query to select the task with the longest run execution time where the execution time is the difference between the stop and the start time? Thanks, Siegfried |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Advanced SQL Query | Wes Henry | General Discussion | 3 | December 14th, 2005 09:55 PM |
SQL query showing diff between actual and budget | Bon | Running & Setting Up Queries | 3 | August 25th, 2005 12:07 PM |
query repeats first record multiple times | LarryWestMCSD | Running & Setting Up Queries | 0 | June 17th, 2005 12:05 AM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM |