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

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?

CodePudding user response：

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

CodePudding user response：

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