CODEWITHSUNDEEP
sql
rafaeli gabriel
rafaeli gabriel

Reputation: 1

Divide by zero error encountered.(my brain is on meltdown)

select  
    x.date as date,x.df as dialfileid,x.filename,x.upload_date,
    sum(x.gross) as leadsreceived,
    sum(x.loaded) as leadsuploaded,
    sum(x.callable) as callable,
    sum(x.sales) as sales,
    sum(x.contacts) as contacts,
    sum(x.uncallable) as uncallable,
    case when sum(x.gross) = 0 then 1 else
    cast(((convert(decimal,sum(x.uncallable)) / 
            convert(decimal,sum(x.loaded)))*1) as numeric(20,2)) end as pen_rate,
    case when sum(x.contacts) = 0 then 1 else
    cast(((convert(decimal,sum(x.sales)) / 
            convert(decimal,sum(x.contacts)))*1) as numeric(20,2)) end AS con_rate_gross
from (
    select
        convert(varchar(10),df.loadts, 120) as date, df.dialfileid as df, df.filename as filename, df.loadts as upload_date,
        case when di.itemseqno is not null then 1 else 0 end as gross, --gross and net are same since there's no scrubbing
        case when di.laststatuscd in ('22','24')  then 0 else 1 end as loaded,
        case when (di.externalstatus in 
        ('a-cb','a-ccb','a-hu','a-dmni','a-busy','eng','a-am','am','a-na','na','nr') or di.externalstatus is null) then 1 else 0 end as callable,
        case when di.externalstatus in ('a-l','A-S','e-v') then 1 else 0 end as sales,
        case when di.externalstatus in ('A-L','A-S','E-V','A-NI','A-DNC','A-NQ','A-NQN') then 1 else 0 end as contacts,
        case when di.dialfileid != '1351' and di.externalstatus in 
        ('a-l','A-S','e-v','a-ni','a-lb','a-dnc','cba','a-lb','a-wn','a-ss','a-pm','a-fax','fax','dis') then 1 else 0 end as uncallable
    from tdialitem di, tdialfile df
    where 
    df.dialfileid = di.dialfileid 
    and df.campaigncd = '422USC'
) as x
group by x.date,x.df,x.filename,x.upload_date
order by x.df DESC

Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.

On the df.campaigncd, if I change the 422USC into 422CAC, there's no error message, and most of the campaigncd has no "Divide zero error message." Please help.

Upvotes: 0

Views: 273

Answers (2)

Linger
Linger

Reputation: 15058

You are not checking the right value for 0. The following CASE statement should check for divide by zero errors:

case when sum(x.gross) = 0 OR convert(decimal,sum(x.loaded)) = 0 then 1 else
cast(((convert(decimal,sum(x.uncallable)) / 
        convert(decimal,sum(x.loaded)))*1) as numeric(20,2)) end as pen_rate,
case when sum(x.contacts) = 0 OR convert(decimal,sum(x.contacts)) = 0 then 1 else
cast(((convert(decimal,sum(x.sales)) / 
        convert(decimal,sum(x.contacts)))*1) as numeric(20,2)) end AS con_rate_gross

I left your previous checks for 0 since that may have some relevance somewhere else (sum(x.gross) = 0 and sum(x.contacts) = 0). If not, then you should remove them.

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

You're checking for a zero sum in one field but then using another:

case when sum(x.gross) = 0 then 1 else
cast(((convert(decimal,sum(x.uncallable)) / 
        convert(decimal,sum(x.loaded)))*1) as numeric(20,2)) end as pen_rate,

So my guess is that sum(x.loaded) is 0.

Upvotes: 1

Related Questions