'Creating a Custom Column based on two column's values

Order #  Item    Item Rejected   Pass/Fail   Order Pass
1        Glass   Yes             Fail        Fail
1        Ball    No              Pass        Fail
1        Shoe    No              Pass        Fail
1        Sock    No              Pass        Fail
2        Shoe    No              Pass        Pass
2        Sock    No              Pass        Pass
3        Glass   No              Pass        Fail
3        Shoe    Yes             Fail        Fail
3        Sock    No              Pass        Fail

Does anyone know a way to create that last column to determine if one row value in column 4("Pass/Fail") for each order in column 1(Order #) has a fail, then the whole order fails per the example above?

sqlsql-server


Solution 1:[1]

If you mean in a query, then just simply:

SELECT *, MIN([Pass/Fail] OVER (PARTITION BY [Order #]) AS [Order Pass]
  FROM yourTableName

will do the trick. This calculates the minimum of that column across rows that share the same Order #, and we just use the fact that Fail comes earlier when sorted than Pass, hence MIN.

If you want to have this accessible permanently, you can't just add a field like that to a table, but you can create a view:

CREATE VIEW OrdersWithStatus
AS
SELECT * -- don't use * in your code, best name all the columns individually
     , MIN([Pass/Fail] OVER (PARTITION BY [Order #]) AS [Order Pass]
  FROM yourTableName

Solution 2:[2]

It looks like a window'd aggregate might be what you're after?

select *, Min([pass/fail]) over(partition by orderNo) as [Order Pass]
from t

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 MarcinJ
Solution 2 Stu

Related Questions