Questions

TSQL If/When issue

+
0 Votes
Locked

TSQL If/When issue

coolaid09
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) Thanks in advance.
  • +
    0 Votes
    Tony Hopkinson

    brackets round Else 0

    They are to indicate else is optional in the case statement documentaion.

    It's a bit naughty but I think I'd be looking at a cursor and a temporary table to do that lot.

    Or perhaps another table and some triggers.

    +
    0 Votes
    Shellbot

    Tony yer such a bad boy!!

    Ah sure, sometimes needs must.. I'm guilty of using a cursor and some temp tables here and there...

    +
    0 Votes
    coolaid09

    I changed [else 0] to else 0... still get 'Incorrect syntax near the keyword 'then'.' Thinking I need to find an alternate way to do this like you suggested. Don't have a ton of experience with temporary tables - this will be interesting.

    +
    0 Votes
    Tony Hopkinson

    only recomend them when your sql get's to teh unmaintainable and incomprehensible stage.

    Both will get round the structural difficulties

    Temporary tables break the job down in to bits, create a table(s) to hold imtermediate stages and then use them as input to teh next stage.

    The big advantage is yopu can see the intermediate results as you develop the code, usually various shortcuts suggest themselves as well. Not a good idea with huge amounts of data though, as the engine will be limited in terms of optimisation.

    The trigger manouver might be more suitable for you

    Basically you create a table to hold review date and end date. Then use trigger on your current table to in insert/updates of a review add / update a record. On end to find the right one and then update that.

    Don't go mad with triggers though, remember they are part of the original transaction.

    HtHs

    +
    0 Votes
    Shellbot

    would throwing an EXISTS into there work?

    maybe not applicable here, but thought of it last night..and don't have time to test it out for ya this morning.. just a thought...

    +
    0 Votes
    coolaid09

    Changed the bottom part of the query to:
    and exists
    (Select eventid from
    eventrecords
    where TaskName = 'End') then 1
    else 0 as bit
    end
    order by eventid desc

    Now it throws: 'Incorrect syntax near the keyword 'then'.'

    +
    0 Votes

    ok

    Shellbot

    hmm..

    it might need to go closer to your "when"..not sure..
    however, as i said, was just a thought, its actually been a while since used exists and can't rememebr the correct usage of it :)

    i maybe be around this weekend and can play around with it..
    but Tony's idea's are sound.
    I'd likely take a similar approach to him, break it down, use a very basic trigger..use a table...

  • +
    0 Votes
    Tony Hopkinson

    brackets round Else 0

    They are to indicate else is optional in the case statement documentaion.

    It's a bit naughty but I think I'd be looking at a cursor and a temporary table to do that lot.

    Or perhaps another table and some triggers.

    +
    0 Votes
    Shellbot

    Tony yer such a bad boy!!

    Ah sure, sometimes needs must.. I'm guilty of using a cursor and some temp tables here and there...

    +
    0 Votes
    coolaid09

    I changed [else 0] to else 0... still get 'Incorrect syntax near the keyword 'then'.' Thinking I need to find an alternate way to do this like you suggested. Don't have a ton of experience with temporary tables - this will be interesting.

    +
    0 Votes
    Tony Hopkinson

    only recomend them when your sql get's to teh unmaintainable and incomprehensible stage.

    Both will get round the structural difficulties

    Temporary tables break the job down in to bits, create a table(s) to hold imtermediate stages and then use them as input to teh next stage.

    The big advantage is yopu can see the intermediate results as you develop the code, usually various shortcuts suggest themselves as well. Not a good idea with huge amounts of data though, as the engine will be limited in terms of optimisation.

    The trigger manouver might be more suitable for you

    Basically you create a table to hold review date and end date. Then use trigger on your current table to in insert/updates of a review add / update a record. On end to find the right one and then update that.

    Don't go mad with triggers though, remember they are part of the original transaction.

    HtHs

    +
    0 Votes
    Shellbot

    would throwing an EXISTS into there work?

    maybe not applicable here, but thought of it last night..and don't have time to test it out for ya this morning.. just a thought...

    +
    0 Votes
    coolaid09

    Changed the bottom part of the query to:
    and exists
    (Select eventid from
    eventrecords
    where TaskName = 'End') then 1
    else 0 as bit
    end
    order by eventid desc

    Now it throws: 'Incorrect syntax near the keyword 'then'.'

    +
    0 Votes

    ok

    Shellbot

    hmm..

    it might need to go closer to your "when"..not sure..
    however, as i said, was just a thought, its actually been a while since used exists and can't rememebr the correct usage of it :)

    i maybe be around this weekend and can play around with it..
    but Tony's idea's are sound.
    I'd likely take a similar approach to him, break it down, use a very basic trigger..use a table...