Color Code Based on Value in Sql Tabular Formatted Email

Question

I have working code that sends an HTML tabular email, I just need help color coding specific results in the HTML code if they are under 0.

   set datefirst 1;
   set nocount on;

   if (object_id('tempdb..#temp') iS NOT NULL)
             drop table #temp

   declare @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX);
   USE PL00
   select w.WeekDayID,w.[WeekDay] + FORMAT(convert(date,convert(varchar(10),dateadd(dd,w.WeekDayID-DATEPART(dw,getdate()),GETDATE()),121)),' (MM/dd)') as [WeekDay]
             ,SUM(CASE o.[TYPE] WHEN 'SVC' THEN o.SUB_TOTAL ELSE 0.0 END) as TotalSales,COUNT(DISTINCT CASE o.[TYPE] WHEN 'SVC' THEN o.ORDER_NO ELSE NULL END) as TotalOrders
                   ,MAX(CASE o.[TYPE] WHEN 'SVC' THEN e.U_DLYSALES ELSE 0.0 END) as SalesGoal
   into #temp
   from (select 1 as WeekDayID,'Monday' as [WeekDay] union select 2,'Tuesday' union select 3,'Wednesday' union select 4,'Thursday' union select 5,'Friday' union select 6,'Saturday' union select 7,'Sunday') as w
             left join schedule s on w.WeekDayID = DATEPART(dw,s.[DATE]) inner join ordhdr o on s.ID_VAL = o.ORDER_NO left join employee e on o.leadtech = e.emp_id
   where s.[DATE] between convert(datetime,convert(varchar(10),dateadd(dd,1-DATEPART(dw,getdate()),GETDATE()),121)) and convert(datetime,convert(varchar(10), GETDATE(),121))
   --     and o.[TYPE] = 'SVC'
             and e.EMP_ID = '45-ANGORTI'
   group by w.WeekDayID,w.[WeekDay]
   union 
   select 8 as WeekDayID,'Weekly' as [WeekDay], SUM(o2.SUB_TOTAL) as TotalSales,COUNT(DISTINCT o2.ORDER_NO) as TotalOrders,MAX(ISNULL(e.U_WKSALES,0.0)) as SalesGoal
   from ordhdr o2 inner join schedule s2 on s2.ID_VAL = o2.ORDER_NO left join employee e on o2.leadtech = e.emp_id
                         where s2.[DATE]  between convert(datetime,convert(varchar(10),dateadd(dd,1-DATEPART(dw,getdate()),GETDATE()),121))  
                                      and convert(datetime,convert(varchar(10),GETDATE(),121))
                                     and o2.[TYPE] = 'SVC'
             and e.EMP_ID = '45-ANGORTI'
   union 
   select 9 as WeekDayID,'Monthly' as [WeekDay], SUM(o2.SUB_TOTAL) as TotalSales,COUNT(DISTINCT o2.ORDER_NO) as TotalOrders,MAX(ISNULL(e.U_MONSALES,0.0)) as SalesGoal
   from ordhdr o2 inner join schedule s2 on s2.ID_VAL = o2.ORDER_NO left join employee e on o2.leadtech = e.emp_id
                         where s2.[DATE]  between convert(datetime,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))  
                                      and convert(datetime,convert(varchar(10),GETDATE(),121))
                                     and o2.[TYPE] = 'SVC'
             and e.EMP_ID = '45-ANGORTI'

   insert #temp
   select 
             w.WeekDayID, w.[WeekDay] + FORMAT(convert(date,convert(varchar(10),dateadd(dd,w.WeekDayID-DATEPART(dw,getdate()),GETDATE()),121)),' (MM/dd)') as [WeekDay]
             ,0.0 as TotalSales,0 as TotalOrders,0.0 as SalesGoal
   from (select 1 as WeekDayID,'Monday' as [WeekDay] union select 2,'Tuesday' union select 3,'Wednesday' union select 4,'Thursday' union select 5,'Friday' union select 6,'Saturday' union select 7,'Sunday') as w
             left join #temp t on w.WeekDayID = t.WeekDayID
   where t.WeekDayID is null

   --delete #temp where WeekDayID >= DATEPART(dw,getdate())

   --select e.U_DLYSALES,e.U_WKSALES,e.U_MONSALES, * from employee e where e.U_DLYSALES > 0.0
   select * from #temp order by WeekDayID


DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT #temp.[WeekDay] AS 'td','',  #temp.TotalSales AS 'td','',#temp.TotalOrders AS 'td','',#temp.SalesGoal  AS 'td', '',
(#temp.TotalSales -  #temp.SalesGoal) AS 'td'


from #temp
order by WeekDayID
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body><H3> TEST. </H3>
<table border = 1> 
<tr>
<th> Weekday </th> <th> TotalSales </th> <th> Total ORders </th> <th> SalesGoal </th>  <th> Difference </th>  </tr>'    

SET @body = @body + @xml +'</table></body></html>'


EXEC msdb.dbo.sp_send_dbmail
@profile_name =  'mail.pacificlawnsprinklers.com',
@body = @body,
@body_format ='HTML',
@recipients = 'Mmahoney@pacificlawnsprinklers.com', -- replace with your email address
@subject = 'TEST' ;

TEST.

+-------------------+-------------+---------------+------------+-------------+
|     Weekday       | TotalSales  | Total ORders  | SalesGoal  | Difference  |
+-------------------+-------------+---------------+------------+-------------+
| Monday (08/05)    |     2570.00 |             1 |    1000.00 |     1570.00 |
| Tuesday (08/06)   |     1072.00 |             6 |    1000.00 |       72.00 |
| Wednesday (08/07) |        0.00 |             0 |       0.00 |        0.00 |
| Thursday (08/08)  |        0.00 |             0 |       0.00 |        0.00 |
| Friday (08/09)    |        0.00 |             0 |       0.00 |        0.00 |
| Saturday (08/10)  |        0.00 |             0 |       0.00 |        0.00 |
| Sunday (08/11)    |        0.00 |             0 |       0.00 |        0.00 |
| Weekly            |     3642.00 |             7 |    6000.00 |    -2358.00 |
| Monthly           |     6244.00 |            13 |   25000.00 |   -18756.00 |
+-------------------+-------------+---------------+------------+-------------+

You'll have more responses if you make a [minimal](https://stackoverflow.com/help/minimal-reproducible-example) example. — pwilcox, Aug 06 '19 at 20:16
Have you looked at this question? https://stackoverflow.com/questions/50066586/formating-color-in-sql-server-using-for-xml-path — LaraRaraBoBara, Aug 06 '19 at 22:22

score 0 · Answer 1 · answered Aug 08 '19 at 01:31

You can check for zero values and encasing these in a span and give that a color.

Example:

<span style="font-color:#f00">0</span>

Since you are doing it for an email, the only global support is adding the color inline at the element level or at the data level.

You can add a class but some email clients don't read CSS in the head or they stripped out. If you want to go the route of classes, then below is what you can do.

.red{color:#f00;}

<span class="red">0</span>

Color Code Based on Value in Sql Tabular Formatted Email

1 Answers1