Sql select min или max на основе условия 2

Ответ 1

Основываясь на новых данных и последнем комментарии к ответу от пользователя, здесь обновленный запрос и скрипка: http://sqlfiddle.com/#!6/87e2f/2

Эй, я нашел пример, который не работает... orderID '12M0003381'... Я добавил данные к вашей скрипке. Я ожидал увидеть операцию 70 как что последняя операция с настройкой или временем процесса... спасибо!

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

Объяснение ниже для изменений запроса:

Единственное изменение, внесенное в запрос, заключалось в обработке значения NULL для weighted_value с использованием следующего синтаксиса

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

Проблематичной частью был внутренний запрос, который при выполнении без предложения group by показывает, что произошло в граничном случае, например, добавлен пользователем.

(Смотрите здесь скрипку: http://sqlfiddle.com/#!6/87e2f/3)

Без нулевой обработки мы имели NULL, который после предложения group by приводил к структуре, подобной ниже

(Смотрите здесь скрипку: http://sqlfiddle.com/#!6/87e2f/5)

Как вы можете видеть при группировке значения LEAD для prodid : 12M0003381, oprnum:70, полученного как NULL вместо 70 (поскольку группировка 70 и NULL должна давать 70).

Это оправдано, если LEAD вычисляется по сгруппированному запросу/таблице, что на самом деле происходит здесь.

В этом случае функция LEAD не вернет никаких данных для последней строки раздела. Это граничный случай и должен корректно обрабатываться с помощью ISNULL.

Я предположил, что значение LEAD oprnum значения последней строки должно быть скорректировано как oprnum значение текущей строки.

Старый ответ ниже:

Итак, я попробовал, и я отправляю ссылку на скрипку 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

Ответ 2

Это не самая красивая вещь, которую я когда-либо писал, но она работает. Я также тестировал его против другой скрипки с дополнительными данными.

Модифицировано для удовлетворения новых требований.

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]

Ответ 3

Я не уверен, что понимаю ваш вопрос, но вот моя попытка:

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

Ответ 4

Я получаю правильный результат с помощью SQL Fiddle.

Однако я не уверен, что полностью понимаю случай nothing >0. Немного больше данных со всеми случаями может быть полезно.

; With data as (
    Select r.ProdId
    , opr = case when h > 0 then  isnull(min(p.OprNum), r.OprNum) else  max(p.OprNum) end
    From (
        Select pr.ProdId, pr.OprNum, h = max(pr.SetupTime + pr.ProcessTime)     
        From ProdRoute as pr
        Inner Join ProdRouteTran as prt on pr.ProdId = prt.ProdID and pr.OprNum = prt.MaxOpCompleted
        Group By pr.ProdId, pr.OprNum
    ) as r
    left join ProdRoute as p on p.ProdId = r.ProdId and p.OprNum > r.OprNum
    Group By r.ProdId, r.OprNum, r.h
)
Select * From data

Ответ 5

Я не совсем уверен, понял ли я, что вы пытаетесь сделать, но может быть, это эквивалент?

SELECT
   t.ProdId,
   CASE WHEN r.OprNum IS NULL THEN t.MaxOpCompleted ELSE r.OprNum END AS OprNum
FROM
   ProdRouteTran t
   LEFT JOIN
      ProdRoute r
   ON
      r.ProdId = t.ProdId AND r.SetupTime + r.ProcessTime > 0 AND
      r.OprNum > t.MaxOpCompleted AND NOT EXISTS(
         SELECT * FROM ProdRoute p WHERE p.ProdId = t.ProdId AND
            p.OprNum > t.MaxOpCompleted AND p.OprNum < r.OprNum)

Ответ 6

Попробуйте это -

-- display the next operation, if condition match
SELECT do_exists.ProdId, do_exists.OprNum
FROM ProdRoute pr
INNER JOIN ProdRouteTran prt 
    ON prt.ProdId = pr.ProdId 
    AND pr.OprNum = prt.MaxOpCompleted 
    AND (pr.SetupTime + pr.ProcessTime) > 0
OUTER APPLY (
    SELECT TOP(1) pr.* 
    FROM ProdRoute pr
    WHERE prt.ProdID = pr.ProdId
    AND   pr.OprNum > prt.MaxOpCompleted
    ORDER BY pr.OprNum
) do_exists


UNION ALL

-- display the max operation, if matching data is not found in ProdRoute. 
---- Matching Data not found - 1) There is entry in ProdRoute for particular ProdId but hours is not present
----                           2) There is no entry in ProdRoute for a particular ProdId    
SELECT pr.ProdId, MAX(pr.OprNum) OprNum
FROM ProdRoute pr
LEFT JOIN (
    SELECT pr.ProdId
    FROM ProdRoute pr
    INNER JOIN ProdRouteTran prt 
        ON prt.ProdId = pr.ProdId 
        AND pr.OprNum = prt.MaxOpCompleted 
        AND (pr.SetupTime + pr.ProcessTime) > 0
) pr_ex ON pr_ex.ProdId = pr.ProdId
WHERE pr_ex.ProdId IS NULL 
GROUP BY pr.ProdId