SQL Server Query for marking multiple rows instead of one

Question

Having issues with formatting this question after adding new information. I am new to this forum and still trying to fix the formatting of the below post.

Would like to have a new varible to identify the bad lot numbers.

Sample Table PRODUCE

Lot    Fruit   Status
1245   Apple   pass
1245   Pear     pass
1245   Lemon    safe
1245   Orange   reject
5542   Pear      pass
5542   Apple    safe
9855   Apple    reject

The query I want would return the following data showing a new variable that identifies every occurrence of the affected lot number.

Lot      Fruit     Status  Flag
1245   Apple   pass      1
1245   Pear     pass      1
1245   Lemon  safe      1  
1245   Orange reject    1
5542   Pear      pass     0
5542   Apple    safe      0
9855   Apple    reject    1

Case statement would work if there was one lot number but we have multiple lots in the same table. We dont want to screen them out as we many more field names and I am only showing the first three columns.

New Information: I liked the elegance of the "Partition Answer" and want to know if this could be done.

In my code I have to find the earliest and latest date for a given lot. I currently do this with a group by where I use min and max functions to get the answer where I then left join it back.

Lot    Fruit     Status  Date
1245   Apple     pass    12OCT16
1245   Pear      pass    11OCT16
1245   Lemon     safe    23OCT16
1245   Orange    reject  12OCT16
5542   Pear      pass    23SEP16
5542   Apple     safe    12NOV16
9855   Apple     reject  23NOV16

The group by and join that I use in my program then generates this output. For a given lot number apearing multiple times I have the min max date.

Lot    Fruit     Status  Date      MinDATE   MaxDATE
1245   Apple     pass    12OCT16   11OCT16   23OCT16
1245   Pear      pass    11OCT16   11OCT16   23OCT16
1245   Lemon     safe    23OCT16   11OCT16   23OCT16
1245   Orange    reject  12OCT16   11OCT16   23OCT16
5542   Pear      pass    23SEP16   23SEP16   12NOV16
5542   Apple     safe    12NOV16   23SEP16   12NOV16
9855   Apple     reject  23NOV16   23NOV16   23NOV16

Is there anyway that "PARTION" could be used to return this type of data instead of using grouping?

Answer 1

You want a flag when anything in the log is "reject". Here is one method:

select t.*, coalesce(l.flag, 0) as flag
from t left join
     (select lot, 1 as flag
      from t
      where status = 'reject'
      group by lot
     ) l
     on t.lot = l.lot;

Answer 2

If your database supports analytics functions, by all means, go with this query.

select      t.*
           ,max(case when Status = 'reject' then 1 else 0 end) over
            (
                partition by Lot
            )                       as flag

from        mytable t
;

If your database does not supports analytics functions, any suggestion will do (and Gordon was the first to give an answer), so just for learning purposes:

select  t.*     
       ,coalesce 
        (
           (select  max(1) 
            from    mytable t2 
            where   t2.Lot = t.Lot 
                and t2.Status = 'reject'
           )
           ,0
        ) as Flag

from    mytable t
;

---

select  t.*
       ,coalesce(f.flag,0)

from    mytable t left join (select 1 as flag) f

        on      exists 
                (
                    select  null
                    from    mytable t2
                    where   t2.Lot = t.Lot 
                        and t2.Status = 'reject' 
                 )