locked
Change font colour in table cell based on SQL Query in SQL Email RRS feed

  • Question

  • Hi All, I am generating a Report through SQL that emails a set of data results in a HTML table to Fee Earners in our firm. I would like to know, how I can set the font to red when the result "Total" column is greater than $10,000. The SQL code I have for the Email body is as follows:

    SET @tableHTML = N'<h2 style=''font-family:arial;color:#048a55;''>Fees and Disbursements Report</h2>' + N'<p style=''font-family:arial;color:#555555;''>This is an automated email. Please review your Fees and Disbursements Report.<br/><br/>Thank you.<br/>Account Team</p>' +

    N'<table style=''text-align:center;font-family:arial;color:#555555;font-size:15px'' border="1">' + N'<tr><th style="background-color: #05fcb6;">Client Code</th><th style="background-color: #05fcb6;">Client Name</th><th style="background-color: #05fcb6;">Matter Code</th> <th style="background-color: #05fcb6;">Matter Description</th><th style="background-color: #05e7fc;">Total Fees</th> <th style="background-color: #05e7fc;">Total Disbursements</th><th style="background-color: #05e7fc;">Total</th></tr>' +

    CAST ( ( SELECT td = CLIENT_CODE, '', 'left' AS 'td/@align', td = CLIENT_NAME, '', td = MATTER_CODE, '', 'left' AS 'td/@align', td = LONG_MATT_NAME, '', 'right' AS 'td/@align', td = '$' + FORMAT(TOTAL_FEES, 'N2'), '', 'right' AS 'td/@align', td = '$' + FORMAT(TOTAL_DISB, 'N2'), '', 'right' AS 'td/@align', td = '$' + FORMAT(TOTAL, 'N2'), '' FROM #WIP_DISB_EMAIL WHERE EMAIL = @Email ORDER BY CLIENT_CODE, TOTAL_FEES DESC FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) + N'</table>' + N'</br></br>' + 

    Any Help would be appreciated.

    Regards

    Tony

    Any help would be greatly appreciated. Regards Tony

    Wednesday, May 20, 2020 1:21 AM

Answers

  • Hi Tony,

    Please check my answer in this post: HTML Code Inside SQL

    • Marked as answer by Tony Velarde Wednesday, May 20, 2020 4:40 AM
    Wednesday, May 20, 2020 1:50 AM
  • Hi Tony,

    If you noticed, I suggested a different approach in the link.

    It is a two step process:

    1. Generate raw XML out of relational table.
    2. Compose (X)HTML via visual template.

    This way you see (X)HTML visually, instead of just string concatenation via T-SQL.

    You can copy the solution from the link to your SSMS and see how it works.

    I am using there 'US states and cities' example. It includes even a screen shot of it.

    Wednesday, May 20, 2020 3:52 AM

All replies

  • Hi Tony,

    Please check my answer in this post: HTML Code Inside SQL

    • Marked as answer by Tony Velarde Wednesday, May 20, 2020 4:40 AM
    Wednesday, May 20, 2020 1:50 AM
  • Hi Yitzhak,

    Thanks for the link.

    I amended my query section to look this:

    CAST ( ( SELECT 
    td = CLIENT_CODE, '',
    'left' AS 'td/@align',
    td = CLIENT_NAME, '',
    td = MATTER_CODE, '',
    'left' AS 'td/@align',
    td = LONG_MATT_NAME, '', 
    'right' AS 'td/@align',
    td = '$' + FORMAT(TOTAL_FEES, 'N2'), '',
    'right' AS 'td/@align',
    td = '$' + FORMAT(TOTAL_DISB, 'N2'), '',
    'right' AS 'td/@align',
    td = '$' + FORMAT(TOTAL, 'N2'), '',
    td = '<td><span style="' + CASE WHEN Total >= 10000 THEN 'color:red;' ELSE 'color:black;'
    END + '">' + TOTAL + '</span></td></tr>'
    FROM #WIP_DISB_EMAIL 
    WHERE EMAIL = @Email
    ORDER BY CLIENT_CODE, TOTAL_FEES DESC

    But now I'm getting "Error converting data type varchar to numeric" error. The temp table Columns are DECIMAL(25,2) that I'm extracting the data from, so I'm not sure why it's failing. Could it be "@tableHTML NVARCHAR(MAX)"? The error mentions line 58 of the script which is were " SET @tableHTML = ..."  I'm a novice in SQL and Html so any help would be appreciated.

    Regards

    Tony


    Wednesday, May 20, 2020 3:22 AM
  • Hi Tony,

    If you noticed, I suggested a different approach in the link.

    It is a two step process:

    1. Generate raw XML out of relational table.
    2. Compose (X)HTML via visual template.

    This way you see (X)HTML visually, instead of just string concatenation via T-SQL.

    You can copy the solution from the link to your SSMS and see how it works.

    I am using there 'US states and cities' example. It includes even a screen shot of it.

    Wednesday, May 20, 2020 3:52 AM
  • Ah ha! Excellent! Thanks for that Yitzhak.
    Wednesday, May 20, 2020 4:40 AM
  • SQL and client/server computing in general is based on the idea of tiers in the architecture. One tier is the database and retrieves the data in a standard format. It then passes that data to a presentation tier, which does all the coloring and display work and all the things that you want to do for human consumption. The architecture may also pass that data to a statistical analysis program, a cell phone program or anything else. Basically you are writing a monolithic program using SQL that does everything! This is 1960s COBOL, not any modern programming technique. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, May 20, 2020 4:17 PM