Post reply

Need to write query to get Steps in custom job that have not completed

  • March 11, 2014 at 2:15 pm

    #305066

    I have the following:

    create table testdata

    (

    StepName varchar(25)

    ,StepStatus char(25)

    ,EntryDate datetime)

    insert testdata values ('Step1','Starting',getdate())

    insert testdata values ('Step2','Starting',getdate())

    insert testdata values ('Step3','Starting',getdate())

    insert testdata values ('Step4','Starting',getdate())

    insert testdata values ('Step1','Finished',getdate())

    insert testdata values ('Step2','Finished',getdate())

    insert testdata values ('Step3','Finished',getdate())

    I need a query that will basically return Step 4 hasn't finished.

    I have tried a few ways but can't get it to work...

    Any assistance would be great.

    Thanks

  • March 11, 2014 at 3:01 pm

    #1696157

    Got it. So simple when you step away from it for 30 minutes.

    Was making this so difficult:

    select td1.StepName

    from testdata td1

    where td1.stepname in (select stepname from testdata where stepstatus = 'Starting')

    and td1.stepname not in (select stepname from testdata where stepstatus = 'Finished')

    Woot.

  • March 11, 2014 at 6:58 pm

    #1696193

    You could do it that way but it might be faster to do it with one table scan instead of three:

    SELECT StepName

    FROM testdata

    GROUP BY StepName

    HAVING COUNT(CASE WHEN StepStatus = 'Starting' THEN 1 END) <> COUNT(CASE WHEN StepStatus = 'Finished' THEN 1 END);

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • March 11, 2014 at 7:23 pm

    #1696197

    Even better.

    Thanks!!

  • March 11, 2014 at 10:42 pm

    #1696210

    dwain, great effort.

    can you please help me how you got idea about that the table sacn is three.

  • March 11, 2014 at 10:50 pm

    #1696212

    Junglee_George (3/11/2014)

    dwain, great effort.

    can you please help me how you got idea about that the table sacn is three.

    That's known by the fact that the table name appears 3 times to the right of FROM in the OP's original query, coupled with knowing that there's no indexing on the sample tables provided.

    SSMS has a feature called "Include Actual Execution Plan" under the Query menu dropdown (also a button) that you can use to confirm it.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply