Hi Everyone - Below is my query which is erroring out. Need your help to fix this. Thanks

Select *, (AverageSpend/CASE WHEN @year = YEAR(GETDATE()) THEN MONTH(GETDATE()) ELSE 12 END )*12 as annual,
concat((AverageSpend/cast(AnnualCap as float)) * 100,'%' ) as AnnualCapSpentPercent,
concat((AverageDaystomeetcap/cast(NumberofPatientsMetCap as float)) * 100,'%' ) as PercentPatientsMetAnnualCap

	 from #DollarCapExecutive  
			 
Hi @kkran  ,    
The reason for this error is that your divisor contains 0.According to the data you provided, the value of NumberofPatientsMetCap contains 0. Therefore, as DanGuzman said, the value of NumberofPatientsMetCap contains 0 which caused an error.For this problem, you can refer to the following three solutions：    

Method 1: SQL NULLIF Function    
    create table #DollarCapExecutive  
    (Prgld int,AverageSpend float,AnnualCap int,AverageDaystomeetcap int,NumberofPatientsMetCap int)  
    insert into  #DollarCapExecutive values(10112,108.35,2000,6,5),(10112,2071.90,35000,136,1),  
    (10112,805.62,35000,0,0),(10112,1059.44,15000,0,0),(10112,48.74,300,12,18)  
    Select --(AverageSpend/CASE WHEN year = YEAR(GETDATE()) THEN MONTH(GETDATE()) ELSE 12 END ) as annual,  
    concat((AverageSpend/cast(AnnualCap as float))* 100,'%' ) as AnnualCapSpentPercent,  
    concat((AverageDaystomeetcap/cast(nullif(NumberofPatientsMetCap,0) as float)) *100,'%' ) as PercentPatientsMetAnnualCap  
    from #DollarCapExecutive  
    drop table #DollarCapExecutive  
Output:    
AnnualCapSpentPercent PercentPatientsMetAnnualCap  
5.4175% 120%  
5.91971% 13600%  
2.30177% %  
7.06293% %  
16.2467% 66.6667%    
Method 2: Using CASE statement to avoid divide by zero error    
Select --(AverageSpend/CASE WHEN year = YEAR(GETDATE()) THEN MONTH(GETDATE()) ELSE 12 END ) as annual,  
concat((AverageSpend/cast(AnnualCap as float))*100,'%' ) as AnnualCapSpentPercent,  
case when NumberofPatientsMetCap=0 then null else  
concat((AverageDaystomeetcap/cast(NumberofPatientsMetCap as float))*100,'%') end as PercentPatientsMetAnnualCap  
from #DollarCapExecutive  
Output:    
AnnualCapSpentPercent PercentPatientsMetAnnualCap  
5.4175% 120%  
5.91971% 13600%  
2.30177% NULL  
7.06293% NULL  
16.2467% 66.6667%  
Method 3: SET ARITHABORT OFF    

For details, please refer to the following article.    

This method may cause performance problems, if not necessary, it is best to choose the first two methods    
Methods to avoid the SQL divide by zero error    
If you have any question, please feel free to let me know.    

If the response is helpful, please click "Accept Answer" and upvote it.    
Regards     

If the answer is helpful, please click "Accept Answer" and upvote it.    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.    
			 
It seems you have zero values for NumberOfPatientsMetCap so the percent calculation fails with the error. One way to avoid the error is with NULLIF(NumberOfPatientsMetCap ,0) to coerce a NULL value. The result after the CONCAT will be '%' with  this method so you may also want to tweak the expression according to how you want to display it.