SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Divide by zero error encountered. error!!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

a180884p
Starting Member

United Kingdom
3 Posts

Posted - 03/19/2012 :  07:23:25  Show Profile  Reply with Quote
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
30282 Posts

Posted - 03/19/2012 :  07:30:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
"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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000