Problem with stored procedure - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with stored procedure

Author

Topic

Cara726
Starting Member

3 Posts

Posted - 2007-12-12 : 14:30:43

I am trying to create a stored procedure that will bring back this information, but I'm having a problem with the math logic (I think). I don't know if the problem is that I'm trying to divide by zero, or if there is a problem with my joins. Can someone please help with this?

declare @crym int
declare @prym int

set nocount on
set @crym = (select top 1 Premium.YrMo from Premium order by Premium.YrMo desc)
set @prym = @crym - 100

--SET ARITHABORT OFF
--SET ANSI_WARNINGS OFF

Select
SpecName,
BranchName,
Cast(sum (Written) AS Float)AS CWP,
Cast(sum (PWritten) as Float) as PWP,
Cast(sum (Growth) AS Float) as CGR,
Cast(sum (Growth) AS Float)/Cast(sum (PWritten) AS Float)* 100 as GrowthRate

From
(SELECT Reps.SpecName, Branch.BranchName,
Cast((Premium.Written/100) AS Float) AS Written, 0 as PWritten,
Cast((Premium.Growth/100) AS Float) as Growth, 0 as LossRatio

FROM Reps
INNER JOIN Premium
ON Reps.RefID=Premium.ARefID
INNER JOIN Gname
ON Gname.RefID=Reps.RefID
INNER JOIN Branch
ON Gname.Name2=Branch.BranchCode

WHERE Premium.YrMo between floor(@crym/100) * 100 and @crym
Union All

SELECT Reps.SpecName, Branch.BranchName,0 AS Written,
Cast((Premium.Written/100) AS Float) AS PWritten,
0 as Growth, case when Premium.Earned = 0 then 0 else sum(Premium.LossIncurred/
Premium.Earned) End as LossRatio

FROM Reps
INNER JOIN Premium
ON Reps.RefID=Premium.ARefID
INNER JOIN Gname
ON Gname.RefID=Reps.RefID
INNER JOIN Branch
ON Gname.Name2=Branch.BranchCode
WHERE Premium.YrMo between floor(@prym/100) * 100 and @prym)m

Group by SpecName,BranchName
with rollup

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-12 : 16:24:51

It's hard to help you without you telling us exactly what the problem is. We can't just look at your code without a description of the problem or an error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-13 : 01:20:18

Note the difference

select cast(100/22 as float)
select cast(100*1.0/22 as float)

Madhivanan

Failing to plan is Planning to fail

Cara726
Starting Member

3 Posts

Posted - 2007-12-13 : 09:36:48

To Tara Kizer.. I noted the problem in the very beginning; however I was having GROUP by issues. I solved those issues, but am now still have the divide by zero error.

To Madhivanan.. Thank you for the suggestion, but I am not understanding what you mean. Can you please elaborate?

Thanks for your help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-13 : 11:14:42

I reread your first post and still can't find your problem. It just says I want to bring back this information and then doesn't show us what the information is. All we see is code, which is helpful but doesn't give us the information that we need to help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-12-13 : 11:40:06

quote:
Originally posted by Cara726

To Tara Kizer.. I noted the problem in the very beginning; however I was having GROUP by issues. I solved those issues, but am now still have the divide by zero error.

To Madhivanan.. Thank you for the suggestion, but I am not understanding what you mean. Can you please elaborate?

Thanks for your help!

Did you run the example Madhi gave?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-13 : 13:03:56

quote:
Originally posted by Cara726

To Tara Kizer.. I noted the problem in the very beginning; however I was having GROUP by issues. I solved those issues, but am now still have the divide by zero error.

I'm not trying to bust on you, but you need to remember that you have your head in the problem and we don't. So you need to provide more information about the specific problem you are having. That is like me posting asking for help with my math and giving you a formula like: @A + 10 / @C. When I realy want a standard deviation algoritm.

quote:
Originally posted by Cara726

To Madhivanan.. Thank you for the suggestion, but I am not understanding what you mean. Can you please elaborate?

select cast(100/22 as float) -- This translates to CAST(4 AS FLOAT) = 4
select cast(100*1.0/22 as float) -- CAST (4.545454 AS FLOAT) = 4.545454

The difference being that integer division gives you an integer so casting 4 to float gives you 4. Where as if you multiply by a float (1.0) or cast to a float then SQL will do float math for you.

Cara726
Starting Member

3 Posts

Posted - 2007-12-13 : 14:40:18

Thank you! I apologize for not being very clear.. this is my first time using forums.

Thank you also for clarifying the math; however, there is still something wrong with the GrowthRate. It comes out zero or null.

Cast(sum(Growth/case when PWritten = 0 then 0 else PWritten end) * 100 as Float) as GrowthRate

Is my case statement wrong in this particular case?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-13 : 14:43:35

This part doesn't make sense: case when PWritten = 0 then 0.

Rather than showing us your incorrect formula, please show us a data example of what you want with a clear explanation of it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-12-13 : 14:45:39

Try making it "Growth * 1.0 /case when PWritten = 0 then 0 else PWritten *1.0 end" for starters. It also looks like when Pwritten = 0 then you will get a division by 0 error.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 01:26:41

1 Cast(sum(Growth*1.0/case when PWritten = 0 then 1 else PWritten end) * 100 as Float) as GrowthRate
2 Cast(sum(Growth*1.0/case when PWritten = 0 then NULL else PWritten end) * 100 as Float) as GrowthRate

See which gives the expected result

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources