FORMAT TABLE AND ADD A COMMA ?

Last Post 18 Sep 2009 10:12 AM by JASBRYDEN. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
JASBRYDEN
New Member
New Member

--
16 Sep 2009 05:30 AM
Hi

I am setting is SQL script to create a table to send to all the manages the sales.

I would like to format the table to get only 2 decimal places for the 'cash amount' and 'Invoice Total'

the ROUND function does not see to work....also can one get a comma in this as well for the thousands ??




DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =
N'<H1>Beltek Sales</H1>' +
N'<tr><th>Date: </th>'+ (SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]) +
N'<tr><th> Total Sales: </th>'+ (select sum (CAST(invoicetotal as decimal(10,4)))from beltekSales) +
N'<table border="1">' +
N'<tr><th>Sales Rep Name</th><th>Customer Number</th><th>Bill To</th><th>Bill Name</th><th>Cash Amount</th><th>Total Tax</th><th>Invoice Tax</th>' +
CAST ( (select td=Salesrepname, '',
td=Customerno, '',
td=Billto, '',
td=BillName, '',
td=CashAmount, '',
td=Tax1total, '',
td=InvoiceTotal
from beltekSales
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;


The table looks like this

CREATE TABLE [dbo].[BeltekSales](
[WarehouseNo] [varchar](6) NULL,
[Salesrepname] [varchar](30) NULL,
[Customerno] [varchar](8) NULL,
[Billto] [varchar](30) NULL,
[BillName] [varchar](30) NULL,
[CashAmount] [money] NULL,
[Tax1total] [money] NULL,
[InvoiceTotal] [money] NULL
) ON [PRIMARY]

DATA LOOKS LIKE THIS


Sales Rep Name Customer Number Invoice No Bill Name Sale Amount Vat Amount Invoice Total
ABINASH RAMPERSAD 1378000 90901524 CASH CUSTOMER 57.4000 8.6100 66.0100
ABINASH RAMPERSAD 1378000 90901525 CASH CUSTOMER 167.6400 8.8000 176.4400
ABINASH RAMPERSAD 1378000 90901526 CASH CUSTOMER 436.0000 0.0000 436.0000
ABINASH RAMPERSAD 1378000 90901527 CASH CUSTOMER 333.5000 50.0300 383.5300
ABINASH RAMPERSAD 1378000 90901528 CASH CUSTOMER 78.3100 11.7500 90.0600
ABINASH RAMPERSAD 1378000 90901529 CASH CUSTOMER 50.1600 7.5200 57.6800


WOULD LIKE IT TO LOOK LIKE THIS

Sales Rep Name Customer Number Invoice No Bill Name Sale Amount Vat Amount Invoice Total
ABINASH RAMPERSAD 1378000 90901524 CASH CUSTOMER 57.40 8.61 66.01
ABINASH RAMPERSAD 1378000 90901525 CASH CUSTOMER 167.64 8.80 176.44
ABINASH RAMPERSAD 1378000 90901526 CASH CUSTOMER 436.00 0.00 436.00
ABINASH RAMPERSAD 1378000 90901527 CASH CUSTOMER 333.50 50.03 383.53
ABINASH RAMPERSAD 1378000 90901528 CASH CUSTOMER 78.31 11.75 90.06
ABINASH RAMPERSAD 1378000 90901529 CASH CUSTOMER 50.16 7.52 57.68



Any ideas as to what I should do ???
JASBRYDEN
New Member
New Member

--
18 Sep 2009 10:12 AM
nope that does not work.

It works fine in SQL, but for some reason when it has to add it to the table it does not work....I am getting 4 decimal places.

Not sure how to correct that.


Acceptable Use Policy
---