Try something like (I excluded one extra table which you didn't list here).
declare @lddataini datetime, @lddatafim datetime
SET @lddataini='20081121 00:00:00'
SET @lddatafim='20081127 23:59:59';
;with ReceitaporProduto(ProdID,Revenue,AmountSold) as
(
SELECT PRODID,SUM(TOTAL) as revenue,SUM(ri.qtd) asAmountSold from romaneioitens ri inner join romaneio r on r.id=ri.romaneioID where r.caddate BETWEEN @lddataini and @lddatafim GROUP BY prodid
),
ProductsByCategory as (select COUNT(*) as Products_Count, CategoryID from produto group by categoryid)
select HRRanks.* from
(
select prodId, P.categoryID,Revenue,AmountSold, Products_Count,
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 ProductsByCategory PC on P.CATEGORYid = PC.categoryid) HRRanks
where HR_Rank<= cast(0.5*(HRRanks.Products_Count) as int) order by categoryID,HR_Rank
>Sure
>
>TABLE [dbo].[romaneio]
> [id] [char](10) NOT NULL
> [lojaid] [char](10) NOT NULL
> [tipo] [char](2) NOT NULL
> [funcid] [char](10) NOT NULL
> [clienteid] [char](10) NOT NULL
> [status] [numeric](1 0) NOT NULL
> [caixaid] [char](10) NULL
> [caddate] [datetime] NOT NULL
> [clientname] [char](50) NOT NULL
> [nf] [numeric](2 0) NULL
> [nfnum] [char](10) NULL
> [volumes] [numeric](2 0) NOT NULL
> [boxes] [char](10) NOT NULL
> [ultacesso] [datetime] NULL
> [valor] [numeric](9 2) NOT NULL
> [qtd] [numeric](5 0) NOT NULL
> [passantecli] [char](50) NULL
> [expedid] [char](10) NULL
>
>TABLE [dbo].[romaneioitens]
> [romaneioid] [char](10) NOT NULL
> [prodid] [char](10) NOT NULL
> [eancode] [char](13) NOT NULL
> [qtd] [numeric](10 0) NOT NULL
> [preco] [numeric](14 2) NOT NULL
> [total] [numeric](14 2) NOT NULL
>
>TABLE [dbo].[produto]
> [id] [char](10) NOT NULL
> [lojaid] [char](10) NOT NULL
> [supplierid] [char](10) NOT NULL
> [categoryid] [char](10) NOT NULL
> [prodname] [char](50) NOT NULL
> [tipo] [char](2) NOT NULL
> [unicompra] [char](3) NULL
> [univenda] [char](3) NULL
> [pesovenda] [numeric](9 2) NULL
> [multcompra] [numeric](6 0) NULL
> [multvenda] [numeric](6 0) NULL
> [eancode] [char](13) NOT NULL
> [description] [char](254) NULL
> [unidade] [char](10) NOT NULL
> [margemref] [numeric](5 2) NULL
> [discontinu] [bit] NOT NULL
> [classfiscal] [char](1) NOT NULL
> [caddate] [datetime] NOT NULL
> [refeforn] [char](6) NOT NULL
> [numeracao] [char](10) NOT NULL
> [nomeetiq] [char](40) NOT NULL
> [funcid] [char](10) NOT NULL
> [refeetiq] [char](6) NOT NULL
> [padraograde] [char](20) NOT NULL
> [lote] [numeric](5 0) NOT NULL
> [aliqipi] [numeric](4 2) NOT NULL
> [multbaseicms] [numeric](5 4) NOT NULL
> [icmscred] [numeric](6 2) NOT NULL
>
>TABLE [dbo].[categprod]
> [id] [char](10) NOT NULL
> [lojaid] [char](10) NOT NULL
> [categname] [char](30) NOT NULL
> [description] [char](254) NULL
> [grossmarg] [numeric](5, 2) NULL
> [caddate] [datetime] NOT NULL
> [pesopeca] [numeric](5, 0) NULL
> [posicao] [char](1) NOT NULL
>
>Romaneio is the table that contains the sales, RomaneioItens contains the Sales Items. ProdID is the ID for the product related to table Produto's ID (primary key). Products(Table Produto) can be grouped by CategoryID related to the field ID from Categprod. What I need is for each category select the top 50% of products order by revenue.
>
>Thanks a lot
If it's not broken, fix it until it is.
My Blog