Hi All,

For the sake of all visitors to this post, here is the solution that I figured out, with combination of my own effort, plus some other online help.

Thanks.

;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 =

(

CASEWHEN 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#mytestAS A

INNER JOINCTE2AS B

ON A.RankID = B.RankID