Two things that come to mind without reading your query too deeply:
1) Avoid nested joins whenever possible such as "SELECT * FROM (nested join)". There's often no need for them and they *SERIOUSLY* limit a database query optimizer's ability to detect faster paths. In fact, most query optimizes will ignore nested joins completely.
According to your code, you have at least 4. Instead use an inner join.
2) Try putting the system through a profile or tuner that can detect places to build indices on. The most important part in database optimize is the tuning, not the query, although as previously discussed inner joins are preferable to nested.
You can also try adding the indices yourself, where you think they are needed. For example, you select 'max' alot in your code, therefore a tree index on this column would likely improve performance since the maximum would then always be available right away. [ December 05, 2005: Message edited by: Scott Selikoff ]