May 1, 2006 at 12:52 pm
I've got a query, where one of the conditions i need to check against for my result set is whether calculated value (in this case, 1 field divided by another) is greater than 80%. I'm getting a divide by 0 error, but everything i try to account for it doesn't seem to work. I've tried a case statement, a coalesce, etc... any suggestions on what i might be able to try to get this this to work? Here's my query...
select
count(lbd.his_user_id)
from
#LBD lbd INNER JOIN #TIT tit ON
lbd.his_user_id = tit.his_user_id
AND
convert(varchar(10), lbd.schedule_start_date, 101) = tit.date
AND
tit.ID_Num = 1
WHERE
lbd.schedule_start_date >= '4/4/2006' AND
lbd.schedule_start_date < dateadd(dd, 1, '4/4/2006') AND
lbd.his_schedule_source_id = 1 AND
lbd.ID_Num = 1 AND
tit.his_user_id = (select his_user_id from #TIT where ID_Num = 1) AND
(convert(numeric(5,2),
convert(float, lbd.total_training_seconds) /
convert(float, tit.tit_wfm)) >= .80)
Any suggestions are appreciated!
Ray
May 1, 2006 at 1:58 pm
This is probably a stupid question, but there's no way you could have stored a 0 for tit.tit_wfm? Without seeing the data, I'm kind of at a loss as to what's wrong because I tried your SQL with some of my own fake data, and it worked.
May 1, 2006 at 2:26 pm
Well, the problem is can't guarantee that that value tit.tit_wfm will be non-zero. So i need to account for that condition. So one of my test cases is has the value stored as a zero. I need to find out how I can do that.
May 1, 2006 at 3:06 pm
select
count(lbd.his_user_id)
from
#LBD lbd INNER JOIN #TIT tit ON
lbd.his_user_id = tit.his_user_id
AND
convert(varchar(10), lbd.schedule_start_date, 101) = tit.date
AND
tit.ID_Num = 1
WHERE
lbd.schedule_start_date >= '4/4/2006' AND
lbd.schedule_start_date = .80)
May 1, 2006 at 3:48 pm
... AND
(convert(numeric(5,2),
convert(float, lbd.total_training_seconds) /
NULLIF(convert(float, tit.tit_wfm), 0)) >= .80)
This will make NULL from the whole expression when it's going to divide by zero and NULL is not >= 0.80
_____________
Code for TallyGenerator
May 2, 2006 at 8:48 am
I think a previous poster meant:
replace
AND
(convert(numeric(5,2),
convert(float, lbd.total_training_seconds) /
convert(float, tit.tit_wfm)) >= .80)
with
AND CASE tit.tit_wfm
WHEN 0 THEN 0 -- to include, or 1 to exclude
ELSE convert(numeric(5,2), convert(float, lbd.total_training_seconds) / convert(float, tit.tit_wfm))
END >= .80
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply