CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Turtleman10
Turtleman10

Reputation: 119

Using the same column with two different parameters SQL server

I'm sorry if I'm not asking this correctly. I need to write a query that goes against a log. in the log there is a column note that tracks the success of each step in a procedure. what I need is if a job order "fjobno" has WCutSuccess... in the note field within a date range and that same job order "fjobno" has ASSYSuccess... in another date range I want to list it. I just can't seem to figure it out. I'm sure its simple. and I'm sure people do it all the time. If some one could point me in the right direction I would appreciate it.

select 
    inm.fgroup, inm.fpartno, jod.fjobno, sqb.note
from 
    inmast as inm
left join 
    jodbom as jod on inm.fpartno = jod.fbompart
left join 
    sqbclog as sqb on jod.fjobno = sqb.job
where 
    (inm.fgroup = 'isl' or inm.fgroup = 'iss')
    and 
    (sqb.note = 'WCutSuccess...'
     and sqb.stamp between '09/01/2014' and '10/01/2014')
    and 
    (sqb.note = 'ASSYSuccess...'
     and sqb.stamp between '10/01/2014' and '10/09/2014')

Upvotes: 0

Views: 49

Answers (1)

Lamak
Lamak

Reputation: 70648

I changed your query a little so it uses IN instead of col = something or col = somethingelse.

I also changed the way you are choosing ranges in dates.

SELECT  inm.fgroup,
        inm.fpartno,
        jod.fjobno,
        sqb.note
FROM inmast AS inm
LEFT JOIN jodbom AS jod
    ON inm.fpartno = jod.fbompart
LEFT JOIN sqbclog AS sqb
    ON jod.fjobno = sqb.job
WHERE inm.fgroup IN ('isl','iss')
AND (
    (   sqb.note = 'WCutSuccess...'
        AND sqb.stamp >= '20140109' 
        AND sqb.stamp < '20140111')
    OR( sqb.note = 'ASSYSuccess...'
        AND sqb.stamp >= '20140110'
        AND sqb.stamp < '20140911')
    )

Upvotes: 1

Related Questions