Calculating the dwell time between two Records

[John Ortt]

Hi There,

I'm trying to work out the dwell between stages in our process.

We have a table (Workability) which records progress of parts through the
factory as follows:

PartID Stage Date
X1 1 01/01/05
X1 2 03/01/05
X1 3 10/01/05
X1 4 30/01/05
X2 1 12/02/05
Y1 1 14/03/05
etc

I would like to work out the dwell times inbetween each stage as a
cumulative and stage by stage figure, based on the PartID and working from
the first Date forwards
An example follows:

PartID Stage Date Dwell CDwell
X1 1 01/01/05 0 0
X1 2 03/01/05 2 2
X1 3 10/01/05 7 9
X1 4 30/01/05 20 29
X2 1 12/02/05 1 1
Y1 1 14/03/05 1 1
etc

I believe the best way to do this would be to use some sort of an embedded
query but I can't seem to addapt any of the ones I have.

If anyone can provide the SQL for this problem or point me in a direction
for help I would greatly appreciative,

Thanks,

John Ortt

[John Ortt]

Think I have done it......I used the following code:

SELECT a.PartID, a.Date, a.Stage,min(b.Date) As Start, max(b.Date) As Last
FROM tblWorkability as a INNER JOIN tblWorkability as b ON (a.date>b.date)
AND (a.PartID=b.PartID)
GROUP BY a.PartID, a.Stage, a.Date;

All I need to do now is subtact the first and last stages from the date...

Thanks anyway everyone and if anyone has any other ways of doing it feel
free to let me know.

Cheers,

John

"John Ortt" <jo******@noemailsuppliedasdontwantspam.com> wrote in message
news:43**********@glkas0286.greenlnk.net...

Hi There,

I'm trying to work out the dwell between stages in our process.

We have a table (Workability) which records progress of parts through the
factory as follows:

PartID Stage Date
X1 1 01/01/05
X1 2 03/01/05
X1 3 10/01/05
X1 4 30/01/05
X2 1 12/02/05
Y1 1 14/03/05
etc

I would like to work out the dwell times inbetween each stage as a
cumulative and stage by stage figure, based on the PartID and working from
the first Date forwards
An example follows:

PartID Stage Date Dwell CDwell
X1 1 01/01/05 0 0
X1 2 03/01/05 2 2
X1 3 10/01/05 7 9
X1 4 30/01/05 20 29
X2 1 12/02/05 1 1
Y1 1 14/03/05 1 1
etc

I believe the best way to do this would be to use some sort of an embedded
query but I can't seem to addapt any of the ones I have.

If anyone can provide the SQL for this problem or point me in a direction
for help I would greatly appreciative,

Thanks,

John Ortt