August 5, 2009 at 11:01 am
Hey y'all,
I did not wite this code, however, I am not sure how to go about this. I am getting the error: Divide by zero error encountered. I'm pretty sure I want to check if the value is GREATER than zero, but I am still fairly new to all of this... Thank you.
Here is the code:
select case datepart(month,hmss.commission_month)
when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December'
end as Month,
s.store_group_code as store_code,
sum(hmss.bedding_opp) as bed_opps,
sum(hmss.bedding_close) as bed_closes,
CAST(CASE SUM(hmss.BEDDING_OPP) WHEN 0 THEN 0
ELSE ((SUM(hmss.BEDDING_CLOSE)/SUM(hmss.BEDDING_OPP)))
END AS decimal(19,4)) AS BED_RATIO,
isnull(sum(case
when s.store_type = 'S' then hmss.treatable_count
else 0
end), 0) as PROTECTION_TREATABLE,
isnull(sum(case
when s.store_type = 'S' then hmss.treated_count
else 0
end), 0) as PROTECTION_TREATED,CAST(CASE SUM(hmss.TREATABLE_COUNT) WHEN 0 THEN 0
ELSE ((SUM(hmss.TREATED_COUNT)/SUM(hmss.TREATABLE_COUNT)))
END AS decimal(19,4)) AS PROTECTION_RATIO,
cast(case sum(isnull(hmss.delivered_to_date, 0)) when 0 then 0
else ((ABS(SUM(isnull(hmss.CREDIT_MEMOS, 0)) + SUM(isnull(hmss.EVEN_EXCHANGE, 0))))/(ABS(SUM(isnull(hmss.CREDIT_MEMOS, 0)) + SUM(isnull(hmss.EVEN_EXCHANGE,0))) + SUM(isnull(hmss.delivered_to_date, 0))))
end as decimal(19,4)) AS TOTAL_CREDIT_MEMO,
cast(case sum(isnull(hmss.delivered_to_date, 0)) when 0 then 0
else ((abs(sum(isnull(hmss.credit_memo_sales,0)) + sum(isnull(hmss.even_exchange_sales,0))))/sum(isnull(hmss.delivered_to_date,0)))
end as decimal(19,4)) as sales_credit_memo,
cast(case isnull(sum(hmss.written_sales_count),0) when 0 then 0
else (isnull(sum(hmss.written_sales_primary),0)/(sum(hmss.written_sales_count))) end as decimal(19,2)) as avg_sale,
(select isnull(count(d.store),0) from duplicates as d where d.store in (select s1.store_code from dw_store as s1 where s1.store_group_code = s.store_group_code) and (d.result = 'Merged' or d.result = 'Kept') and datepart(month,d.changed_date) = datepart(month,hmss.commission_month) group by datepart(month,d.changed_date)) as dup_count,
hmss.commission_month as StartMonthNum,
s.region_code as Region,
isnull(sum(case
when s.store_type = 'S' then hmss.written_sales_count
else 0
end), 0) as WrittenSalesCount,
--sum(hmss.written_sales_count) as WrittenSalesCount,
sum(hmss.written_sales) as WrittenSales,
isnull(sum(case
when s.store_type = 'S' then hmss.written_sales_primary
else 0
end), 0) as WrittenSalesPrimary,
--sum(hmss.written_sales_primary) as WrittenSalesPrimary,
sum(hmss.delivered_to_date) as DeliveredToDate,
abs(sum(hmss.credit_memos)) as CreditMemoCount,
abs(sum(hmss.even_exchange)) as EvenExchangeCount,
abs(sum(hmss.even_exchange_sales)) as EvenExchangeSales,
abs(sum(hmss.credit_memo_sales)) as CreditMemoSales,
sum(email_collected) as EmailCollected,
sum(email_chances) as EmailChances
FROM dw_historical_monthly_salesman_sales as hmss, dw_store as s
WHERE hmss.commission_month >= '07/01/2009' and
hmss.commission_month < '07/31/2009' and
hmss.store_code = s.store_code
GROUP BY DATEPART(MONTH, hmss.COMMISSION_MONTH), s.store_group_code, HMSS.COMMISSION_MONTH, s.region_code
HAVING (SUM(HMSS.WRITTEN_SALES) <> 0 OR SUM(HMSS.DELIVERED_TO_DATE) <> 0 OR SUM(HMSS.SCHEDULED_BY_MONTHEND) <> 0 OR SUM(HMSS.FUTURE_SCHEDULED) <> 0 OR SUM(HMSS.CREDIT_MEMOS) <> 0 OR SUM(HMSS.EVEN_EXCHANGE) <> 0)
ORDER BY DATEPART(MONTH, hmss.COMMISSION_MONTH),s.store_group_code, HMSS.COMMISSION_MONTH
August 5, 2009 at 11:26 am
This is probably/possible where the Div 0 error comes from:
SUM(hmss.BEDDING_CLOSE)/SUM(hmss.BEDDING_OPP)
If you use NullIf on the Bedding_Opp sum, you should be able to get rid of the error. If you need a value other than null in that case, you can use IsNull or Coalesce to wrap around that and give you the override you're looking for.
Might look like:
IsNull(SUM(hmss.BEDDING_CLOSE)/NullIf(SUM(hmss.BEDDING_OPP), 0), 0)
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 5, 2009 at 12:24 pm
GSquared (8/5/2009)
This is probably/possible where the Div 0 error comes from:
SUM(hmss.BEDDING_CLOSE)/SUM(hmss.BEDDING_OPP)If you use NullIf on the Bedding_Opp sum, you should be able to get rid of the error. If you need a value other than null in that case, you can use IsNull or Coalesce to wrap around that and give you the override you're looking for.
Might look like:
IsNull(SUM(hmss.BEDDING_CLOSE)/NullIf(SUM(hmss.BEDDING_OPP), 0), 0)Does that help?
Although it looked like it was goinging to work (meaning it was processing a little longer) it came back with the same error.
Here is the updated code, and thank you VERY much for your help:
select case datepart(month,hmss.commission_month)
when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December'
end as Month,
s.store_group_code as store_code,
sum(hmss.bedding_opp) as bed_opps,
sum(hmss.bedding_close) as bed_closes,
CAST(CASE SUM(hmss.BEDDING_OPP) WHEN 0 THEN 0
ELSE (IsNull(SUM(hmss.BEDDING_CLOSE)/NullIf(SUM(hmss.BEDDING_OPP), 0), 0))
END AS decimal(19,4)) AS BED_RATIO,
isnull(sum(case
when s.store_type = 'S' then hmss.treatable_count
else 0
end), 0) as PROTECTION_TREATABLE,
isnull(sum(case
when s.store_type = 'S' then hmss.treated_count
else 0
end), 0) as PROTECTION_TREATED,CAST(CASE SUM(hmss.TREATABLE_COUNT) WHEN 0 THEN 0
ELSE (IsNull(SUM(hmss.TREATED_COUNT)/NullIf(SUM(hmss.TREATABLE_COUNT), 0), 0))
END AS decimal(19,4)) AS PROTECTION_RATIO,
cast(case sum(isnull(hmss.delivered_to_date, 0)) when 0 then 0
else ((ABS(SUM(isnull(hmss.CREDIT_MEMOS, 0)) + SUM(isnull(hmss.EVEN_EXCHANGE, 0))))/(ABS(SUM(isnull(hmss.CREDIT_MEMOS, 0)) + SUM(isnull(hmss.EVEN_EXCHANGE,0))) + SUM(isnull(hmss.delivered_to_date, 0))))
end as decimal(19,4)) AS TOTAL_CREDIT_MEMO,
cast(case sum(isnull(hmss.delivered_to_date, 0)) when 0 then 0
else ((abs(sum(isnull(hmss.credit_memo_sales,0)) + sum(isnull(hmss.even_exchange_sales,0))))/sum(isnull(hmss.delivered_to_date,0)))
end as decimal(19,4)) as sales_credit_memo,
cast(case isnull(sum(hmss.written_sales_count),0) when 0 then 0
else (isnull(sum(hmss.written_sales_primary),0)/(sum(hmss.written_sales_count))) end as decimal(19,2)) as avg_sale,
(select isnull(count(d.store),0) from duplicates as d where d.store in (select s1.store_code from dw_store as s1 where
s1.store_group_code = s.store_group_code) and (d.result = 'Merged' or d.result = 'Kept')
and datepart(month,d.changed_date) = datepart(month,hmss.commission_month) group by datepart(month,d.changed_date)) as dup_count,
hmss.commission_month as StartMonthNum,
s.region_code as Region,
isnull(sum(case
when s.store_type = 'S' then hmss.written_sales_count
else 0
end), 0) as WrittenSalesCount,
--sum(hmss.written_sales_count) as WrittenSalesCount,
sum(hmss.written_sales) as WrittenSales,
isnull(sum(case
when s.store_type = 'S' then hmss.written_sales_primary
else 0
end), 0) as WrittenSalesPrimary,
--sum(hmss.written_sales_primary) as WrittenSalesPrimary,
sum(hmss.delivered_to_date) as DeliveredToDate,
abs(sum(hmss.credit_memos)) as CreditMemoCount,
abs(sum(hmss.even_exchange)) as EvenExchangeCount,
abs(sum(hmss.even_exchange_sales)) as EvenExchangeSales,
abs(sum(hmss.credit_memo_sales)) as CreditMemoSales,
sum(email_collected) as EmailCollected,
sum(email_chances) as EmailChances
FROM dw_historical_monthly_salesman_sales as hmss, dw_store as s
WHERE hmss.commission_month >= '07/01/2009' and
hmss.commission_month < '07/31/2009' and
hmss.store_code = s.store_code
GROUP BY DATEPART(MONTH, hmss.COMMISSION_MONTH), s.store_group_code, HMSS.COMMISSION_MONTH, s.region_code
HAVING (SUM(HMSS.WRITTEN_SALES) 0 OR SUM(HMSS.DELIVERED_TO_DATE) 0 OR SUM(HMSS.SCHEDULED_BY_MONTHEND) 0 OR SUM(HMSS.FUTURE_SCHEDULED) 0 OR SUM(HMSS.CREDIT_MEMOS) 0 OR SUM(HMSS.EVEN_EXCHANGE) 0)
ORDER BY DATEPART(MONTH, hmss.COMMISSION_MONTH),s.store_group_code, HMSS.COMMISSION_MONTH
August 5, 2009 at 12:26 pm
Do you think it may have something to do with this line or the one a couple of lines below it (or both)?
else ((ABS(SUM(isnull(hmss.CREDIT_MEMOS, 0)) + SUM(isnull(hmss.EVEN_EXCHANGE, 0))))/(ABS(SUM(isnull(hmss.CREDIT_MEMOS, 0)) + SUM(isnull(hmss.EVEN_EXCHANGE,0))) + SUM(isnull(hmss.delivered_to_date, 0))))
August 5, 2009 at 12:44 pm
donato1026 (8/5/2009)
Do you think it may have something to do with this line or the one a couple of lines below it (or both)?
else ((ABS(SUM(isnull(hmss.CREDIT_MEMOS, 0)) + SUM(isnull(hmss.EVEN_EXCHANGE, 0))))/(ABS(SUM(isnull(hmss.CREDIT_MEMOS, 0)) + SUM(isnull(hmss.EVEN_EXCHANGE,0))) + SUM(isnull(hmss.delivered_to_date, 0))))
Could be. It is possible that the code above could end up with a 0 in the denominator. Probably should wrap that in a nullif((...),0) then wrap the whole thing in an isnull((...),0)
August 5, 2009 at 12:49 pm
Lynn Pettis (8/5/2009)
donato1026 (8/5/2009)
Do you think it may have something to do with this line or the one a couple of lines below it (or both)?
else ((ABS(SUM(isnull(hmss.CREDIT_MEMOS, 0)) + SUM(isnull(hmss.EVEN_EXCHANGE, 0))))/(ABS(SUM(isnull(hmss.CREDIT_MEMOS, 0)) + SUM(isnull(hmss.EVEN_EXCHANGE,0))) + SUM(isnull(hmss.delivered_to_date, 0))))
Could be. It is possible that the code above could end up with a 0 in the denominator. Probably should wrap that in a nullif((...),0) then wrap the whole thing in an isnull((...),0)
I'm sorry and I hate to ask this, but could you show me please?
Do you mean like this:
(isnull(nullif((ABS(SUM(isnull(hmss.CREDIT_MEMOS, 0)) + SUM(isnull(hmss.EVEN_EXCHANGE, 0))))/(ABS(SUM(isnull(hmss.CREDIT_MEMOS, 0),0)) + SUM(isnull(hmss.EVEN_EXCHANGE,0))) + SUM(isnull(hmss.delivered_to_date, 0)))),0)
August 5, 2009 at 12:55 pm
You'd do the same thing as with the other division issue that I posted.
Use NullIf to turn the denominator into a null if it's zero, then use IsNull to replace it with whatever value you want when that comes up.
If you look up the two functions (NullIf, IsNull) in Books Online or on MSDN, you'll understand how they work and it'll make sense.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 5, 2009 at 1:01 pm
GSquared (8/5/2009)
You'd do the same thing as with the other division issue that I posted.Use NullIf to turn the denominator into a null if it's zero, then use IsNull to replace it with whatever value you want when that comes up.
If you look up the two functions (NullIf, IsNull) in Books Online or on MSDN, you'll understand how they work and it'll make sense.
Thank you. I inherited this code though and I am unfortunately a C# developer. Not that strong in SQL, and when I usually do my backend stuff, I usually use Visual Studio.
So, these case statements are doing division:
cast(case sum(IsNull(hmss.delivered_to_date, 0)) when 0 then 0
else ((ABS(SUM(IsNull(hmss.CREDIT_MEMOS, 0)) + SUM(IsNull(hmss.EVEN_EXCHANGE, 0))))/(ABS(SUM(IsNull(hmss.CREDIT_MEMOS, 0)) + SUM(IsNull(hmss.EVEN_EXCHANGE,0))) + SUM(IsNull(hmss.delivered_to_date, 0))))
end as decimal(19,4)) AS TOTAL_CREDIT_MEMO,
cast(case sum(IsNull(hmss.delivered_to_date, 0)) when 0 then 0
else ((abs(sum(IsNull(hmss.credit_memo_sales,0)) + sum(IsNull(hmss.even_exchange_sales,0))))/sum(IsNull(hmss.delivered_to_date,0)))
end as decimal(19,4)) as sales_credit_memo,
cast(case IsNull(sum(hmss.written_sales_count),0) when 0 then 0
else (IsNull(sum(hmss.written_sales_primary),0)/(sum(hmss.written_sales_count))) end as decimal(19,2)) as avg_sale,
I'm just not sure where to implement or put the NullIf or IsNull code... It's the parentheses that throw me off all the time.
I'm not trying to be a pain... :unsure:
Thank you!
August 5, 2009 at 1:13 pm
cast(
case sum(IsNull(hmss.delivered_to_date, 0))
when 0 then 0
else
ABS(SUM(IsNull(hmss.CREDIT_MEMOS, 0))
+ SUM(IsNull(hmss.EVEN_EXCHANGE, 0)))
/
NullIf( -- Turns the value to Null if it's 0, to avoid Div-0 error
ABS(SUM(IsNull(hmss.CREDIT_MEMOS, 0))
+ SUM(IsNull(hmss.EVEN_EXCHANGE,0))
+ SUM(IsNull(hmss.delivered_to_date, 0)))
, 0) -- Second value for NullIf
end
as decimal(19,4)) AS TOTAL_CREDIT_MEMO,
cast(
case sum(IsNull(hmss.delivered_to_date, 0))
when 0 then 0
else
(abs(sum(IsNull(hmss.credit_memo_sales,0))
+ sum(IsNull(hmss.even_exchange_sales,0))))
/
NullIf(sum(IsNull(hmss.delivered_to_date,0)), 0) -- Null value if = 0
end
as decimal(19,4)) as sales_credit_memo,
cast(
case IsNull(sum(hmss.written_sales_count),0)
when 0 then 0
else
IsNull(sum(hmss.written_sales_primary),0)
/
NullIf(sum(hmss.written_sales_count), 0) -- Null value if = 0
end
as decimal(19,2)) as avg_sale,
I formatted it for a bit better readability, added in the NullIf statements, and commented them.
Does that help?
I understand inheriting poorly written code, and having to work outside your area of expertise. Not a problem.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 5, 2009 at 1:40 pm
GSquared (8/5/2009)
cast(case sum(IsNull(hmss.delivered_to_date, 0))
when 0 then 0
else
ABS(SUM(IsNull(hmss.CREDIT_MEMOS, 0))
+ SUM(IsNull(hmss.EVEN_EXCHANGE, 0)))
/
NullIf( -- Turns the value to Null if it's 0, to avoid Div-0 error
ABS(SUM(IsNull(hmss.CREDIT_MEMOS, 0))
+ SUM(IsNull(hmss.EVEN_EXCHANGE,0))
+ SUM(IsNull(hmss.delivered_to_date, 0)))
, 0) -- Second value for NullIf
end
as decimal(19,4)) AS TOTAL_CREDIT_MEMO,
cast(
case sum(IsNull(hmss.delivered_to_date, 0))
when 0 then 0
else
(abs(sum(IsNull(hmss.credit_memo_sales,0))
+ sum(IsNull(hmss.even_exchange_sales,0))))
/
NullIf(sum(IsNull(hmss.delivered_to_date,0)), 0) -- Null value if = 0
end
as decimal(19,4)) as sales_credit_memo,
cast(
case IsNull(sum(hmss.written_sales_count),0)
when 0 then 0
else
IsNull(sum(hmss.written_sales_primary),0)
/
NullIf(sum(hmss.written_sales_count), 0) -- Null value if = 0
end
as decimal(19,2)) as avg_sale,
I formatted it for a bit better readability, added in the NullIf statements, and commented them.
Does that help?
I understand inheriting poorly written code, and having to work outside your area of expertise. Not a problem.
Unbelievable!!! That solved it! It makes so much more sense seeing it done the right way!
I truly thank you. I don't know what I would do without this site. Everyone here is extremely polite and so willing to help. I wish there was more of that out there.
Thanks again...
August 5, 2009 at 1:46 pm
You're welcome.
Yeah, this site is pretty cool. Gotta agree with that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 5, 2009 at 1:47 pm
That's the difference between a community (SSC) and a forum (all the others).
Thanks for the compliment (from all of us) and the feedback.
August 5, 2009 at 1:55 pm
Lynn Pettis (8/5/2009)
That's the difference between a community (SSC) and a forum (all the others).Thanks for the compliment (from all of us) and the feedback.
That's a very good point. And I never really looked at it that way. You know... being a developer for well over 10 years now, you just described in one sentence what seperates Communities from Forums in one sentence. And even I never thought about that.
I just design and build them. 😉
Thanks again... Truly ladies and gentlemen on here.
August 5, 2009 at 2:43 pm
donato1026 (8/5/2009)
Lynn Pettis (8/5/2009)
That's the difference between a community (SSC) and a forum (all the others).Thanks for the compliment (from all of us) and the feedback.
That's a very good point. And I never really looked at it that way. You know... being a developer for well over 10 years now, you just described in one sentence what seperates Communities from Forums in one sentence. And even I never thought about that.
I just design and build them. 😉
Thanks again... Truly ladies and gentlemen on here.
Yes, there are truely ladies and gentleman on here, but please don't include me in the former as it may shock my wife! 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply