Trying to put a single query together to be used eventually in a SQL Server 2005 report. I need to:
Pull in all distinct records for values in the "eventid" column for a time frame - this seems to work.
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.
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.
