This is not all that tricky, so rather than spoon feed you the answer I'm going to try and help you to arrive there on your own.

Firstly, it's always a good idea to produce DDL and sample data so that anyone wanting to help you doesn't have to do any extra work setting up a test environment.

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment;

END;

SELECT RankID, WorkingID, RegionID, OverrideType, PctAllocation, bResult

INTO #testEnvironment

FROM (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);

Which produces: -

RankID WorkingID RegionID OverrideType PctAllocation bResult

----------- ----------- ----------- ------------ ------------- -----------

3 -4 1 1 50 NULL

3 -4 2 1 20 NULL

3 -4 3 1 25 NULL

4 -3 1 1 90 NULL

4 -3 2 3 15 NULL

4 -3 3 3 5 NULL

Now, let's start with RankID 3. I'm going to assume that you got this far on your own: -

SELECT RankID,

CASE WHEN RankID = 3 AND SUM(PctAllocation) <> 100 THEN 0 END

FROM #testEnvironment

GROUP BY RankID;

So what the case statement is saying is, "if the rankid is 3 and the sum of the pctallocation is not 100 then the answer is 0, otherwise ignore (which returns NULL)". The third part of your case statement needs to say "if all of the overridetypeid columns are 1". This is easier done than it appears, we can just add a nested case statement: - "CASE WHEN OverrideType = 1 THEN 0 ELSE 1 END", then grab the SUM. If the sum is equal to 0, then they were all 1, otherwise they weren't. So let's add that in to the other case statement: -

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 #testEnvironment

GROUP BY RankID;

OK, so that deals with rankid 3.

For rankid 4, I'm going to assume that you got this far: -

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 END

FROM #testEnvironment

GROUP BY RankID;

So, the third part of your case statement this time needs to say "if the overridetype doesn't contain identical data". Again, this is easier done than you think. If we compare the MAX and the MIN, if they are different then we know that the data is mixed.

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 END

FROM #testEnvironment

GROUP BY RankID;

So now all you've got left to do is convert that in to an UPDATE statement. Remember that an aggregate can't appear in the WHERE clause. If you get stuck, post back with what you've tried and we'll take a look.