Solved

Two Query Results In One HTML Email

Posted on 2012-03-26
3
399 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
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.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now