Query - Date Diff from different Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Thanks! That was exactly what I needed.

Amytdev

mscertified said:
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
Click to expand...
 
Back
Top