alter procedure uspGetProductWiseAmountDetailChartRpt1      

(      

@CompanyId int,      

@ChannelId int,    

@Month int  

)      

as      

      

begin      

  

  

select * from (select     

poid.ProductID,  

MONTH(POM.PODate) as 'Month',    

p.ProductName,      

SUM(POID.Amount) as 'Amount',      

SUM(poid.TaxAmount) as 'TaxAmount',  

  

RANK() OVER (partition by MONTH(POM.PODate) order by SUM(POID.Amount) desc) as 'Rnk'      

      

from PurchaseOrderItemDetail POID       

inner join PurchaseOrderMaster POM on POID.POID=pom.ID    

and poid.ProductID in (select top 5 po.ProductID from PurchaseOrderItemDetail PO   

inner join PurchaseOrderMaster PO2 on PO.POID=Po2.ID where MONTH(PO2.PODate)=@Month group by po.ProductID order by SUM(po.amount) desc)  

inner join Products P on poid.ProductID=p.id       

inner join Companies C on p.CompanyID=C.ID      

inner join Channels CH on p.ChannelID=CH.ID      

where p.CompanyID=isnull(@CompanyId,C.ID)       

and p.ChannelID=isnull(@ChannelId,CH.ID)      

and not  MONTH(POM.PODate)=@Month   

group by p.ProductName,MONTH(POM.PODate),poid.ProductID    

  

)aa where   

Rnk <=5 and aa.ProductID in (select top 5 p.ProductID from purchaseorderitemdetail p  

join PurchaseOrderItemDetail pod on pod.productid = p.ProductID  

group by pod.ProductID,p.ProductID order by SUM(pod.amount) desc)  

  

     

union  

  

select * from (select     

poid.ProductID,  

MONTH(POM.PODate) as 'Month',    

p.ProductName,      

SUM(POID.Amount) as 'Amount',      

SUM(poid.TaxAmount) as 'TaxAmount',  

  

RANK() OVER (partition by MONTH(POM.PODate) order by SUM(POID.Amount) desc) as 'Rnk'      

      

from PurchaseOrderItemDetail POID       

inner join PurchaseOrderMaster POM on POID.POID=pom.ID    

inner join Products P on poid.ProductID=p.id       

inner join Companies C on p.CompanyID=C.ID      

inner join Channels CH on p.ChannelID=CH.ID      

where p.CompanyID=isnull(@CompanyId,C.ID)       

and p.ChannelID=isnull(@ChannelId,CH.ID)      

and MONTH(POM.PODate)=@Month  

group by p.ProductName,MONTH(POM.PODate),poid.ProductID    

  

)aa where aa.Month=@Month and Rnk <=5  

     

      

end   

Out put is:

Productid   Month  ProductName Amount  TaxAmount Rnk