Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

calculated percentage data always retruning zero

Posted on 2006-07-13
2
Medium Priority
?
361 Views
Last Modified: 2008-03-17
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

SELECT underwrtr,COUNT (received)as received,count(denied)as denied,fullname
 
INTO #tmpPull
FROM dbo.TestPull
WHERE received BETWEEN @startDate and @enddate and received is not null
GROUP BY underwrtr,fullname, denied,received
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
Comment
Question by:jemigossaye
2 Comments
 
LVL 11

Assisted Solution

by:pootle_flump
pootle_flump earned 800 total points
ID: 17103481
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 17103486
CREATE PROCEDURE dbo.SC_PMPULL

(
     @startdate datetime,
     @enddate datetime
)
AS
BEGIN

SELECT underwrtr,COUNT (received)as received,count(denied)as denied,fullname
 
INTO #tmpPull
FROM dbo.TestPull
WHERE received BETWEEN @startDate and @enddate and received is not null
GROUP BY underwrtr,fullname, denied,received
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question