I have a query that sends the results to my email in a tabular format. I am using this link as a reference. https://www.mssqltips.com/sqlservertip/2347/send-email-in-a-tabular-format-using-sql-server-database-mail/
I am able to successfully send the email with the data I want, but the values are not the way I want it.
When I run the query with out using HTML I get the right formatted values.
What do I need to do in the script that will get me the fixed values and not the scientific notation when implementing a html tabular format? Any help is most appreciated.
DROP TABLE #timetot
DECLARE @intVar INT
SET @intVar=0;
;
WITH ss
AS (SELECT a.rowid,
a.employee,
Sum(b.value) AS Total
FROM employeetimesheets b
RIGHT JOIN employees a
ON a.rowid = b.empid
WHERE b.day >= Dateadd("d", -6, '2015-10-10')
AND b.day <= Dateadd("d", -0, '2015-10-10')
GROUP BY a.rowid,
a.employee
UNION ALL
SELECT rowid,
employee,
@intVar AS Total
FROM employees
EXCEPT
SELECT empid,
employee,
@intVar AS Total
FROM employeetimesheets a
INNER JOIN employees b
ON a.empid = b.rowid
WHERE a.day >= Dateadd("d", -6, '2015-10-10')
AND a.day <= Dateadd("d", -0, '2015-10-10'))
SELECT *
INTO #timetot
FROM ss
DECLARE @xml NVARCHAR(max)
DECLARE @body NVARCHAR(max)
SET @xml = Cast((SELECT [rowid] AS 'td',
'',
[employee] AS 'td',
'',
[total] AS 'td',
''
FROM #timetot
ORDER BY rowid
FOR xml path('tr'), elements) AS NVARCHAR(max))
SET @body ='<html><body><H3>Employees with Un-finished or Null Timesheets</H3> <table border = 1> <tr> <th> RowId </th> <th> Employee </th> <th> Total </th> </tr>'
SET @body = @body + @xml + '</table></body></html>'
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = '***',
@body = @body,
@body_format ='HTML',
@recipients = '****.com',
@subject = 'E-mail in Tabular Format';
