Using CSS to control formatting in dbmail

Transact-SQL
1

I have written a query and currently have a job that runs and sends an email using sp_send_dbmail in a formatted table if certain conditions are met. I use style sheets to control the format and this works fine for all columns returned using the style for td but I have a need to control the format of individual columns of data only, i.e. control the text alignment, data type, color, etc. of the value column data independently of the column header. I would like to format the individual columns below to a currency if possible and right justify them. Even if I can not format to currency, if i could format to a number format with a comma separated value (XX,XXX) I can concatenate the $ sign. I have attached a snippet of the table output as well as the code.

FYI- there is another table below the summary table shown here but I did not send it. Much similar in nature, I just need guidance on getting the style sheet embedded to work.

I found during my search on the web a query written almost identical to mine with the same issue and the response was as follows:
Note: create a css classes for each td above.

CAST
(( SELECT
[td/@class]='anyclassname1', td = [Column Name 1], ""='',
[td/@class]='anyclassname2', td = [Column Name 2], ""='', etc.

I have tried this and substituted [td/@class]='text-align: right', and can not get it to work.
Any help would be greatly appreciated.

DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableSummary nvarchar(max)

SET @subject = 'Matter Alert for 189274'

SET @tableSummary =
N'
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: left;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
padding: 5px;
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
padding: 5px;
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd) { background-color:#eee; }
tr:nth-child(even) { background-color:#fff; }
'+
N'

Matter Management Alert

' +
N'' +
N'

' + CAST ( (

SELECT
td = cast(sum(itdr.[Base Amt]) as numeric(25,2)),'',
td = cast(sum(itdr.[A Rate Value]) as numeric(25,2)),'',
td = cast(sum(itdr.[C Rate Value]) as numeric(25,2)),'',
td = cast( sum(
CASE WHEN itdr.[Employee Code] = 'TLW'
THEN (itdr.[Base Hrs] * 125)
ELSE (itdr.[Base Amt])
END
) as numeric(25,2)),'',
td = '5000'
FROM Intl_Tax_Data_Run itdr
where itdr.[Matter Code] = '189274'
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +

N'

Base Rate Value (B) Value at A Rate Value at C Rate Value at $125/Hr Threshold
'

declare @nbody varchar(max)

set @nbody = @tableSummary

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileName',
@recipients='stevesmith@abcco.com',
@from_address = 'stevesmith@abcco.com',
@subject = @subject,
@body = @nbody,
@body_format = 'HTML' ;

Table

2

Sorry for the delayed response but I found the solution. When I converted the td to money, it right aligned the columns. It also did this when I concatenated the $ and added the "," inside the 5000.

In the latter part of the query which is not written above, I did have to do some tweaking to the query. It has both varchar and numeric columns in the td. I had to redefine the classes of my table. See below:

DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @Style nvarchar(max)= '';
DECLARE @tableHTML nvarchar(max)= '';

SET @subject = 'Matter Alert for 101853'

SET @Style +=

  • N'' +
    N'.tg
    {
    font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
    font-size: 12px;
    text-align: left;
    border-collapse: collapse;
    border-top: 7px solid #9baff1;
    border-bottom: 7px solid #9baff1;
    }
    ' + N'.tg td
    {
    padding: 5px;
    border-right: 1px solid #aabcfe;
    border-left: 1px solid #aabcfe;
    border-bottom: 1px solid #aabcfe;
    color: #669;
    }
    ' + N'.tg th
    {
    padding: 5px;
    font-size: 13px;
    font-weight: normal;
    background: #b9c9fe;
    border-right: 2px solid #9baff1;
    border-left: 2px solid #9baff1;
    border-bottom: 2px solid #9baff1;
    color: #039;
    }

' + N' tr:nth-child(odd) { background-color:#eee; }
' + N' tr:nth-child(even) { background-color:#fff; }
' + N'
' + N''
;

SET @tableHTML += @Style + @tableHTML +
N'

' --DEFINE TABLE
/*
Define Column Headers and Column Span for each Header Column
*/

  • N'
'

/*
Define each th
*/

  • N'
'
  • N'
    • '

  • /*
    Define data for table and cast to xml
    */

    CAST ( (

    SELECT
    'left' as 'td/@align', td = itdr.[Employee Name],'',
    'right' as 'td/@align', td = '$' + PARSENAME(CONVERT(VARCHAR,CAST(cast(sum(itdr.[Base Amt]) as numeric(25,2)) AS MONEY),1),2)
    FROM Intl_Tax_Data_Run itdr
    where itdr.[Matter Code] = '189274'
    group by itdr.[Employee Name]FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'

    Employee Name Base Rate Value
    '

    declare @nbody varchar(max)

    set @nbody = @tableHTML

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileName',
    @recipients='s_smith@sample.com',
    @from_address = 's_smith@sample.com',
    @subject = @subject,
    @body = @nbody,
    @body_format = 'HTML' ;