DateDiff on the fly? and perhaps cumulative? 1

[Fherrera]

Hi, i'm wondering if there exists SQL code to determine the date difference between successive rows. For example, a query I have would return something like this: (where VAL > 500 but notice the GAP that says there are 14 entries that were not included ie, VAL <= 500) If I want to determine the amount of time that VAL was > 50, i'd take the date difference from ID 81 and 80 and 96 and 95 (out of simplicity I won't count any time within the gap)

ID NAME VAL DATE
80 T5212057 502 1/8/94 18:22
81 T5212057 506 1/8/94 18:25
> gap <
95 T5212057 501 1/8/94 19:43
96 T5212057 504 1/8/94 19:47

I understand there is a datediff() function that can do this, but is it possible to combine this with sql to do it on the fly (when no gap exists) and then sum the total up... Or even, just do the datediff in hours on the fly then I can sum them up later. If not, I can do this in excel, i'm just trying to save a step

Thanks.

Frank

 

[PHV]

Something like this ?
SELECT A.ID,A.NAME,A.VAL,A.DATE,Format(A.DATE-Nz(B.DATE,A.DATE),"hh:nn")
FROM theQuery A LEFT JOIN theQuery B ON A.ID=B.ID+1

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

 

[Fherrera]

Thanks for the reply PHV, looks good, I think I understand it

Only problem is when I try to run this:

SELECT A.ID, A.NAME, A.VAL, A.DATE, Format(A.DATE-Nz(B.DATE,A.DATE),"hh:nn")
FROM FRANK AS A LEFT JOIN FRANK AS B ON A.ID=B.ID+1;

Yes I have a creative table name (it's my test table) Access asks me to enter a value in for A.NAME... Does this only work for when using queries (ie. thisQuery) as opposed to an actual table (ie. Frank the table)

Frank

 

[PHV]

Does the table Frank contains a column named NAME ?

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

 

[Fherrera]

Crikey I was gonna write back and say that it works if I input any value as Name ..-cough- and umm ..yes it works now

Sorry about that. Thanks PHV