I am generating report to display in Outlook with SQL Server ONLY.(no editor used)
Here is my codes. I have column name "Fee" in my DB. I need to sum the total fee and display in HTML table as "Total Fee".
As I have already using AS for [Fee] AS 'td', hence how do I code this?
Select SUM(Fee) as 'Total'
into table data 'td' ?
How should I code that? Experts please help. Thanks!
enter code here
USE MYS
DECLARE @YTD varchar (4000)
DECLARE @BODY NVARCHAR(MAX)
DECLARE @HTMLHEADER VARCHAR(100)
DECLARE @HTMLFOOTER VARCHAR(100)
--Start of HTML page
SET @HTMLHEADER='<html><body>'
SET @HTMLFOOTER ='</body></html>'
-- Set html header
SET @BODY = @HTMLHEADER
+ '<meta charset="UTF-8">'
+ '<style>'
+ 'td {border: solid black;
font: 12px arial} '
+ '</style>'
+ 'Report generated on : '
+ CONVERT(VARCHAR(50), GETDATE(), 106)
--set table layout for YTD with header
Declare @YTD_HEADER VARCHAR(MAX)
Set @YTD_HEADER = '<style>'
+ 'td {border: solid black;
border-width: 1px;
padding-left:5px;
padding-right:5px;
padding-top:1px;
padding-bottom:1px;
word-break: break-all;
word-wrap: break-word;
font: 12px arial} '
+ '</style>'
+ '<H3>Year to Date</H3><table class "YTD_CSS" border = 1 width="1000"><tr> <td bgcolor=#F0E68C><b>Client ID</b></td><td bgcolor=#F0E68C><b>Total Fee</b></td></tr>'
--select data
SET @YTD = CAST((
SELECT [ClientID] AS 'td',''
,[Fee] AS 'td',''
FROM frmTblName
WHERE [ClientID] = 'Mike'
ORDER BY [Date] DESC
FOR XML PATH('tr'), ELEMENTS ) AS VARCHAR(MAX))
SET @BODY = @BODY + @YTD_HEADER + @YTD +'</table>'
--ending for HTML page
SET @BODY = @BODY + @HTMLFOOTER
USE R_Email
--SEND EMAIL
EXEC msdb.dbo.sp_send_dbmail
@profile_name='ProfileName',
@body = @BODY,
@body_format ='HTML',
@recipients = 'mc@outlook.com.my',
@copy_recipients ='sauternes_chocg@hotmail.com',
@subject = 'Monthly Report' ;
