DECLARE @t TABLE(ItemRelation INT, SaleCount NUMERIC(10, 8), IsPromo BIT, DocumentNum INT, DocumentYear INT) INSERT INTO @t SELECT 11202, 8.85947691, 0, 137, 218 UNION ALL SELECT 11202, 9.450108704, 0, 137, 218 UNION ALL SELECT 11202, 12.40326767, 1, 137, 218 UNION ALL SELECT 11202, 25.98779894, 1, 137, 218 UNION ALL SELECT 11202, 63.19760196, 1, 137, 218 UNION ALL SELECT 11202, 8.85947691, 0, 138, 218 UNION ALL SELECT 11202, 9.450108704, 0, 138, 218 UNION ALL SELECT 11202, 12.40326767, 1, 138, 218 SELECT dat.* FROM ( SELECT t.ItemRelation ,t.DocumentNum ,t.DocumentYear ,t.SaleCount ,t.IsPromo ,SUM(CAST(IsPromo AS INT)) OVER(PARTITION BY t.ItemRelation, t.DocumentNum, t.DocumentYear) AS PromoSum FROM @t AS t ) AS dat WHERE dat.PromoSum > 1 AND dat.PromoSum < 5 |