divide by zero error

lb003
lb003 used Ask the Experts™
on
I have a query that i am trying to run but i get a divide by zero error.  I pretty sure calling a case when in my query will resolve the issue but i'm not so sure where i need to call it within the query.  Can someone please point me in the right direction? Here's what i have so far:

 select *, StartsMTD/EnrollsMTD as MTDConversion,
StartsYTD/EnrollsYTD as YTDConversion
from
(
select
REP.descrip as Rep,
count(case when ENRL.startdate>=dateadd(wk,datediff(wk,0,getdate()),0) then ENRL.startdate else null end) as StartsWTD,
count(case when ENRL.enrolldate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.enrolldate else null end) as EnrollsMTD,
count(case when ENRL.startdate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.startdate else null end) as StartsMTD,
count(ENRL.enrolldate) as EnrollsYTD,
count(ENRL.startdate) as StartsYTD
from
dbo.adenroll ENRL
join
dbo.amrep REP on REP.amrepid = ENRL.amrepid
join
dbo.systudent SS on SS.systudentid = ENRL.systudentid      
where
REP.amrepid in ('277','356','37','359','365','300','444','360','362','445','299','271')
and
(ENRL.startdate >= dateadd(yy,datediff(yy,0,getdate()),0)
and ENRL.startdate<dateadd(dd,datediff(dd,0,getdate()),1)
or ENRL.enrolldate >= dateadd(yy,datediff(yy,0,getdate()),0)
or ENRL.startdate<dateadd(yy,datediff(yy,0,getdate()),1)
)
and SS.syschoolstatusid IN ( '13','5','74')
group by REP.descrip
)t
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
select *, CASE WHEN EnrollsMTD > 0 THEN  StartsMTD/ EnrollsMTD  ELSE NULL END as MTDConversion,
CASE WHEN EnrollsMTD > 0 THEN  StartsYTD/EnrollsYTD  ELSE NULL END as YTDConversion
Awarded 2008
Awarded 2008

Commented:
something like this:

select *, case when enrollsmtd  = 0 then 0 else StartsMTD/EnrollsMTD end as MTDConversion,

Commented:
Try this:
select *, 
       CASE WHEN EnrollsMTD  = 0 or EnrollsMTD IS NULL Then 0 Else StartsMTD/EnrollsMTD END as MTDConversion, 
       CASE WHEN EnrollsMTD  = 0 or EnrollsMTD IS NULL Then 0 Else StartsYTD/EnrollsYTD  END as YTDConversion
from
(
select 
REP.descrip as Rep,
count(case when ENRL.startdate>=dateadd(wk,datediff(wk,0,getdate()),0) then ENRL.startdate else null end) as StartsWTD,
count(case when ENRL.enrolldate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.enrolldate else null end) as EnrollsMTD,
count(case when ENRL.startdate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.startdate else null end) as StartsMTD,
count(ENRL.enrolldate) as EnrollsYTD,
count(ENRL.startdate) as StartsYTD
from 
dbo.adenroll ENRL 
join
dbo.amrep REP on REP.amrepid = ENRL.amrepid
join
dbo.systudent SS on SS.systudentid = ENRL.systudentid      
where
REP.amrepid in ('277','356','37','359','365','300','444','360','362','445','299','271')
and
(ENRL.startdate >= dateadd(yy,datediff(yy,0,getdate()),0)
and ENRL.startdate<dateadd(dd,datediff(dd,0,getdate()),1)
or ENRL.enrolldate >= dateadd(yy,datediff(yy,0,getdate()),0)
or ENRL.startdate<dateadd(yy,datediff(yy,0,getdate()),1)
)
and SS.syschoolstatusid IN ( '13','5','74')
group by REP.descrip
)t

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
pssandhu,
your suggestion works in that data is returned without error.  However, it returns only zeroes in the mtdconversion and ytdconversion columns

Commented:
Sorry, quick correction. Please see if this helps. If this still returns zero then I cannot see anything wrong with the syntax. Also try printing the values for all four columns to see that there are values in those columns and not all are zeros and nulls.

select *, 
       CASE WHEN EnrollsMTD  = 0 or EnrollsMTD IS NULL Then 0 Else StartsMTD/EnrollsMTD END as MTDConversion, 
       CASE WHEN EnrollsYTD  = 0 or EnrollsYTD IS NULL Then 0 Else StartsYTD/EnrollsYTD  END as YTDConversion
from
(
select 
REP.descrip as Rep,
count(case when ENRL.startdate>=dateadd(wk,datediff(wk,0,getdate()),0) then ENRL.startdate else null end) as StartsWTD,
count(case when ENRL.enrolldate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.enrolldate else null end) as EnrollsMTD,
count(case when ENRL.startdate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.startdate else null end) as StartsMTD,
count(ENRL.enrolldate) as EnrollsYTD,
count(ENRL.startdate) as StartsYTD
from 
dbo.adenroll ENRL 
join
dbo.amrep REP on REP.amrepid = ENRL.amrepid
join
dbo.systudent SS on SS.systudentid = ENRL.systudentid      
where
REP.amrepid in ('277','356','37','359','365','300','444','360','362','445','299','271')
and
(ENRL.startdate >= dateadd(yy,datediff(yy,0,getdate()),0)
and ENRL.startdate<dateadd(dd,datediff(dd,0,getdate()),1)
or ENRL.enrolldate >= dateadd(yy,datediff(yy,0,getdate()),0)
or ENRL.startdate<dateadd(yy,datediff(yy,0,getdate()),1)
)
and SS.syschoolstatusid IN ( '13','5','74')
group by REP.descrip
)t

