time difference

[Ielamrani]

I have this query with a field that contains time like this:
time (Field name)
8:35:10
9:10:15
9:05:15
10:10:15
etc...

I want to get the difference between 9:10:15 and 8:35:10 And 10:10:15 and 9:05:15.
In other words, the second line minus the first line and the fourth line minus the third line...etc...
Thanks in advance.
Ismail

 

[gol4]

something like this

SELECT DateDiff("n",(SELECT max([field]) AS Expr1
FROM [query] as a where a.[field] < [query].dateworked),[field]) AS Minutes
FROM [query];

 

[PHV]

By which unique field(s) is your query sorted ?
i.e., how do you know that 9:05:15 is on third line ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ielamrani]

Hi, this is what I did:

SELECT DateDiff("n",(SELECT max([Time]) AS Expr1
FROM [01TableProviderUpdates] as a where a.[Time] < [01TableProviderUpdates].dateworked),[Time]) AS Minutes
FROM 01TableProviderUpdates;

I am getting this message: 01TableProviderUpdates.dateworked

Can you help
Thanks
Ismail

 

[Ielamrani]

it's sorted by time (field)

 

[PHV]

it's sorted by time
Really ? So, why is 9:10:15 on second line and 9:05:15 on third line ?


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ielamrani]

you got me there. Yes my query is sorted by time as I said before but those times I put there I created them as an example...I did not pay attention to the sort when I typed them...

 

[PHV]

A starting point:
SELECT [time],
DateDiff('n', Nz(DMax('[time]','yourQuery','[time]<#' & [time] & '#'),[time]), [time]) AS Minutes
FROM yourQuery
ORDER BY 1;

Another starting point:
SELECT A.time As StartTime, Min(B.time) As EndTime,
Format(Min(B.time-A.time),'h:nn:ss') AS ElapsedTime
FROM yourQuery A, yourQuery B
WHERE A.time<B.time
GROUP BY A.time
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, missed the line numbers stuff:
SELECT A.time As StartTime, Min(B.time) As EndTime,
Format(Min(B.time-A.time),'h:nn:ss') AS ElapsedTime
FROM yourQuery A, yourQuery B
WHERE A.time<B.time
AND ((SELECT Count(*) FROM yourQuery WHERE [time]<A.time) Mod 2)=0
GROUP BY A.time
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gol4]

PHV is way better at this then me so follow His lead.
I do feel obligated to point out the problem with my post.
I pasted working code from a table and then changed it so you could understand it. the dateworked was my field so just change that to [time]

SELECT DateDiff("n",(SELECT max([Time]) AS Expr1
FROM [01TableProviderUpdates] as a where a.[Time] < [01TableProviderUpdates].[Time]),[Time]) AS Minutes
FROM 01TableProviderUpdates;

BTW should not name a field time and that is a reserved word in access

 

[Ielamrani]

You guys are great...I tried all the codes and they all worked...
Is there a way to make thes code calculate the whole field [Time]instead of minutes only I want to see the whole time difference (hour, minutes and second) using the following code:
SELECT DateDiff("n",(SELECT max([Time]) AS Expr1
FROM [01TableProviderUpdates] as a where a.[Time] < [01TableProviderUpdates].[Time]),[Time]) AS Minutes
FROM 01TableProviderUpdates;

Again thanks for all your help

 

[PHV]

Have you tried my suggestion posted 31 Aug 05 14:50 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ielamrani]

I just did and I am getting this error message: 'invalid SQL Statement, expected'delete', insert'...'
i am sorry I don't use sql that much..

this is what I did:

SELECT A.Time As StartTime, Min(B.Time) As EndTime,
Format(Min(B.Time-A.time),'h:nn:ss') AS ElapsedTime
FROM 01TableProviderUpdates A, 01TableProviderUpdates. B
WHERE A.Time<B.Time
AND ((SELECT Count(*) FROM [01TableProviderUpdates] WHERE [Time]<A.Time) Mod 2)=0
GROUP BY A.Time
ORDER BY 1;

 

[PHV]

SELECT A.time As StartTime, Min(B.time) As EndTime,
Format(Min(B.time-A.time),'h:nn:ss') AS ElapsedTime
FROM 01TableProviderUpdates A, 01TableProviderUpdates B
WHERE A.time<B.time
AND ((SELECT Count(*) FROM 01TableProviderUpdates WHERE [time]<A.time) Mod 2)=0
GROUP BY A.time
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ielamrani]

sorry to bother you again, now i am getting this message:
'data type mismatch in criteria expression'