Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Two Query Results In One HTML Email

Posted on 2012-03-26
3
Medium Priority
?
412 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 18

Accepted Solution

by:
lludden earned 1000 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 1000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

618 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