Link to home
Start Free TrialLog in
Avatar of JCMcNeil
JCMcNeil

asked on

Rounding float number to 1 decimal point in stored procedure

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
Avatar of Moliere
Moliere

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.
Avatar of JCMcNeil

ASKER

Thanks, but didn't work- made it, apparently, an integer so I got zeros.
Another try?
Anyone else?
ASKER CERTIFIED SOLUTION
Avatar of Moliere
Moliere

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial