Post reply

SQL_DB_SENDMAIL in HTML format question

  • July 22, 2015 at 10:38 am

    #310076

    I have a routine that generates an HTML email and sends it just fine, but one of the columns ends up with 4 decimal places for a column datatype of money. How can I get the script to output only 2 decimal places for the amount column from the select statement?

    Here's the script:

    declare @tableHTML nvarchar(max) ;

    set @tableHTML =

    N'<h1>Revenue Report</h1>' +

    N'<table border="1">' +

    N'<tr><th>Amount</th><th>Index</th><th>CompObj</th><th>Rev Type</th><th>Program</th>'+

    CAST ((SELECT td=SUM(dbo.tblAllocations.Amount),'',

    td=dbo.tblFunding.Index,'',

    td=dbo.tblFunding.CompOBJ,'',

    td=dbo.tblRevenue.RevType,'',

    td=dbo.tblfunding.deptname

    FROM dbo.tblAllocations INNER JOIN

    dbo.tblRevenue ON dbo.tblAllocations.LineNumber = dbo.tblRevenue.LineNumber INNER JOIN

    dbo.tblFunding ON dbo.tblAllocations.FundCode = dbo.tblFunding.FundCode

    WHERE (dbo.tblRevenue.RevType IN ('cash', 'check', 'currency', 'cashier check', 'wire', 'direct deposit', 'money order')) AND (dbo.tblrevenue.HoldOrVoid is null) AND(CAST(dbo.tblRevenue.Entered AS DATE)) = (cast(GETDATE() as date))

    GROUP BY dbo.tblRevenue.RevType, dbo.tblFunding.Index, dbo.tblFunding.Index, dbo.tblFunding.CompOBJ, dbo.tblfunding.deptname for XML path('tr'), TYPE) as nvarchar (max))+

    N'</table>'

    exec msdb.dbo.sp_send_dbmail

    @profile_name='SQL0',

    @recipients='blah',

    @subject='Daily Revenue',

    @body = @tableHTML,

    @body_format='HTML';

  • July 22, 2015 at 10:50 am

    #1814163

    the convert function has an optional parameter for style/number of decimals? when the converted value is a decimal/float.

    https://msdn.microsoft.com/en-us/library/ms187928.aspx

    money and smallmoney Styles

    When expression is money or smallmoney, style can be one of the values shown in the following table. Other values are processed as 0.

    Value

    Output

    0 (default)

    No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

    1

    Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

    2

    No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

    126

    Equivalent to style 2 when converting to char(n) or varchar(n)

    td = SUM(dbo.tblAllocations.Amount)

    changes to td = CONVERT(varchar,SUM(dbo.tblAllocations.Amount),0)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 22, 2015 at 10:56 am

    #1814164

    Excellent, thanks much

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply