Help - Divided by Zero Error - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help - Divided by Zero Error

Author

Topic

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-06-04 : 13:38:46

I am getting the above error on my coworkers SQL procedure she created. I was trying to fix it, but I can't find where the problem is. I did put in a few NULLIF, but still not helping. Can someone look to see if they can find the issue for me? I have been looking at this all morning and my coworker is on vacation.

Thanks,

@StartDate datetime,
@EndDate datetime,
@Negotiator varchar(1000)

as
Begin

--====DEAL QUERY===--
--declare
--@StartDate datetime = '4/8/2013',
--@EndDate datetime= '4/12/2013',
--@Negotiator varchar(1000) = 'Jenny Lavin, Kathleen Sims, Zenobia Bryan, Jonathan Greenberg, Alice Minger, Andrew Zavada, Dan Wetzel, Michael Salerno, Shawn Scavone- CPC, Dawn Newton, Michael Sarnese, Paige McQuillan, Thomas Urban- CPC, Adam Richardson- CPC, Jennifer Kerr, Mary Bracarella, Client Services, Kyle Hauber, Amelia Kahiu, Ian Throne, John Howland'

Select Deals.*,
NoDeal.ndTINCOUNT as NoDealTinCount,
Nodeal.ndClaims as NoDealClaims,
NODeal.ndAvgCharge as NoDealAvgCharge,
Total.tTINCOUNT as TotalTinCount,
total.tClaims as TotalClaims,
Total.tAvgCharge as TotalAvgCharge,
total.tAvgTAT as TotalAvgTAT,
Convert(decimal(10,6),(convert(decimal(18,6),NULLIF(Deals.dTINCOUNT,0))/convert(decimal(10,6),NULLIF(Total.tTINCOUNT,0))) * 100.00) as TINHR,
Convert(decimal(10,6),(convert(decimal(18,6),NULLIF(Deals.dclaims,0))/convert(decimal(10,6),NULLIF(Total.tclaims,0))) * 100.00) as ClaimHR
from (
select o.ClaimOwner as ClaimOwner,
DateAdd(d,-DatePart(dw,CLM_DOUT),DateAdd(d,6,clm_dout)) as "Week Ending",
count (distinct v.tin) as [DTINCOUNT],
count (distinct v.clm_id1) as [DClaims],
AVG (v.clm_tchg) as [DAvgCharge],
--AVG (v.TCPercentOfMedicare) as [Davg%MedBilled],
-- -- AVG(v.NegoPercentOfMedicare) as [Davg%ofMedPd],
(sum(NULLIF(v.clm_tchg,0))/sum(Cast(v.medicareamount as decimal(18,6))) * 100.00) as [Davg%MedBilled],
(sum(NULLIF(v.clm_nego,0))/sum(Cast(v.medicareamount as decimal(18,6))) * 100.00) as [Davg%ofMedPd],

sum (v.AccessFeeFinal) as [DAccess Fee],
sum (v.clm_sppo) as [DSavings],
sum (v.clm_tchg) as [DChrgs],
AVG (v.tat) as [DAvgTAT],
CONVERT(DECIMAL(10,6),(sum(NULLIF(v.clm_sppo,0))/sum(NULLIF(v.clm_tchg,0))) * 100.00) as '%ofSavings'

from vw_NGClaims v left JOIN vw_ngxrmclaimowners o on v.clm_id1 = o.impactclaimid
where CLM_DOUT between @StartDate and @EndDate
and isError <> '1'and negdup is null
and clm_att2 in ('ad')
group by DateAdd(d,-DatePart(dw,CLM_DOUT),DateAdd(d,6,clm_dout)), o.ClaimOwner
) as Deals JOIN

--====NO DEAL QUERY====--

(select o.ClaimOwner as ClaimOwner,
DateAdd(d,-DatePart(dw,CLM_DOUT),DateAdd(d,6,clm_dout)) as "Week Ending",
count (distinct v.tin) as [ndTINCOUNT],
count (distinct v.clm_id1) as [ndClaims],
AVG (v.clm_tchg) as [ndAvgCharge],
AVG (v.tat) as [ndAvgTAT]
from vw_NGClaims v left JOIN vw_ngxrmclaimowners o on v.clm_id1 = o.impactclaimid
where CLM_DOUT between @StartDate and @EndDate
and isError <> '1'and negdup is null
and clm_att2 in ('na')
group by DateAdd(d,-DatePart(dw,CLM_DOUT),DateAdd(d,6,clm_dout)), o.ClaimOwner
) as NoDeal ON Deals.ClaimOwner = NoDeal.ClaimOwner and Deals.[Week Ending] = Nodeal.[Week Ending]

JOIN
--====TOTAL CLAIM QUERY====--

(select o.ClaimOwner as ClaimOwner,
DateAdd(d,-DatePart(dw,CLM_DOUT),DateAdd(d,6,clm_dout)) as "Week Ending",
count (distinct v.tin) as [tTINCOUNT],
count (distinct v.clm_id1) as [tClaims],
AVG (v.clm_tchg) as [tAvgCharge],
AVG (v.tat) as [tAvgTAT]
from vw_NGClaims v left JOIN vw_ngxrmclaimowners o on v.clm_id1 = o.impactclaimid
where CLM_DOUT between @StartDate and @EndDate
and isError <> '1'and negdup is null
and clm_att2 in ('ad', 'na')
group by DateAdd(d,-DatePart(dw,CLM_DOUT),DateAdd(d,6,clm_dout)), o.ClaimOwner
) as Total ON Deals.ClaimOwner = Total.ClaimOwner and Deals.[Week Ending] = TOtal.[Week Ending]
where Deals.ClaimOwner COLLATE DATABASE_DEFAULT in (Select _ID from SplitString(@Negotiator,',') )
order by [Week Ending]
End

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-04 : 13:51:37

I see you added NULLIF's for three out of the five divisions. Add it to the others as well. (Just search for "/")

....
                            ( SUM(NULLIF(v.clm_tchg, 0))
                              / NULLIF(SUM(CAST(v.medicareamount AS DECIMAL(18, 6)),0))
                              * 100.00 ) AS [Davg%MedBilled] ,
                            ( SUM(NULLIF(v.clm_nego, 0))
                              / NULLIF(SUM(CAST(v.medicareamount AS DECIMAL(18, 6)),0))
                              * 100.00 ) AS [Davg%ofMedPd] ,
...

Use the nullif outside of a summation, not inside. In the numerators, it does not matter.

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-06-04 : 15:26:38

Thanks you so much, that worked for me!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-04 : 16:43:03

You are very welcome - glad to help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources