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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
