1

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!

closed as unclear what you're asking by Mikael Eriksson, RLF, RolandoMySQLDBA, Max Vernon, Mark Storey-Smith Aug 11 '15 at 19:37

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • What do you mean by next operation? – Lennart Aug 11 '15 at 11:46
  • and what defines the highest row? – Lennart Aug 11 '15 at 11:47
  • oprnum = operation, so the last operation that has work (hours) against it is 50, the next op after this in the prodroute table is 60. this is the next active operation. highest row is row from prodroute that has setup + processtime > 0 – jhowe Aug 11 '15 at 11:58
3

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 > 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 > 0
    ) 
    and not exists (
        select 1 
        from ProdRouteTran prt 
        where pr1.ProdId = prt.ProdId 
          and pr1.OprNum <= prt.MaxOpCompleted
    ) 
    group by ProdId
) as t
group by ProdId;
  • I have added your query to sqlfiddle.com/#!6/12bb3/1 and am getting the correct result... i will need to test this with more examples... thanks! – jhowe Aug 11 '15 at 12:02
  • apologies the data i gave you for prodroutetran did not match exactly what i had. I have updated. ProdRoute.OprNum = ProdRouteTran.MaxOpCompleted sqlfiddle.com/#!6/7a588 – jhowe Aug 11 '15 at 12:34
  • I have changed the predicate in my answer, does that help? – Lennart Aug 11 '15 at 12:46
  • yes i have correct answer now i need to check through other examples. Thanks very much, get back shortly. – jhowe Aug 11 '15 at 12:48
  • I have found an order where i am getting no results. have updated original post data and fiddle sqlfiddle.com/#!6/e5db2c/1 I would expect to see 12M0003571 70 for this order... – jhowe Aug 11 '15 at 12:59

Not the answer you're looking for? Browse other questions tagged or ask your own question.