SQL Server CASE statement dependent on other rows in result set

Question

I have some data that comes out of a cube.

I have one row in this order that has gone in as a substitution variance but I need to put it in the quantity variance bucket. The code that decides on whether it's a substitution is here at the lowest level (this is within a large CTE) (The code behind this cube is massive and exists on multiple levels)

CASE 
   WHEN ISNULL(PC.ItemDimCostPrice, SC.ItemDimCostPrice) = 1
        AND (PC.ConfigId <> SC.ConfigId
             OR PC.InventColorId <> SC.InventColorId
             OR PC.InventSizeId <> SC.InventSizeId
             OR PC.InventStyleId <> SC.InventStyleId) 
      THEN 1
   --New code here ... THEN 0
   WHEN PC.[Resource] IS NULL
        OR SC.[Resource] IS NULL 
     THEN 1
   ELSE 0
END AS [HasSubstitutionVariance] ,

The logic that they have given me which I need to add into this is basically if the cost grouping is material (I know how to calculate this) and there exists another record with the same costgroupid and operationid (within the production order) then this will never be a substitution variance. I was thinking of using rownumber and partitioning on costgroupid and operationid but as far as I'm aware you can't use window functions in a case statement? Anyone have any ideas how I would do this? Thanks!

Answer 1

So this is what i came up with. Was fairly straightforward actually. So as I stated in the post that code was in a CTE. I left that code alone, later in the CTE I added

CASE WHEN MC.HasSubstitutionVariance = 1
                      AND ( COUNT(MC.CostGroupId) OVER ( PARTITION BY MC.ProdId,
                                                         MC.CostGroupId,
                                                         MC.OprId ) > 1
                            AND LEFT(MC.CostGroupId, 3) IN ( 'LAB', 'FOH',
                                                             'L T', 'F T' )
                          ) THEN 0
                 ELSE MC.HasSubstitutionVariance
            END AS HasSubstitutionVariance ,

Which says is the costgroup repeated and is it standard material, if so mark substitution variance as 0. I thought you couldn't use OVER within a CASE statement? Or is it only certain window functions you can't use within CASE?