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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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!!!
 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
30421 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  
 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.04 seconds. Powered By: Snitz Forums 2000