0

I have code that will populate an html table in an email in SQL Server. Some of the fields are date fields. I want to set up an if statement saying that if the due date is within the past 30 days from today's date color the background of the cell yellow. If it is withing the past 60 days from today's date the background of the cell red. How would I be able to color just one cell?

Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' +

                             '<style>' +

                  'td {border: padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +

                  '</style>' +

                  '</head>' +

                  '<body><table cellpadding=0 cellspacing=0 border=0>' +

                  '<tr bgcolor=#FFEFD8><td align=left><b>Principal</b></td>' +

                  '<td align=left><b>Company</b></td>' +

                  '<td align=left><b>Action Item</b></td>' +

                  '<td align=left><b>Sales</b></td>' +

                             '<td align=left><b>Due Date</b></td>' +

                             '<td align=left><b>Updated</b></td>' +

                  '<td align=left><b>Owner</b></td></tr>';

    DECLARE @CNT as int, @SLS as NVARCHAR(10)
    select  [employeeid], [Sales], [Email Address]
    into #loctempemployee from tblEmployees  

    Set @CNT =  (Select COUNT (Distinct EmployeeID) from #loctempemployee)
           While (@CNT > 0) 

             begin

                Set @message=
                    (
                    SELECT tr.Principal As [TD], tr.[Company Name] As [TD], ai.[Action Item] As [TD], ai.Owners As [TD], LEFT((ai.[Due Date]),10) As [TD], LEFT((ai.Updated),10) As [TD], em.Sales As [TD]
                    FROM [tblActionItem] ai
                    INNER JOIN tblTripReport tr ON ai.TripReportID = tr.tripreportID
                    INNER JOIN tblCustomers cu ON cu.CustomerID = tr.[Customer ID]
                    INNER JOIN tblEmployees em ON em.EmployeeID = cu.EmployeeID
                    WHERE em.Sales = (Select sales from #loctempemployee Where EmployeeID = (Select top 1 EmployeeID from #loctempemployee))
                    For XML raw('tr'), Elements
                    )





                Select @Body = (@message)




                Set @Body = Replace(@Body, '_x0020_', space(1))

                Set @Body = Replace(@Body, '_x003D_', '=')

                Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')

                Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')



                Select @Body = @TableHead + @Body + @TableTail
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user9448003
  • 87
  • 1
  • 10
  • Using just HTML this is not possible. You will need to get very involved with embedding some jQuery/javascript/css and honestly at that point you're better off just throwing this email into a standalone executable with some .net behind it. – haag1 Jan 22 '19 at 20:25
  • 1
    Yes, and this is the best i have seen: https://stackoverflow.com/a/39487565/6167855 which you can edit however you want. I use it like so: https://github.com/scsimon/sqlserver/blob/master/ufn_createHTMLtable – S3S Jan 22 '19 at 20:26
  • You are just generating html to be interpreted by whatever email reader... you then just have to create an specific class for the td you want the color changed and add it on the TD given your logic. Something like this https://stackoverflow.com/q/6984227/460557 injected in your code – Jorge Campos Jan 22 '19 at 20:29
  • @scsimon Noticed you have a couple of my functions in your GIT... I'm honored that I made the cut and pleased you kept the attribution. :) – John Cappelletti Jan 22 '19 at 20:59
  • 1
    haha i told you i added them to my collection! Only makes sense to give credit where credit is due ;) @JohnCappelletti – S3S Jan 22 '19 at 21:22

0 Answers0