sql select min or max based on condition part 2

Question

This post is in continuatation of a problem of another post sql select min or max based on condition

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(oprnum) after this number. (which would be 60 in prodroute).

The query needs to work within a CTE as it's part of a bigger query.

    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

Answer 1

Based on new data and last comment on the answer by asker, here's the updated query and fiddle :http://sqlfiddle.com/#!6/87e2f/2

hey i found an example that doesn't work... orderID '12M0003381'... i've added data to your fiddle. I would expect to see operation 70 as that's the last operation with a setup or process time... thanks!

select prodid, ISNULL(MAX(weighted_value),MIN(oprnum)) as value from
(           
            select 
                a.prodid,
                a.oprnum,
                ISNULL(LEAD(a.oprnum,1) OVER(Partition by a.prodID  ORDER by a.oprnum asc),a.oprnum)  * 
                MAX(case 
                    when ISNULL([Hours], 0) >= (setupTime + ProcessTime) AND (SetupTime + ProcessTime ) > 0 
                    then 1
                    else NULL
                    end) as weighted_value
            from temp1 a LEFT JOIN temp4 b 
                    ON a.OprNum = b.OPRNUM
                    AND a.ProdID = b.ProdId
            group by a.prodid,a.oprnum
) t
group by prodid

Explanation for below query changes:

The only change made to query was to handle the NULL value for weighted_value using the following syntax

ISNULL(LEAD(a.oprnum,1) OVER(Partition by a.prodID  ORDER by a.oprnum asc),a.oprnum)

The problematic part was the inner query which when run without group by clause shows what happened on a boundary case like added by user.

( See fiddle for this here: http://sqlfiddle.com/#!6/87e2f/3 )

Without null handling, we had a NULL which after group by clause resulted in a structure like below

( See fiddle for this here:http://sqlfiddle.com/#!6/87e2f/5 )

As you can see on grouping the LEAD value for prodid : 12M0003381, oprnum:70 resulted as NULL instead of 70 (as grouping 70 and NULL should give 70).

This is justified if LEAD is calculated on grouped query/table , which is actually what is happening here.

In that case, the LEAD function will not return any data for the last row of partition. This is the boundary case and must be handled correctly with ISNULL.

I assumed that LEAD oprnum value of last row should be corrected as oprnum value of current row.

Old answer below:

So I tried and I am posting the fiddle link http://sqlfiddle.com/#!6/e965c/1

select prodid, ISNULL(MAX(weighted_value),MIN(oprnum)) as value from 
( 
select 
a.prodid, 
a.oprnum, 
LEAD(a.oprnum,1) OVER(Partition by a.prodID ORDER by a.oprnum asc) * 
MAX(case 
when ISNULL([Hours], 0) >= (setupTime + ProcessTime) AND (SetupTime + ProcessTime ) > 0 
then 1 
else NULL 
end) as weighted_value 
from ProdRoute a LEFT JOIN COMPLETED_OP b 
ON a.OprNum = b.OPRNUM 
AND a.ProdID = b.ProdId 
group by a.prodid,a.oprnum 
) t 
group by prodid

Answer 2

This isn't the prettiest thing I have ever written but it works. I also tested it against the other fiddle with additional data.

Modified to meet new requirement.

SELECT
    *
FROM
    (
        SELECT
            A.ProdID,
            MIN(A.OprNum) AS 'OprNum'
        FROM
            #ProdRoute AS A
            JOIN 
                (
                    SELECT
                        ProdID,
                        MAX(MaxOpCompleted) AS 'OprNum'
                    FROM
                        #ProdRouteTran
                    GROUP BY
                        ProdID
                ) AS B
                ON A.ProdId = B.ProdId AND A.OprNum > B.OprNum
        GROUP BY
            A.ProdID
    ) AS [HoursA]
UNION ALL
SELECT
    *
FROM
    (
        SELECT
            DISTINCT
            A.ProdID,
            B.OprNum
        FROM
            #ProdRoute AS A
            JOIN 
                (
                    SELECT
                        ProdID,
                        MAX(MaxOpCompleted) AS 'OprNum'
                    FROM
                        #ProdRouteTran
                    GROUP BY
                        ProdID
                ) AS B
                ON A.ProdId = B.ProdId AND A.OprNum = B.OprNum
                    AND B.OprNum = (SELECT MAX(OprNum) FROM #ProdRoute WHERE ProdId = A.ProdId)
    ) AS [HoursB]
UNION ALL
SELECT
    *
FROM
    (
        SELECT
            ProdId,
            MIN(OprNum) AS 'OprNum'
        FROM
            #ProdRoute
        WHERE
            ProdId NOT IN 
                (SELECT ProdId FROM #ProdRouteTran)
            AND (SetupTime <> 0 OR ProcessTime <> 0)
        GROUP BY
            ProdId
    ) AS [NoHoursA]
UNION ALL
SELECT
    *
FROM
    (
        SELECT
            ProdId,
            MIN(OprNum) AS 'OprNum'
        FROM
            #ProdRoute
        WHERE
            ProdId NOT IN 
                (SELECT ProdId FROM #ProdRouteTran)
        GROUP BY
            ProdId
        HAVING
            SUM(SetupTime) = 0 AND SUM(ProcessTime) = 0
    ) AS [NoHoursB]

Answer 3

I'm not really sure I understand your question, but here is my attempt:

SELECT
    pr.ProdId,
    CASE 
        WHEN SUM(SetupTime) + SUM(ProcessTime) > 0 THEN MAX(x.OprNum)
        ELSE MAX(pr.OprNum)
    END
FROM ProdRoute pr
INNER JOIN (
    SELECT ProdID, MAX(MaxOpCompleted) AS OprNum
    FROM ProdRouteTran
    GROUP BY ProdID
)prt
    ON prt.ProdId = pr.ProdID
    AND prt.OprNum = pr.OprNum
OUTER APPLY(
    SELECT TOP 1 OprNum FROM ProdRoute 
    WHERE 
        ProdId = pr.ProdId
        AND OprNum > pr.OprNum
    ORDER BY OprNum
)x
GROUP BY pr.ProdId
ORDER BY pr.ProdId