Cumulative DATEDIFFs and JOINs

I have a table to track when a project has reached a certain state, structured 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 project 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 = end.projectId {AND start.state = 'started' AND end..state = '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! Scare away worms, viruses and so much more! Try Windows Live OneCare! http://onecare.live.com/standard/en-us/purchase/trial.aspx?s_cid=wl_hotmailnews