Dvide by zero error and percent calculation wrong

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

SQL Server 2005 Forums

Transact-SQL (2005)

Dvide by zero error and percent calculation wrong

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-11-05 : 13:28:44

Maybe these should be 2 different question but expaining it 2 times takes time. Big Sql select that goes into datagrid. Where you see its commented out I get a divide by zero error that I need to get ride of and just show 0%. teh next problem I have is on line 14, it gives me 100% and if the math is done it should not, don't know what I am doing wrong here??

Select ud.strUIc,
Sum(up.intReqstr) as REQ,
Sum(up.intAsgnStr) as ASGN,
CAST(CEILING(Sum(up.intAsgnStr)*1.0 / Sum(up.intReqstr) * 100) as varchar(10)) + '%' as [% ASGN],
ISNULL((Select(SUM(intReqStr)) from tblUnitPosition where Substring(strpara, 1, 1) = 9 and up.intUnitMobId = tblUnitPosition.intUnitMobId), 0) as [O/S],
ISNULL((Select(SUM(intAsgnStr)) from tblUnitPosition where Substring(strpara, 1, 1) = 9 and up.intUnitMobId = tblUnitPosition.intUnitMobId), 0) as [O/S ASGN],

--CASE WHEN (Select(SUM(intReqStr)) from tblUnitPosition where Substring(strpara, 1, 1) = 9 and up.intUnitMobId = tblUnitPosition.intUnitMobId) = 0 THEN '0%' ELSE
--Cast(Ceiling(ISNULL((Select(SUM(intAsgnStr)) from tblUnitPosition where Substring(strpara, 1, 1) = 9 and up.intUnitMobId = tblUnitPosition.intUnitMobId), 0)*1.0 /
--ISNULL((Select(SUM(intReqStr)) from tblUnitPosition where Substring(strpara, 1, 1) = 9 and up.intUnitMobId = tblUnitPosition.intUnitMobId), 0) * 100) as varchar(10)) + '%' END as [% O/S ASGN],

Sum(up.intAsgnStr) + isNull((Select(SUM(intAsgnStr)) from tblUnitPosition where Substring(strpara, 1, 1) = 9 and up.intUnitMobId = tblUnitPosition.intUnitMobId ), 0) as [Total ASGN],

14. CAST(Ceiling((Sum(up.intAsgnStr) + isNull((Select(SUM(intAsgnStr)) from tblUnitPosition where Substring(strpara, 1, 1) = 9 and up.intUnitMobId = tblUnitPosition.intUnitMobId ), 0))*1.0 / Sum(up.intReqstr)) * 100 as varchar(10)) + '%' as [% Total],

(Select sum(CASE WHEN up.strFillType in ('STATE') and up.intAsgnstr = 0 THEN 1 else 0 END))as [State_VAC],
(Select sum(CASE WHEN up.strFillType in ('NGB') and up.intAsgnstr = 0 THEN 1 else 0 END))as [NGB_VAC],
(Select sum(CASE WHEN up.strFillType in ('DA') and up.intAsgnstr = 0 THEN 1 else 0 END))as DA_VAC
from tblData as ud INNER JOIN
tblUnitPosition as up on up.intUnitMobId = ud.intUnitMobId
where Substring(up.strpara, 1, 1) not in ('9')
Group by up.intUnitMobId, strUic

Here is sample data, without % O/S Asign because it gives me divide by zero erro but would be O/S asgn divided O/S
UIC req Asgn %ASGN o/s o/s ASGn TS %TS SVAC NVAC DVAC
X43A0 24 1 5% 0 0 1 100% 1 1 0
PUFAA 146 6 5% 3 1 7 100% 1 1 2
PUMB0 101 1 1% 0 0 1 100% 1 2 0
X43B0 56 1 2% 0 0 1 100% 0 0 0
X43T0 182 1 1% 0 0 1 100% 1 0 2
X43C0 178 8 5% 1 1 9 100% 1 1 0

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-05 : 13:44:18

try changing the dividing ISNULL to NULLIF, you'll just need to COALESE the whole thing to return zero if the result is NULL.

--CASE WHEN (Select(SUM(intReqStr)) from tblUnitPosition where Substring(strpara, 1, 1) = 9 and up.intUnitMobId = tblUnitPosition.intUnitMobId) = 0 THEN '0%' ELSE 
--Cast(Ceiling(ISNULL((Select(SUM(intAsgnStr)) from tblUnitPosition where Substring(strpara, 1, 1) = 9 and up.intUnitMobId = tblUnitPosition.intUnitMobId), 0)*1.0 / 
--ISNULLNULLIF((Select(SUM(intReqStr)) from tblUnitPosition where Substring(strpara, 1, 1) = 9 and up.intUnitMobId = tblUnitPosition.intUnitMobId), 0) * 100) as varchar(10)) + '%' END as [% O/S ASGN],

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-05 : 14:05:47

Can you get rid of all those correlated sub-queries? It should be much more efficient and simpler to read. Try adding this table to the FROM clause:


from   tblData as ud 
INNER JOIN tblUnitPosition as up 
       on up.intUnitMobId = ud.intUnitMobId

left outer join tblUnitPosition as x
       on x.intUnitMobId = up.intUnitMobId
       and left(x.strpara, 1) = '9'

where  Substring(up.strpara, 1, 1) not in ('9')
Group by up.intUnitMobId, strUic

Then you can replace all the sub-queries by referencing the [x] table. Like this:


      .....
      ,ISNULL( SUM(x.intReqStr), 0) as [O/S]
       ,ISNULL( SUM(x.intAsgnStr), 0) as [O/S ASGN]
      ,etc....

Be One with the Optimizer
TG

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-11-06 : 10:43:37

Tg,
Tried this and the counts keep coming out wrong, tried swithcing stuff around but still not working to good.

Lamprey,
I changed to this format but now where it has 2 zeros is comes up with NULL instead of 0%
CAST(Ceiling(NULLIF((Select(SUM(intAsgnStr)) from tblUnitPosition where Substring(strpara, 1, 1) = 9 and up.intUnitMobId = tblUnitPosition.intUnitMobId), 0)*1.0 /
(Select(Count(intReqStr)) from tblUnitPosition where Substring(strpara, 1, 1) = 9 and up.intUnitMobId = tblUnitPosition.intUnitMobId) * 100) as varchar(10)) + '%' as [% Total Asgn],

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-11-06 : 11:09:56

All,
Ok got this working but I had to drop my Percetn sign, because it gave me cannot covnert to type integer, how can I get back in, thanks for the help.

coalesce(CAST(Ceiling(NULLIF((Select(SUM(intAsgnStr)) from tblUnitPosition where Substring(strpara, 1, 1) = 9 and up.intUnitMobId = tblUnitPosition.intUnitMobId), 0)*1.0 /
(Select(Count(intReqStr)) from tblUnitPosition where Substring(strpara, 1, 1) = 9 and up.intUnitMobId = tblUnitPosition.intUnitMobId) * 100) as varchar(10)), 0) as [% Total Asgn],

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-06 : 11:58:00

CAST( <insert statement here> AS VARCAHR(18)) + '%' AS [% Total Asgn]

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-11-06 : 13:02:15

Lamprey,
ok, I was doing that originally in the statement, guess I really never new waht it did with the ceiling command. I am going to try to cut out the first cast and see what happens. thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources