SQL get row based on condition Hi all please see attached data below.  I'm trying to get a row based on various conditions.

scenario 1 - get highest row if no hours exist against it that has (setup + processtime > 0). 

scenario 2 - if there's hours (like in this example) show next operation.  (which would be 60).

    CREATE TABLE ProdRoute
    ([ProdId] varchar(10), [OprNum] int, [SetupTime] int, [ProcessTime] numeric)
;

INSERT INTO ProdRoute
    ([ProdId], [OprNum], [SetupTime], [ProcessTime])
VALUES
    ('12M0004893', 12, 0.7700000000000000, 1.2500000000000000),
    ('12M0004893', 12, 0.0000000000000000, 0.0000000000000000),
    ('12M0004893', 40, 0.0800000000000000, 0.4000000000000000),
    ('12M0004893', 50, 0.0400000000000000, 2.8000000000000000),
    ('12M0004893', 50, 0.0000000000000000, 0.0000000000000000),
    ('12M0004893', 60, 0.0000000000000000, 0.6100000000000000),
    ('12M0004893', 60, 0.0000000000000000, 0.0000000000000000),
    ('12M0004893', 70, 0.0000000000000000, 1.2900000000000000),
    ('12M0004893', 70, 0.0000000000000000, 0.0000000000000000),
    ('12M0004893', 75, 0.0000000000000000, 3.8700000000000000),
    ('12M0004893', 75, 0.0000000000000000, 0.0000000000000000),
    ('12M0004893', 80, 0.0000000000000000, 0.5500000000000000),
('12M0003571', 3, 0.8900000000000000, 0.0000000000000000),
    ('12M0003571', 3, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 7, 1.0000000000000000, 0.0000000000000000),
    ('12M0003571', 10, 0.3000000000000000, 0.3000000000000000),
    ('12M0003571', 10, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 20, 0.0700000000000000, 0.1000000000000000),
    ('12M0003571', 20, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 30, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 40, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 50, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 60, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 60, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 70, 0.0700000000000000, 0.1500000000000000),
    ('12M0003571', 70, 0.0000000000000000, 0.0000000000000000)
;

CREATE TABLE ProdRouteTran
    ([ProdID] varchar(10), [MaxOpCompleted] int, [Hours] numeric)
;

INSERT INTO ProdRouteTran
    ([ProdID], [MaxOpCompleted], [Hours])
VALUES
    ('12M0004893', 50, 1.7800000000000000),
('12M0003571', 70, 1.2660000000000000)
;


expected output : 

ProdId  OprNum
12M0004893  60

ProdId  OprNum
12M0003571  70


Thanks!
This is just a guess on my behalf, it is not clear to me how you define highest row and next operation:

select ProdId, max(OprNum)
from (
    select ProdId, max(OprNum) as OprNum 
    from ProdRoute pr1 
    where not exists ( 
        select 1 
        from ProdRoute pr2 
        where pr1.ProdId = pr2.ProdId
          and pr1.OprNum = pr2.OprNum 
          and SetupTime + ProcessTime &gt; 0
    ) 
    group by ProdId 
    union all 
    select ProdId, min(OprNum) as OprNum 
    from ProdRoute pr1 
    where exists ( 
        select 1 
        from ProdRoute pr2 
        where pr1.ProdId = pr2.ProdId 
          and pr1.OprNum = pr2.OprNum 
          and SetupTime + ProcessTime &gt; 0
    ) 
    and not exists (
        select 1 
        from ProdRouteTran prt 
        where pr1.ProdId = prt.ProdId 
          and pr1.OprNum &lt;= prt.MaxOpCompleted
    ) 
    group by ProdId
) as t
group by ProdId;