SQL How to show '0' value for a month, if no data exists.

Question

sql how to show '0' value for a month, if no data exists in the table for that month.

My Store procedure is :

      

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

11 7 ABCD 9715303.89 1206212.40 1

11 8 ABCD 621000.00 12360.00 2

11 10 ABCD 28800.00 0.00 5

11 11 ABCD 15000.00 0.00 1

12 10 XYZ 28879.69 0.00 4

14 10 ASDF 40000.00 4944.00 3

18 10 Cement 44467.92 2223.40 2

19 10 1245 105000.00 5250.00 1