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_PE R_MONTH (@Studio_ID int)
as
select
StudioID=isnull(a.StudioID ,d.StudioI D)
, "month" =isnull(a."month" ,d."month")
, "year" =isnull(a."year" ,d."year")
, retention=convert(float,is null(a.Pay ments,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(DayDeactivat ed), "Year"=Year(DayDeactivated ), Deacs=count(*)
from fasterfitness.Deactivation s
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
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_PE
as
select
StudioID=isnull(a.StudioID
, "month" =isnull(a."month" ,d."month")
, "year" =isnull(a."year" ,d."year")
, retention=convert(float,is
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(DayDeactivat
from fasterfitness.Deactivation
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
ASKER
Thanks, but didn't work- made it, apparently, an integer so I got zeros.
Another try?
Anyone else?
Another try?
Anyone else?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
retention=convert(float,is
to:
retention=convert(numeric(
You may need to add to the first number (x) in numeric(x,y) if there is an overflow entry.