Trying to put a single query together to be used eventually in a SQL Server 2005 report. I need to:

1. Pull in all distinct records for values in the "eventid" column for a time frame - this seems to work.
2. For each eventid referenced above, I need to search for all instances of the same eventid to see if there is another record with TaskName like 'review1%'. Again, this seems to work.
3. This is where things get complicated: For each record where TaskName is like review1, I need to see if another record exists with the same eventid and where TaskName='End'. Utimately, I need a count of how many records have TaskName like 'review1%', and then how many have TaskName like 'review1%' AND TaskName='End'. I would think this could be accomplished by setting a new value for each record, and for the eventid, if a record exists with TaskName='End', set to 1, and if not, set to 0.

The query below seems to accomplish item #1 above:

SELECT eventid, TimeStamp, TaskName, filepath
FROM (SELECT eventid, TimeStamp, filepath, TaskName,
ROW_NUMBER() OVER(PARTITION BY eventid
ORDER BY TimeStamp DESC)
AS seq
FROM eventrecords where ((TimeStamp >= '2010-4-1 00:00:00.000') 
and (TimeStamp <= '2010-4-21 00:00:00.000'))) AS T
WHERE seq = 1 order by eventid

And the query below seems to accomplish #2:

SELECT eventid, TimeStamp, TaskName, filepath
FROM (SELECT eventid, TimeStamp, filepath, TaskName,
ROW_NUMBER() OVER(PARTITION BY eventid
ORDER BY TimeStamp DESC)
AS seq
FROM eventrecords where ((TimeStamp >= '2010-4-1 00:00:00.000') 
and (TimeStamp <= '2010-4-21 00:00:00.000')) and TaskName like 'Review1%') AS T
WHERE seq = 1 order by eventid

This will bring back the eventid's that also have a TaskName='End':

SELECT eventid, TimeStamp, TaskName, filepath
FROM (SELECT eventid, TimeStamp, filepath, TaskName,
ROW_NUMBER() OVER(PARTITION BY eventid
ORDER BY TimeStamp DESC)
AS seq
FROM eventrecords where ((TimeStamp >= '2010-4-1 00:00:00.000') 
and (TimeStamp <= '2010-4-21 00:00:00.000')) and TaskName like 'Review1%') AS T
WHERE seq = 1 
and eventid in 
(Select eventid from 
eventrecords 
where TaskName = 'End')
order by eventid

So I've tried the following to TRY to accomplish #3:

SELECT eventid, TimeStamp, TaskName, filepath
FROM (SELECT eventid, TimeStamp, filepath, TaskName,
ROW_NUMBER() OVER(PARTITION BY eventid
ORDER BY TimeStamp DESC)
AS seq
FROM eventrecords where ((TimeStamp >= '2010-4-1 00:00:00.000') 
and (TimeStamp <= '2010-4-21 00:00:00.000')) and TaskName like 'Review1%') AS T
WHERE seq = 1 
and 
case
when (eventid in 
(Select eventid from 
eventrecords 
where TaskName = 'End') then 1 
else 0) as bit
end
order by eventid

When I try to run this, I get: "Incorrect syntax near the keyword 'then'." Not sure what I'm doing wrong. Haven't seen any examples anywhere quite like this.

I should mention that eventrecords has a primary key, but it doesn't seem to help anything when I include it, and I am not permitted to change the table. (ugh) I've received one suggestion to use a cursor and temporary table, but am not sure how badley that would bog down performance when the report is running. Thanks in advance.