Subtracting time between dates in two different records

[GMACDEE]

I would like to be able to subtract time between to records and from two different fields. I have three fields in a query, Equipment ID, Start Time & End Time are the fields. I would like to subtract the time between the first End Time and the next Start Time. This would have to be against the Equipment ID. Thanks for any help.


Example

Equipmnet Desription	Start Time	End Time	Time Diff
Machine #1	14/2/2007 9:00 AM	14/2/2007 9:30 AM
Machine #1	20/2/2007 8:45 AM	20/2/2007 9:30 AM	143:15:00
Machine #1	19/3/2007 7:45 AM	19/3/2007 8:30 AM	646:15:00
Machine #1	6/2/2008 9:00 AM	6/2/2008 11:30 AM	7776:30:00

[NoellaG]

Hi,

with the function datediff you can calculate the interval between two dates. If you choose 's' as interval you get the number of seconds between datetime1 and datetime2: for example = datediff"s", endtime, begintime)

[CJ_London]

the time format is limited to 24 hours, so you will need to find a way of displaying as you require (your last record will not display correctly since it is greater than 1440 minutes) - there are plenty of examples how to do this - no time to find one myself, so google it. But with regards your basic query

SELECT *, starttime-(SELECT Max(endtime) from myTable as T where Equipment=myTable.equipment and endtime<=myTable.starttime) as downtime
FROM myTable
ORDER BY StartTime