Solved

Two Query Results In One HTML Email

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach 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.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

920 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

12 Experts available now in Live!

Get 1:1 Help Now