?
Solved

Rounding float number to 1 decimal point in stored procedure

Posted on 2003-03-09
3
Medium Priority
?
381 Views
Last Modified: 2012-05-04
In this stored procedure, I get an output for "retention" that look like this:

58.82352941177

When I want it to look like this:

58.8

How can I get it to round the number to one decimal point?

Here's the SP:

Alter Procedure SP_RETENTION_PER_STUDIO_PER_MONTH  (@Studio_ID int)
as
select
StudioID=isnull(a.StudioID,d.StudioID)
, "month" =isnull(a."month" ,d."month")
, "year" =isnull(a."year" ,d."year")
, retention=convert(float,isnull(a.Payments,0))/(isnull(a.Payments,0)+isnull(d.Deacs,0))*100.0
from
(
select StudioID, "Month"=Month(date), "Year"=Year("date"), Payments=count(*)
from fasterfitness.Payments
where NewRenewal = 'Renewal'
group by StudioID, Year(date), Month(date)
) a
full join
(
select StudioID, "Month"=Month(DayDeactivated), "Year"=Year(DayDeactivated), Deacs=count(*)
from fasterfitness.Deactivations
group by StudioID, Year(DayDeactivated), Month(DayDeactivated)
) d on a.StudioID=d.StudioID and a."Year"=d."Year" and a."Month"=d."Month"
WHERE a.StudioID=@Studio_ID
order by StudioID, "Year" DESC, "Month" DESC
0
Comment
Question by:JCMcNeil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 3

Expert Comment

by:Moliere
ID: 8097985
Change:
retention=convert(float,isnull(a.Payments,0))/(isnull(a.Payments,0)+isnull(d.Deacs,0))*100.0
to:
retention=convert(numeric(7,1),(isnull(a.Payments,0))/(isnull(a.Payments,0)+isnull(d.Deacs,0))*100.0)

You may need to add to the first number (x) in numeric(x,y) if there is an overflow entry.
0
 

Author Comment

by:JCMcNeil
ID: 8099068
Thanks, but didn't work- made it, apparently, an integer so I got zeros.
Another try?
Anyone else?
0
 
LVL 3

Accepted Solution

by:
Moliere earned 856 total points
ID: 8099185
How about:
retention=convert(numeric(7,1),convert(float,isnull(a.Payments,0))/(isnull(a.Payments,0)+isnull(d.Deacs,0))*100.0)

If you were getting 58.82352941177, the added convert function should change it to 58.8.

Hopefully, second times the charm.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

752 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