Query - Date Diff from different Records

[Guest]

I have a history table that has ID, Stage, and Date. I'm trying to figure out
how to calculate the date diff from records that are between one stage and
another. For example,
ECO# STAGE DATE
ECO 25 INITIAL 5/1/2006
ECO 25 IMPLEMENT 5/1/2006
ECO 25 INCOPORATE 5/3/2006
ECO 25 FINAL 5/5/2006
ECO 100 INITIAL 3/1/2006
ECO 100 IMPLEMENT 3/2/2006
ECO 100 INCOPORATE 3/15/2006
ECO 100 FINAL 3/16/2006

The end result would be ECO 25, 2 days and ECO 100, 13 days
Any suggestions?

Amytdev

 

[Guest]

How about something like:

SELECT A.ID, DateDiff("d",A.Date, B.Date)
From Mytable as A, Mytable AS B
Where A.ID = B.ID AND A.Stage = "INITIAL" AND B.Stage = "FINAL";

-Dorian

 

[Guest]

Thanks! That was exactly what I needed.

Amytdev

How about something like:

SELECT A.ID, DateDiff("d",A.Date, B.Date)
From Mytable as A, Mytable AS B
Where A.ID = B.ID AND A.Stage = "INITIAL" AND B.Stage = "FINAL";

-Dorian