>SET @lddataini='20081121 00:00:00' >SET @lddatafim='20081127 23:59:59'; > >with ReceitaporProduto(ProdID,Revenue,AmountSold, nProducts_Count) as > ( > SELECT PRODID,SUM(TOTAL) as revenue,SUM(ri.qtd) asAmountSold, Count(*) as nProducts_Count from romaneioitens ri inner join romaneio r on r.id=ri.romaneioID where r.caddate BETWEEN @lddataini and @lddatafim GROUP BY prodid > ) > >select HRRanks.* from >( >select prodId,categoryID,unestoque,Revenue,AmountSold, Row_Number() OVER (partition by p.categoryID order by RP.revenue desc) as Rn, >rank() over (partition by p.categoryID order by RP.revenue desc) as HR_Rank > from ReceitaporProduto RP >inner join produto p on p.id=RP.prodid >inner join estoquefisico e on e.id=p.id >where e.ativo=1 >) HRRanks >where RN < cast(RP.nProducts_Count / 50 as int) order by categoryID,HR_Rank >