Open in new window

Commented:
In other words, run your subquery by itself to see if it is returning values in columns StartsMTD, StartsYTD, EnrollsMTD, and EnrollsMTD.
P.

Author

Commented:
pssandhu,
i ran it again and still the same result.  I ran the subquery by itself and it returns values in all but most rows.  Any thoughts?  

Author

Commented:
here is what the query returns. columns areas follows, from left to right:
StartsWTD, EnrollsMTD, StartsMTD, EnrollsYTD, StartsYTD, MTDConversion, YTDConversion

0      2      3      49      16      1      0
0      2      0      5      0      0      0
0      4      1      27      4      0      0
0      14      9      124      61      0      0
0      0      0      4      1      0      0
0      9      2      56      18      0      0
0      4      0      4      0      0      0
0      5      2      20      3      0      0
0      3      2      21      2      0      0
0      4      0      4      0      0      0
0      4      3      55      19      0      0
0      2      1      56      25      0      0

Commented:
Let's try rounding:
select *, 
       ROUND(CASE WHEN EnrollsMTD  = 0 or EnrollsMTD IS NULL Then 0 Else StartsMTD/EnrollsMTD END, 5) as MTDConversion, 
       ROUND(CASE WHEN EnrollsYTD  = 0 or EnrollsYTD IS NULL Then 0 Else StartsYTD/EnrollsYTD  END, 5) as YTDConversion
from
(
select 
REP.descrip as Rep,
count(case when ENRL.startdate>=dateadd(wk,datediff(wk,0,getdate()),0) then ENRL.startdate else null end) as StartsWTD,
count(case when ENRL.enrolldate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.enrolldate else null end) as EnrollsMTD,
count(case when ENRL.startdate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.startdate else null end) as StartsMTD,
count(ENRL.enrolldate) as EnrollsYTD,
count(ENRL.startdate) as StartsYTD
from 
dbo.adenroll ENRL 
join
dbo.amrep REP on REP.amrepid = ENRL.amrepid
join
dbo.systudent SS on SS.systudentid = ENRL.systudentid      
where
REP.amrepid in ('277','356','37','359','365','300','444','360','362','445','299','271')
and
(ENRL.startdate >= dateadd(yy,datediff(yy,0,getdate()),0)
and ENRL.startdate<dateadd(dd,datediff(dd,0,getdate()),1)
or ENRL.enrolldate >= dateadd(yy,datediff(yy,0,getdate()),0)
or ENRL.startdate<dateadd(yy,datediff(yy,0,getdate()),1)
)
and SS.syschoolstatusid IN ( '13','5','74')
group by REP.descrip
)t

Open in new window

Author

Commented:
unfortunately we get the same results:
StartsWTD, EnrollsMTD, StartsMTD, EnrollsYTD, StartsYTD, MTDConversion, YTDConversion

0      2      3      49      16      1      0
0      2      0      5      0      0      0
0      4      1      27      4      0      0
0      14      9      124      61      0      0
0      0      0      4      1      0      0
0      9      2      56      18      0      0
0      4      0      4      0      0      0
0      5      2      20      3      0      0
0      3      2      21      2      0      0
0      4      0      4      0      0      0
0      4      3      55      19      0      0
0      2      1      56      25      0      0

Commented:
This is because you are dividing the number by bigger number and the answer has to be in decimals. 16 divided by 49  equals 0.33
Let's see if we can do some conversion and get the results.
P.
Commented:
Okay, this should work
select *, 
       CASE WHEN EnrollsMTD  = 0 or EnrollsMTD IS NULL Then 0 Else CAST(StartsMTD as Decimal(5,2))/CAST(EnrollsMTD as Decimal(5,2)) END as MTDConversion, 
       CASE WHEN EnrollsYTD  = 0 or EnrollsYTD IS NULL Then 0 Else CAST(StartsYTD as Decimal(5,2))/CAST(EnrollsYTD as Decimal(5,2)) END as YTDConversion
from
(
select 
REP.descrip as Rep,
count(case when ENRL.startdate>=dateadd(wk,datediff(wk,0,getdate()),0) then ENRL.startdate else null end) as StartsWTD,
count(case when ENRL.enrolldate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.enrolldate else null end) as EnrollsMTD,
count(case when ENRL.startdate>=dateadd(mm,datediff(mm,0,getdate()),0) then ENRL.startdate else null end) as StartsMTD,
count(ENRL.enrolldate) as EnrollsYTD,
count(ENRL.startdate) as StartsYTD
from 
dbo.adenroll ENRL 
join
dbo.amrep REP on REP.amrepid = ENRL.amrepid
join
dbo.systudent SS on SS.systudentid = ENRL.systudentid      
where
REP.amrepid in ('277','356','37','359','365','300','444','360','362','445','299','271')
and
(ENRL.startdate >= dateadd(yy,datediff(yy,0,getdate()),0)
and ENRL.startdate<dateadd(dd,datediff(dd,0,getdate()),1)
or ENRL.enrolldate >= dateadd(yy,datediff(yy,0,getdate()),0)
or ENRL.startdate<dateadd(yy,datediff(yy,0,getdate()),1)
)
and SS.syschoolstatusid IN ( '13','5','74')
group by REP.descrip
)t

Open in new window

Author

Commented:
Thank you very much, this worked greatly!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial