Forespørgsel på forskel i samme kolonne

<<Produktionstiden for Emne1 er den tid, i sekunder, der er gået mellem Emne1 og Emne2's Sluttid, Porduktionstiden for Emne2 er så den tid der er gået mellem Emne2 og Emne3's Sluttid.>>

Dette skulle selvfølgelig være:

Produktionstiden for Emne2 er den tid, i sekunder, der er gået mellem Emne1 og Emne2's Sluttid, Porduktionstiden for Emne3 er så den tid der er gået mellem Emne2 og Emne3's Sluttid osv.

How many Emne do you have? Or maybe its juts the next record in the table which is used to make the calculation?

Looking at the example data it looks simple enough but I susspecty that ther are other tings which need to be taken into account

I have 19411 Emne (units) atm and the DB will grow =)

So I somehow have to compare Emne(n)'s Sluttid with Emne(n-1)'s Sluttid.
And the difference in seconds is my production time ProdTid for Emne(n).

That means I have to start at Emne2 since Emne1 has no prior Emne to compare to, but I'm willing to add this uncertainty to the resulting query. I later have to use the results in another query, but that is simple. My problem is this, to get the difference in seconds between the timestamps in the same column.

So I end up with a query that returns the time between this Emne and the one just before it.

Eks. of Tabel1
Name:  Endtime:
Emne22  07-03-2007 12:33:24
Emne23  07-03-2007 12:35:33
Emne24  07-03-2007 12:38:08  osv.

resulting in :

Name:  ProductionTime (in seconds)
Emne23    129
Emne24    155 
Emne25  and so on

So basically

SELECT Name, Difference between (EndTime(n) and EndTime(n-1)) As ProductionTime
FROM tabel1

DateDiff() seems to only be able to look at 2 fields in the same row. Which means I would have to have a StartTime in teh same Row to compare, which I dont.
I have to compare an entry with an entry in the previous row if sorted by time ofc. If not it has to compare with the entry just before timewise.

Well enough ranting, hope u get what I mean =)

Can you be sure that the Emne numbers are in sequence?

What I am trying to get at is, can we always assume that the difference in seconds is calcualted from the previous record?

Can you send me an example of your table  with data in?
ekspertenATsanthell.dk
AT = @

Without making a prior ORDER BY on the times, they are not in sequence. But Making an Order By query returning only Name and the time in ASCENDING order shouldnt be a problem. I allready did that. So it should be possible to assume they are in sequence when running a query on the ORDERED query.

I will mail u a scaled down version of the DB asap.

OK

:o)

and the SQl for those interested

SELECT (SELECT Max([DataFine]) FROM Generale L WHERE L.DataFine < G.DataFine) AS LastDataFine, G.DataFine AS ThisDataFine, DateDiff("s",[LastDataFine],[ThisDataFine]) AS ProductionTime
FROM Generale AS G
ORDER BY G.DataFine;

=)