Solved

Two Query Results In One HTML Email

Posted on 2012-03-26
3
403 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 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