SQL Server 2005 - query with case statement

Question

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.

Answer 1

It's not necessary to convert the result from IN to an integer. You can just do this:

and eventid in (
    Select eventid from 
    eventrecords
    where TaskName = 'End'
)

Answer 2

Try adding another parenthesis after 'End'), like 'End')) and removing the parenthesis after the 0, as else 0 as bit end

Answer 3

If I understand you question correctly you'll be able to this with a subquery surrounded by a CASE. The subquery checks if there are one (or more) records within the same period and with the same eventid that has a TaskName='End'. Then, if such row exists, the CASE clause returns 1 and if not 0.

SELECT eventid,
       TimeStamp,
       TaskName,
       filepath,
       CASE WHEN EXISTS (SELECT 1 FROM eventrecords WHERE TaskName = 'End' and TimeStamp BETWEEN '2010-04-01' AND '2010-04-21' and eventid = T.eventid) THEN 1 ELSE 0 END as TaskNameEndExists
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