IF object_id('tempdb..#testEnvironment') IS NOT NULLBEGIN DROP TABLE #testEnvironment;END;SELECT RankID, WorkingID, RegionID, OverrideType, PctAllocation, bResultINTO #testEnvironmentFROM (SELECT 3, -4, 1, 1, 50, NULL UNION ALL SELECT 3, -4, 2, 1, 20, NULL UNION ALL SELECT 3, -4, 3, 1, 25, NULL UNION ALL SELECT 4, -3, 1, 1, 90, NULL UNION ALL SELECT 4, -3, 2, 3, 15, NULL UNION ALL SELECT 4, -3, 3, 3, 5, NULL ) a(RankID, WorkingID, RegionID, OverrideType, PctAllocation, bResult);

RankID WorkingID RegionID OverrideType PctAllocation bResult----------- ----------- ----------- ------------ ------------- -----------3 -4 1 1 50 NULL3 -4 2 1 20 NULL3 -4 3 1 25 NULL4 -3 1 1 90 NULL4 -3 2 3 15 NULL4 -3 3 3 5 NULL

SELECT RankID, CASE WHEN RankID = 3 AND SUM(PctAllocation) <> 100 THEN 0 END FROM #testEnvironmentGROUP BY RankID;

SELECT RankID, CASE WHEN RankID = 3 AND SUM(CASE WHEN OverrideType = 1 THEN 0 ELSE 1 END) = 0 AND SUM(PctAllocation) <> 100 THEN 0 END FROM #testEnvironmentGROUP BY RankID;

SELECT RankID, CASE WHEN RankID = 3 AND SUM(CASE WHEN OverrideType = 1 THEN 0 ELSE 1 END) = 0 AND SUM(PctAllocation) <> 100 THEN 0 WHEN RankID = 4 AND SUM(PctAllocation) > 100 THEN 0 ENDFROM #testEnvironmentGROUP BY RankID;

SELECT RankID, CASE WHEN RankID = 3 AND SUM(CASE WHEN OverrideType = 1 THEN 0 ELSE 1 END) = 0 AND SUM(PctAllocation) <> 100 THEN 0 WHEN RankID = 4 AND MAX(OverrideType) > MIN(OverrideType) AND SUM(PctAllocation) > 100 THEN 0 ENDFROM #testEnvironmentGROUP BY RankID;

;WITH CTE1 AS ( SELECT *, SUM(PctAllocation) OVER (PARTITION BY RankID) AS TotalAlloc FROM #mytest ), CTE2 AS ( SELECT RankID, MIN(nIndex_CapacityOverrideType) AS MinType, MAX(nIndex_CapacityOverrideType) AS MaxType, TotalAlloc FROM CTE1 GROUP BY RankID, TotalAlloc ) UPDATE #mytest SET bResult = ( CASE WHEN B.MinType = 1 AND B.MaxType = 1 AND B.TotalAlloc <> 100 THEN 0 WHEN B.MinType = 1 AND B.MaxType = 2 AND B.TotalAlloc > 100 THEN 0 WHEN B.MinType = 2 AND B.MaxType = 2 AND B.TotalAlloc > 100 THEN 0 WHEN B.MinType = 1 AND B.MaxType = 3 AND B.TotalAlloc <> 100 THEN 0 WHEN B.MinType = 2 AND B.MaxType = 3 AND B.TotalAlloc > 100 THEN 0 WHEN B.MinType = 3 AND B.MaxType = 3 AND B.TotalAlloc <> 100 THEN 0 ELSE 1 END ) FROM #mytest AS A INNER JOIN CTE2 AS B ON A.RankID = B.RankID

UPDATE aSET bResult = newValueFROM #mytest aINNER JOIN (SELECT RankID, CASE WHEN a.MinType = 1 AND a.MaxType = 1 AND a.TotalAlloc <> 100 THEN 0 WHEN a.MinType = 1 AND a.MaxType = 2 AND a.TotalAlloc > 100 THEN 0 WHEN a.MinType = 2 AND a.MaxType = 2 AND a.TotalAlloc > 100 THEN 0 WHEN a.MinType = 1 AND a.MaxType = 3 AND a.TotalAlloc <> 100 THEN 0 WHEN a.MinType = 2 AND a.MaxType = 3 AND a.TotalAlloc > 100 THEN 0 WHEN a.MinType = 3 AND a.MaxType = 3 AND a.TotalAlloc <> 100 THEN 0 ELSE 1 END AS newValue FROM (SELECT RankID, MAX(nIndex_CapacityOverrideType), MIN(nIndex_CapacityOverrideType), SUM(PctAllocation) FROM #mytest GROUP BY RankID) a(RankID, MaxType, MinType, TotalAlloc) ) b ON a.RankID = b.RankID;