format results into currency

[kevgais]

Hi Guys,

A complete noob here so be gentle.

I have this statement (compiled from examples I have found on the web) which works fine, however I want the 'totalsales' column to be formatted into a currency format.

set dateformat dmy
SELECT convert(varchar,ChitClosedDateTime,103) ChitClosedDateTime, EmployeeNumber, SUM (ChitTotalAmount) as TotalSales
FROM COReMicrosChitInterface
where EmployeeNumber between 1 and 200
and ChitClosedDateTime between convert(datetime, '11/10/2006 00:00') and convert(datetime,'12/10/2006 23:59')
GROUP BY convert(varchar,ChitClosedDateTime,103), EmployeeNumber
order by totalsales desc

I have found this bit of code

SELECT '£' + Convert(Varchar, ChitTotalAmount) AS totalsales

but am unsure of where to put it.

any assistance greatfully recivied.

thanks
Kev

 

[mrdenny]

Your select statement should look something like this.

set dateformat dmy
SELECT convert(varchar,ChitClosedDateTime,103) ChitClosedDateTime, EmployeeNumber, '£' + Convert(Varchar, SUM (ChitTotalAmount)) as TotalSales
FROM COReMicrosChitInterface
where EmployeeNumber between 1 and 200
        and ChitClosedDateTime between convert(datetime, '11/10/2006 00:00') and convert(datetime,'12/10/2006 23:59')
GROUP BY convert(varchar,ChitClosedDateTime,103), EmployeeNumber
order by totalsales desc

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[kevgais]

thank you so much for your help.

it seems now though that the order by isn't working correctly as shown below:

this is suppose to be sorted in desc order

any ideas?

thanks again.

£97.20
£94.80
£91.50
£885.80
£85.15
£84.75
£84.55
£84.00
£83.30
£8.75
£8.55
£78.40
£77.00
£75.00
£74.65
£74.05
£711.10
£709.05
£69.75
£64.15
£614.15
£604.80
£60.60

 

[earthandfire]

It is sorting as you requested.

You code has changed the datatype to a text type and so the sort is text based, left to right of the characters in each string.

Hope this helps.

 

[SQLDenis]

order by SUM (ChitTotalAmount) desc

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/