Solved

# calculated percentage data always retruning zero

Posted on 2006-07-13
300 Views
I'm trying to use T-SQL to return the relative percentage of two summed values when I divide the values into each other, but I always get 0. Here is my stored proc
CREATE PROCEDURE dbo.SC_PMPULL

(
@startdate datetime,
@enddate datetime
)
AS
BEGIN

INTO #tmpPull
FROM dbo.TestPull
Select P.fullname, coalesce(sum(P.received), ' ')as [U/W Received],coalesce(sum (P.denied) ,' ')as [U/W Denied] , P.underwrtr,(sum(P.denied) /sum(P.received) )as PercentageDenied--,P.month,P.year,P.monthvol
From #tmpPull  P
Group by P.fullname,P.underwrtr
drop table #tmpPull

END

0
Question by:jemigossaye

LVL 11

Assisted Solution

pootle_flump earned 200 total points
Hi

Your values are Ints - you need to cast them as DECIMAL (or NUMERIC) e.g.:

CAST((sum(P.denied) AS NUMERIC, 4, 3) /sum(P.received) )as PercentageDenied

0

LVL 142

Accepted Solution

Guy Hengel [angelIII / a3] earned 300 total points
CREATE PROCEDURE dbo.SC_PMPULL

(
@startdate datetime,
@enddate datetime
)
AS
BEGIN

INTO #tmpPull
FROM dbo.TestPull
Select P.fullname, coalesce(sum(P.received), ' ')as [U/W Received],coalesce(sum (P.denied) ,' ')as [U/W Denied] , P.underwrtr,(cast(sum(P.denied)  as decimal(20,4)) / cast(sum(P.received)  as decimal(20,4)) )as PercentageDenied--,P.month,P.year,P.monthvol
From #tmpPull  P
Group by P.fullname,P.underwrtr
drop table #tmpPull
0

## Featured Post

### Suggested Solutions

I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…