Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Rounding float number to 1 decimal point in stored procedure

Posted on 2003-03-09
3
Medium Priority
?
386 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
  • 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

579 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