Search through rows with specific values

  • Hello All,

    I've a table that stores operationcode for each jobnumber. The jobnumber can have multiple operationcode. From the below DDL, I need to show all the jobs that have operation codes as 2001 and 2002. In the below DDL Jobnumber 80011 has both the operation codes 2001 and 2002 so this job will display on the report.

    On the other hand Job 80021 only has operationcode 2001 and I do not want this job to show up on the report.

    I need to show all the operationcodes for a job if it has operationcode 2001 and 2002.

    How can I achieve this? Any help is appreciated

    Thanks

    USE tempdb;

    GO

    DECLARE @TEST_DATA TABLE

    (

    DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

    , OperationCodeVARCHAR(10) NOT NULL

    , EmployeeCode VARCHAR(10) NOT NULL

    , JobNumber VARCHAR(10) NOT NULL

    );

    INSERT INTO @TEST_DATA (OperationCode, EmployeeCode,JobNumber)

    VALUES

    ('1100', 'AUT', '80011' )

    ,('1180', 'AUT', '80011')

    ,('1910', 'AUT', '80011')

    ,('2002', 'AUT', '80011')

    ,('7261', 'AUT', '80011')

    ,('2001', 'AUT', '80011')

    ,('1100', 'AUT', '80021')

    ,('1180', 'AUT', '80021')

    ,('1910', 'AUT', '80021')

    ,('2001', 'AUT', '80021')

    ,('7261', 'AUT', '80021')

    ,('8001', 'AUT', '80021')

    ,('8002', 'AUT', '80021')

    ,('8003', 'AUT', '80021')

    ;

    SELECT

    J.DT_ID

    , OperationCode

    ,EmployeeCode

    ,JobNumber

    FROM @TEST_DATA J

    order by JobNumber

  • This is probably the most straightforward, albeit perhaps not the very most efficient:

    SELECT

    J.DT_ID

    ,J.OperationCode

    ,J.EmployeeCode

    ,J.JobNumber

    FROM @TEST_DATA J

    INNER JOIN (

    SELECT

    J2.JobNumber

    FROM @TEST_DATA J2

    WHERE J2.OperationCode IN ('2001', '2002')

    GROUP BY J2.JobNumber

    HAVING MAX(CASE WHEN J2.OperationCode = '2001' THEN 1 ELSE 0 END) = 1 AND

    MAX(CASE WHEN J2.OperationCode = '2002' THEN 1 ELSE 0 END) = 1

    ) AS O ON O.JobNumber = J.JobNumber

    ORDER BY JobNumber

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • GREAT job providing the setup.

    in this case, since you have to check for two values,, i think you want to join the table against itself.

    you can join the same table as two aliases.

    a bit faster way is to use an EXISTS,and a correlatation to the outer table to test the second condition:

    here's both examples:

    SELECT

    J.DT_ID

    , J.OperationCode

    ,J.EmployeeCode

    ,J.JobNumber

    FROM @TEST_DATA J

    INNER JOIN @TEST_DATA X

    ON J.JobNumber = X.JobNumber

    WHERE X.OperationCode = '2002'

    AND J.OperationCode = '2001'

    SELECT

    J.DT_ID

    , OperationCode

    ,EmployeeCode

    ,JobNumber

    FROM @TEST_DATA J

    WHERE EXISTS(SELECT * FROM @TEST_DATA X WHERE OperationCode = '2002' AND J.JobNumber = X.JobNumber)

    AND OperationCode = '2001'

    order by JobNumber

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • My understand was that all operation codes needed shown, not just the two being checked.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ScottPletcher & Lowell- Thank you very much for your help.

  • ScottPletcher,

    Thank you for your help before. I've to create a new report where I have to show all the jobs that DO NOT have OperationCode 2001 and 2002. Any help on this is greatly appreciated.

    Thanks

  • Any help is appreciated. Thanks again everyone.

  • Here's a simpler option to Scott's query that can be easily converted to the second report.

    SELECT

    J.DT_ID

    ,J.OperationCode

    ,J.EmployeeCode

    ,J.JobNumber

    FROM @TEST_DATA J

    WHERE J.JobNumber /*NOT*/ IN (

    SELECT J2.JobNumber

    FROM @TEST_DATA J2

    WHERE J2.OperationCode IN ('2001', '2002')

    GROUP BY J2.JobNumber

    HAVING MIN(J2.OperationCode) = '2001' AND

    MAX(J2.OperationCode) = '2002'

    )

    ORDER BY JobNumber

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thank you very much Luis.

  • Luis,

    The query you suggested works fine. I've a small issue with it. If there is Jobnumber that has OperationCode 2001 it still shows up. In the below DDL there is a jobnumber 80031 that has operationcode 2001 and it still shows up. I only want to show jobs that do not have either of the OperationCode 2001, 2002 or both.

    Thanks

    Here is the new DDL

    USE tempdb;

    GO

    DECLARE @TEST_DATA TABLE

    (

    DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

    , OperationCodeVARCHAR(10) NOT NULL

    , EmployeeCode VARCHAR(10) NOT NULL

    , JobNumber VARCHAR(10) NOT NULL

    );

    INSERT INTO @TEST_DATA (OperationCode, EmployeeCode,JobNumber)

    VALUES

    ('1100', 'AUT', '80011' )

    ,('1180', 'AUT', '80011')

    ,('1910', 'AUT', '80011')

    ,('2002', 'AUT', '80011')

    ,('7261', 'AUT', '80011')

    ,('2001', 'AUT', '80011')

    ,('1100', 'AUT', '80021')

    ,('1180', 'AUT', '80021')

    ,('1910', 'AUT', '80021')

    ,('2001', 'AUT', '80021')

    ,('7261', 'AUT', '80021')

    ,('8001', 'AUT', '80021')

    ,('8002', 'AUT', '80021')

    ,('8003', 'AUT', '80021')

    ,('1180', 'AUT', '80031')

    ,('1910', 'AUT', '80031')

    ,('2001', 'AUT', '80031')

    ,('7261', 'AUT', '80031')

    ,('8001', 'AUT', '80031')

    ,('8002', 'AUT', '80031')

    ,('8003', 'AUT', '80031')

    ;

    SELECT

    J.DT_ID

    ,J.OperationCode

    ,J.EmployeeCode

    ,J.JobNumber

    FROM @TEST_DATA J

    WHERE J.JobNumber NOT IN (

    SELECT J2.JobNumber

    FROM @TEST_DATA J2

    WHERE J2.OperationCode IN ('2001', '2002')

    GROUP BY J2.JobNumber

    HAVING MIN(J2.OperationCode) = '2001' AND

    MAX(J2.OperationCode) = '2002'

    )

    ORDER BY JobNumber

  • That's easier, just remove the GROUP BY (and HAVING) clause as that's only needed if you want to exclude the job numbers that have both operation codes.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thank you again Luis

Viewing 12 posts - 1 through 11 (of 11 total)

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