Divide by zero error encountered. error!!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Divide by zero error encountered. error!!!

Author

Topic

a180884p
Starting Member

3 Posts

Posted - 2012-03-19 : 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

30421 Posts

Posted - 2012-03-19 : 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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources