Author 
Topic 

a180884p
Starting Member
United Kingdom
3 Posts 
Posted  03/19/2012 : 07:23:25

I keep getting the divide by zero error? Someone advised to put '1' in the union but this is ruining my figures?
declare @LastYear nvarchar(4) declare @CurrentMonth nvarchar(2) declare @CurrentYear nvarchar(4)
select @CurrentYear=LEFT(MAX(Tpl_Month), 4), @CurrentMonth=RIGHT(MAX(Tpl_Month), 2), @LastYear= LEFT(MAX(Tpl_Month), 4)1 FROM TowergateAgency_MSCRM.dbo.Tpl_zonegwp
SELECT A.[companyID] as CompanyID, TowergateAgency_MSCRM.dbo.Account.name as [Agent], CASE WHEN ZB.Tpl_ZoneCatergory = 1 THEN 'Commercial' WHEN ZB.Tpl_ZoneCatergory = 2 THEN 'Personal' WHEN ZB.Tpl_ZoneCatergory = 3 THEN 'Other' else ' ' END [Zone Catergory], A.Zone, SUM(A.[TGWP 2012]) [TG  2012 YTD], SUM(A.[TGWP 2011]) [TG  2011 YTD], SUM(A.[NBGWP 2012]) [NB  2012 YTD], SUM(A.[NBGWP 2011]) [NB  2011 YTD], SUM(A.[RNLGWP 2012]) [RNL  2012 YTD], SUM(A.[RNLGWP 2011]) [RNL  2011 YTD], SUM((A.[TGWP 2012]  A.[TGWP 2011])*100)/sum(A.[TGWP 2012]) as [TG Variance], SUM((A.[NBGWP 2012]  A.[NBGWP 2011])*100)/sum(A.[NBGWP 2012]) as [NB Variance], SUM((A.[RNLGWP 2012]  A.[RNLGWP 2011])*100)/sum(A.[RNLGWP 2012]) as [RNL Variance]
FROM( select Tpl_CompanyId [CompanyID], Tpl_Month, Tpl_Channel, 0 as [TGWP 2012], SUM(Tpl_TotalGWP) as [TGWP 2011], 0 as [NBGWP 2012], 0 as [NBGWP 2011], 0 as [RNLGWP 2012], 0 as [RNLGWP 2011], Tpl_ZoneId, Tpl_Zone [ZONE], LEFT(Tpl_Month, 4) as Year, RIGHT(Tpl_Month, 2) as Month from TowergateAgency_MSCRM.dbo.Tpl_zonegwp
where LEFT(Tpl_Month, 4) = @lastyear and RIGHT(Tpl_Month, 2) <=@CurrentMonth AND DeletionStateCode = '0' GROUP BY Tpl_CompanyId, Tpl_Month, Tpl_Channel, Tpl_ZoneId, Tpl_Zone, LEFT(Tpl_Month, 4), RIGHT(Tpl_Month, 2)
UNION ALL
select Tpl_CompanyId [CompanyID], Tpl_Month, Tpl_Channel, SUM(Tpl_TotalGWP) as [TGWP 2012], 0 as [TGWP 2011], 0 as [NBGWP 2012], 0 as [NBGWP 2011], 0 as [RNLGWP 2012], 0 as [RNLGWP 2011], Tpl_ZoneId, Tpl_Zone [ZONE], LEFT(Tpl_Month, 4) as Year, RIGHT(Tpl_Month, 2) as Month from TowergateAgency_MSCRM.dbo.Tpl_zonegwp where LEFT(Tpl_Month, 4) = @currentyear and RIGHT(Tpl_Month, 2) <=@CurrentMonth AND DeletionStateCode = '0' GROUP BY Tpl_CompanyId, Tpl_Month, Tpl_Channel, Tpl_ZoneId, Tpl_Zone, LEFT(Tpl_Month, 4), RIGHT(Tpl_Month, 2)
)A
INNER JOIN TowergateAgency_MSCRM.dbo.Account ON A.companyID = TowergateAgency_MSCRM.dbo.Account.AccountId INNER JOIN TowergateAgency_MSCRM.dbo.Tpl_zone ZB on ZB.Tpl_zoneId = a.Tpl_ZoneId
GROUP BY ZB.Tpl_ZoneCatergory,A.Zone, A.[CompanyID],TowergateAgency_MSCRM.dbo.Account.name ORDER BY 2 

SwePeso
Patron Saint of Lost Yaks
Sweden
30407 Posts 
Posted  03/19/2012 : 07:30:09

"Division by zero" error can only happen in a division. In your case, these three lines
SUM((A.[TGWP 2012]  A.[TGWP 2011])*100)/sum(A.[TGWP 2012]) as [TG Variance], SUM((A.[NBGWP 2012]  A.[NBGWP 2011])*100)/sum(A.[NBGWP 2012]) as [NB Variance], SUM((A.[RNLGWP 2012]  A.[RNLGWP 2011])*100)/sum(A.[RNLGWP 2012]) as [RNL Variance]
Change them to
SUM(a.[TGWP 2012]  A.[TGWP 2011]) * 100E / NULLIF(SUM(a.[TGWP 2012]), 0) AS [TG Variance], SUM(a.[NBGWP 2012]  A.[NBGWP 2011]) * 100E / NULLIF(SUM(a.[NBGWP 2012]), 0) AS [NB Variance], SUM(a.[RNLGWP 2012]  A.[RNLGWP 2011]) * 100E / NULLIF(SUM(a.[RNLGWP 2012]), 0) AS [RNL Variance]
N 56°04'39.26" E 12°55'05.63" 



Topic 


