Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

Select Format Decimal & Currency

Hello,

I have the following Stored Procedure....

CREATE PROCEDURE [dbo].[LocalRetainerAverage]
(
@Locality int
)
AS
BEGIN
SET NOCOUNT ON;
Select
AVG(RetainerValue) As Retainer,
AVG(FeeCommission) AS FeeCommission,
AVG(TotalRetainer) AS Total
From dbo.RetainerSurvey
Where Locality = @Locality
END

GO

Open in new window


Which is producing results like -

20333.333333

Ideally what I want to do is have the resuilts like

£20333.33

So add the £ and only show 2 characters after the period...

Appreciate any help you can offer -

Thank you
0
garethtnash
Asked:
garethtnash
3 Solutions
 
Rajkumar GsSoftware EngineerCommented:
DECLARE @INPUT FLOAT
SET @INPUT = 20333.333333
select '£' + CAST(CONVERT(money, ROUND(@INPUT , 2)) AS VARCHAR(10))

Open in new window

0
 
ProjectChampionCommented:
Personally I'd prefer to leave the formatting to the pertinent app or report generator etc. but if you insist on doing it within our query/stored proc, one possible method is to use Str() Function. It takes three arguments(the number, the number total characters to display, and the number of decimal places to display. For instance assuming you select the required value in a variable called @MyVar, you can use the following to get the result.
SELECT '£'+ STR(@MyVar, 10, 2)
0
 
jorgedeoliveiraborgesCommented:
SELECT '£'+ ltrim (STR(20333.333333, 10, 2));
SELECT '£'+ STR(20333.333333, 10, 2);
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jorgedeoliveiraborgesCommented:

DECLARE @v2 DECIMAL (36,10)
SELECT @v2 = 13243543.56565656

SELECT '£ '+ CONVERT(VARCHAR,CONVERT(MONEY,@v2),1) --13,243,543.57 
--Formatted with commas

Open in new window


http://www.tek-tips.com/faqs.cfm?fid=6157
0
 
jorgedeoliveiraborgesCommented:
declare @v money
select @v = 1322323.6666

select '£ '+ convert(varchar, @v, 0)  --1322323.67     
-- Rounded but no formatting

select '£ '+ convert(varchar, @v, 1)    --1,322,323.67     
-- Formatted with commas

select '£ '+ convert(varchar, @v, 2)    --1322323.6666  
-- No formatting

Open in new window



http://www.tek-tips.com/faqs.cfm?fid=6157
0
 
garethtnashAuthor Commented:
Hi Guys,

Thanks for all the responses, and apologies for the delayed response. One last question, what about if I wanted to round the number so that -

1,322,323.67
becomes -
1,322,324

thanks
0
 
garethtnashAuthor Commented:
thank you
0
 
jorgedeoliveiraborgesCommented:
>> One last question, what about if I wanted to round the number so that -
>> 1,322,323.67
>> becomes -
>> 1,322,324

declare @v money
set @v = round (1322323.67, 0) -- 1,322,324
select '£ '+ convert(varchar, @v, 1);

Open in new window



Was this comment helpful?
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now