BartWestphal
asked on
Two Query Results In One HTML Email
I have the following code that I use to generate an email each morning that provides the dollar amount of shipping revenue from the prior day. This works great and I don't want to change it. What I do need to do is add a second query that will output it's results either above or below the table that is generated by the existing code. My problem is that I don't know where to put the extra code (if it's even possible).
Here's the code
-------------------------- ---------- ---------- ---------- --------
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<h2 style="color:black">Daily Shipping Revenue</h2>' +
N'<table border="1" ALIGN="left">' +
N'<tr align="left"><th>Date</th> ' +
N'<th>Shipping Revenue</th></tr>' +
CAST ((SELECT td = convert(varchar, FIN_TRANS_DT,101), ' ',
td = SUM(FIN_TRANS_AMT)
FROM Eco.dbo.ShippingRevenue
GROUP BY FIN_TRANS_DT
HAVING (FIN_TRANS_DT = CONVERT(varchar, DATEADD(dd, - 1, GETDATE()), 101))
FOR XML PATH('tr'), TYPE )
AS NVARCHAR(MAX)) +
N'</table>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ServiceUser',
@recipients = 'bw@abc.com',
@body = @tableHTML,
@subject = 'Daily Shipping Revenue' ,
@body_format = 'HTML'
-------------------------- ---------- ---------- ---------- --------
An example of the required output (the top portion exists, it's the pink stuff that I want to add). It could be an additional table or simply a value (such as a sum or count).
Thanks for the help.
- Bart
Here's the code
--------------------------
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<h2 style="color:black">Daily Shipping Revenue</h2>' +
N'<table border="1" ALIGN="left">' +
N'<tr align="left"><th>Date</th>
N'<th>Shipping Revenue</th></tr>' +
CAST ((SELECT td = convert(varchar, FIN_TRANS_DT,101), ' ',
td = SUM(FIN_TRANS_AMT)
FROM Eco.dbo.ShippingRevenue
GROUP BY FIN_TRANS_DT
HAVING (FIN_TRANS_DT = CONVERT(varchar, DATEADD(dd, - 1, GETDATE()), 101))
FOR XML PATH('tr'), TYPE )
AS NVARCHAR(MAX)) +
N'</table>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ServiceUser',
@recipients = 'bw@abc.com',
@body = @tableHTML,
@subject = 'Daily Shipping Revenue' ,
@body_format = 'HTML'
--------------------------
An example of the required output (the top portion exists, it's the pink stuff that I want to add). It could be an additional table or simply a value (such as a sum or count).
Thanks for the help.
- Bart
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
+ N'<br>'
+ N'<br>'
Part made it format properly, and the syntax of the first one got it most of the way there.
Thank you both!!!