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
Medium Priority
386 Views
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
Question by:JCMcNeil
• 2

LVL 3

Expert Comment

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

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

Moliere earned 856 total points
ID: 8099185
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

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
Course of the Month13 days, 7 hours left to enroll