Cumulative DATEDIFFs and JOINs

I have a table to track when a project has reached a certain state, structu=
red like so:

id
projectId
timeStamp
state

Say:
Project1 on 2007-09-01 is at state "started"
Project2 on 2007-10-01 is at state "started"
Project3 on 2007-10-15 is at state "started"
Project1 on 2007-10-20 is at state "completed"
Project2 on 2007-10-25 is at state "completed"

Is there a single query to find the duration of the completed projects when=
the started date and the completed date are between a specific date range?

For starters, I was thinking of LEFT JOINing on the projectId (since I need=
dates from the same project), to get the start project date and the end pr=
oject date.
But how do I link them?
Do I also need sub queries?

SELECT DATEDIFF(end.timeStamp, start.timeStamp) AS Duration
FROM MyTable AS `start`
LEFT JOIN MyTable AS `end`
ON start.projectId =3D end.projectId {AND start.state =3D 'started' AND end=
..state =3D 'completed' ??? This may actually work LOL -- I don't have data =
yet and it started making sense when I began to compose this question.}
WHERE start.timeStamp BETWEEN '2007-01' AND '2007-10'
AND end.timeStamp BETWEEN '2007-01' AND '2007-10'
ORDER BY start.timeStamp ASC

And what if there are entries where the project is temporarily "stopped"?
Project2 on 2007-10-05 is at state "stopped"
Project2 on 2007-10-07 is at state "started" (resumed)
Project3 on 2007-10-18 is at state "stopped" (and never resumed)

I'm using MySQL 4.1.
____________________________________________________________ _____
Boo!=A0Scare away worms, viruses and so much more! Try Windows Live OneCare=
!
http://onecare.live.com/standard/en-us/purchase/trial.aspx?s _cid=3Dwl_hotma=
ilnews=

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php