Solved

Two Query Results In One HTML Email

Posted on 2012-03-26
3
402 Views
Last Modified: 2012-03-26
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).
 Exmple OutputThanks for the help.

- Bart
0
Comment
Question by:BartWestphal
3 Comments
 
LVL 18

Accepted Solution

by:
lludden earned 250 total points
ID: 37767969
It would help to have the query you want to add.  To make it simple. you could duplicate everything and change the variable name in the 2nd then concatenate the two.  This will make an email with the same table twice.  Change either query to make show the data you want.

DECLARE @tableHTML1  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>'
   

DECLARE @tableHTML2  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>'

   DECLARE @tableHTML  NVARCHAR(MAX)  = @tableHTML1 + @tableHTML2

Open in new window

0
 
LVL 9

Assisted Solution

by:OCDan
OCDan earned 250 total points
ID: 37767971
Just extend your @tablehtml variable. e.g.

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>'
+ N'<br>'
+ N'<br>'
+all the stuff you had before except with new table data.

You should then get in the email one table a couple of line breaks and then one more table.

Hope that helps.
0
 

Author Closing Comment

by:BartWestphal
ID: 37768520
This was the best and most helpful feedback.  Thank you both.  I couldn't do what I wanted with just one of the answers, it took both.  The:
+ 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!!!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question