Need urgent help in a tricky t-SQL

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

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

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;

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;

;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

ChrisM@Work (2/15/2013)

Sachin Vaidya (2/14/2013)

Hi Cadavre,

Your response definitely shows a path to move ahead, thanks a lot for that.

But on the other side, it was very rude. It will discourage people from putting any questions to the forum.

To underline, this was not my tution assignment or homework which I wanted to get done from the forum (read spoonfeeding).

To create an example, I suggested RankID as 3 and 4 but it is not limited, RankID's can be in thousands, so I will not be writing thousand CASE staements...

What sounds like a cakewalk to you might be a bit 'tricky' for others, as all fingers are not same!

But in the end, I would like to appreciate your effort spent in posting a detailed reply.

Thanks and Regards.

Gosh, this is unexpected.

Cadavre could easily have posted his solution without any explanation. Many of us do. That's spoonfeeding. Instead he has taken the time (his own time, for free) to break the solution down into parts and explain to you in some detail how each works so you can understand it. Next time you face a similar problem you will be equipped with the necessary knowledge and skill to tackle it yourself, without seeking assistance from others. "Give a man a fish and you feed him for a day - teach him how to fish and you feed him for the rest of his life."
Cadavre's solution is a good example of the exceptionally high standard of assistance he offers to posters here. It's certainly not rude, and neither will it discourage others from seeking help on ssc.

UPDATE a
SET bResult = newValue
FROM #mytest a
INNER 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;