SQL Server CASE statement dependent on other rows in result set
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 ) &gt; 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?