i generating report display in outlook with sql server only.(no editor used)
here codes. have column name "fee" in db. need sum total fee , display in html table "total fee".
as have using as [fee] 'td', hence how code this?
select sum(fee) 'total' into table data 'td' ?
how should 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 ytd 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 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] 'td','' ,[fee] 'td','' frmtblname [clientid] = 'mike' order [date] desc xml path('tr'), elements ) varchar(max)) set @body = @body + @ytd_header + @ytd +'</table>' --ending 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' ;
