Solved

How to send an email to each unique value in a field

Posted on 2009-07-02
2
278 Views
Last Modified: 2012-06-21
i'm running a query as a job in SQL Server 2005.  this query will send an html report to customers and show all of their pending orders.

i have a field for customer number (CustomerNo)
each customer only has one email address.
but the customer may have several orders and they should receive only 1 email with all orders on it.

how do i do the iteration?
USE [Database]
GO
IF (OBJECT_ID('#tmpEmailStatus') IS NOT NULL)
DROP TABLE #tmpEmailStatus
 
CREATE TABLE #tmpEmailStatus(
	SalesOrderNo char(7) NOT NULL,
	CustomerNo char(7) NULL,
	EmailAddress varchar(50) NOT NULL,
	PackageNo varchar(4) NULL,
	TrackingID varchar(30) NULL,
	ShipToCity varchar(20) NULL,
	ShipToState char(2) NULL,
	ShipVia varchar(15) NULL,
	ItemCode varchar(15) NULL,
	QuantityShipped smallint NULL,
	QuantityOrderedRevised smallint NULL,
	FreightAmt decimal(12,5) NULL,
	PromiseDate datetime NULL
)
INSERT INTO #tmpEmailStatus(
	SalesOrderNo, CustomerNo, EmailAddress, PackageNo, TrackingID, ShipToCity, ShipToState,ShipVia,ItemCode,QuantityShipped,QuantityOrderedRevised,FreightAmt,PromiseDate
)
SELECT     SalesOrderNo, CustomerNo, EmailAddress, PackageNo, TrackingID, ShipToCity, ShipToState,ShipVia,ItemCode,QuantityShipped,QuantityOrderedRevised,FreightAmt,PromiseDate
FROM         dbo.vEmailShippingStatus
GROUP BY SalesOrderNo, CustomerNo, EmailAddress, PackageNo, TrackingID, ShipToCity, ShipToState,ShipVia,ItemCode,QuantityShipped,QuantityOrderedRevised,FreightAmt,PromiseDate
 
DECLARE @EmailRecipient VARCHAR(50);
SELECT @EmailRecipient = 'SELECT EmailAddress from dbo.vEmailShippingStatus';
 
DECLARE @tableHTML  NVARCHAR(MAX) ;
 
SET @tableHTML =
    N'<H3>Order Status</H3>' +
		N'<div><H5>Execution Date: <font color="blue">' + CONVERT(NVARCHAR(30), getdate(), 113) + ' PST' + '</font></H5></div>'+ 
    N'<table border="1">' +
    N'<tr><th>SalesOrder</th>' +
    N'<th>CustomerNo</th>' +
    N'<th>PackageNo</th>' +
    N'<th>TrackingID</th>' +
    N'<th>ShipToCity</th>' +
    N'<th>ShipToState</th>' +
    N'<th>ShipVia</th>' +
    N'<th>ItemCode</th>' +
    N'<th>QuantityShipped</th>' +
    N'<th>QuantityOrderedRevised</th></tr>' +
    CAST ( ( SELECT td = SalesOrderNo,       '',
                    td = CustomerNo,		 '',
					td = PackageNo,			 '',
					td = TrackingID,		 '',
					td = ShipToCity,		 '',
					td = ShipToState,		 '',
					td = ShipVia,			 '',
					td = ItemCode,			 '',
					td = QuantityShipped,	 '',
					td = QuantityOrderedRevised
              FROM #tmpEmailStatus
 
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;
 
drop table #tmpEmailStatus
 
EXEC msdb.dbo.sp_send_dbmail @recipients=@EmailRecipient,
    @subject = 'OverDue Sales Orders',
    @body = @tableHTML,
    @body_format = 'HTML',
@profile_name='Email';

Open in new window

0
Comment
Question by:zephyr_hex (Megan)
[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
  • 2
2 Comments
 
LVL 43

Author Comment

by:zephyr_hex (Megan)
ID: 24767173
ok, i have a partial solution.  

i created a view that has a list of unique customer number, email address and use a CURSOR to iterate through it.

the only thing missing is how to SELECT only the records where CustomerNo is equal to the current record that the cursor is pointing to.

do i need a second cursor to point to the customer number or can i select the customer number column from the current cursor?
USE [Database]
GO
 
DECLARE @EmailRecipient VARCHAR(50);
DECLARE db_cursor CURSOR FOR
SELECT EmailAddress from dbo.ViewOfUniqueCustomerNumberEmailAddress;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @EmailRecipient;
 
WHILE @@FETCH_STATUS=0
BEGIN
IF (OBJECT_ID('#tmpEmailStatus') IS NOT NULL)
DROP TABLE #tmpEmailStatus
 
CREATE TABLE #tmpEmailStatus(
	SalesOrderNo char(7) NOT NULL,
	CustomerNo char(7) NULL,
	EmailAddress varchar(50) NOT NULL,
	PackageNo varchar(4) NULL,
	TrackingID varchar(30) NULL,
	ShipToCity varchar(20) NULL,
	ShipToState char(2) NULL,
	ShipVia varchar(15) NULL,
	ItemCode varchar(15) NULL,
	QuantityShipped smallint NULL,
	QuantityOrderedRevised smallint NULL,
	FreightAmt decimal(12,5) NULL,
	PromiseDate datetime NULL
)
INSERT INTO #tmpEmailStatus(
	SalesOrderNo, CustomerNo, EmailAddress, PackageNo, TrackingID, ShipToCity, ShipToState,ShipVia,ItemCode,QuantityShipped,QuantityOrderedRevised,FreightAmt,PromiseDate
)
SELECT     SalesOrderNo, CustomerNo, EmailAddress, PackageNo, TrackingID, ShipToCity, ShipToState,ShipVia,ItemCode,QuantityShipped,QuantityOrderedRevised,FreightAmt,PromiseDate
FROM         dbo.vEmailShippingStatus
GROUP BY SalesOrderNo, CustomerNo, EmailAddress, PackageNo, TrackingID, ShipToCity, ShipToState,ShipVia,ItemCode,QuantityShipped,QuantityOrderedRevised,FreightAmt,PromiseDate
 
 
 
DECLARE @tableHTML  NVARCHAR(MAX) ;
 
SET @tableHTML =
    N'<H3>Order Status</H3>' +
		N'<div><H5>Execution Date: <font color="blue">' + CONVERT(NVARCHAR(30), getdate(), 113) + ' PST' + '</font></H5></div>'+ 
    N'<table border="1">' +
    N'<tr><th>SalesOrder</th>' +
    N'<th>CustomerNo</th>' +
    N'<th>PackageNo</th>' +
    N'<th>TrackingID</th>' +
    N'<th>ShipToCity</th>' +
    N'<th>ShipToState</th>' +
    N'<th>ShipVia</th>' +
    N'<th>ItemCode</th>' +
    N'<th>QuantityShipped</th>' +
    N'<th>QuantityOrderedRevised</th></tr>' +
    CAST ( ( SELECT td = SalesOrderNo,       '',
                    td = CustomerNo,		 '',
					td = PackageNo,			 '',
					td = TrackingID,		 '',
					td = ShipToCity,		 '',
					td = ShipToState,		 '',
					td = ShipVia,			 '',
					td = ItemCode,			 '',
					td = QuantityShipped,	 '',
					td = QuantityOrderedRevised
              FROM #tmpEmailStatus
 
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;
 
drop table #tmpEmailStatus
 
EXEC msdb.dbo.sp_send_dbmail @recipients=@EmailRecipient,
    @subject = 'OverDue Sales Orders',
    @body = @tableHTML,
    @body_format = 'HTML',
@profile_name='Email';
FETCH NEXT FROM db_cursor INTO @EmailRecipient;
END
 
CLOSE db_cursor;
DEALLOCATE db_cursor;

Open in new window

0
 
LVL 43

Accepted Solution

by:
zephyr_hex (Megan) earned 500 total points
ID: 24767243
ok, i got it.

i figured out how to use the current cursor, assign both the current customer number and email address to variables, and then send one email to each customer.
USE [Database]
GO
 
DECLARE @EmailRecipient VARCHAR(50);
DECLARE @Customer char(7);
DECLARE db_cursor CURSOR FOR
SELECT CustomerNo,EmailAddress from dbo.ViewOfUniqueCustomerNumberEmailAddress;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @Customer,@EmailRecipient;
 
WHILE @@FETCH_STATUS=0
BEGIN
 
IF (OBJECT_ID('#tmpEmailStatus') IS NOT NULL)
DROP TABLE #tmpEmailStatus
 
CREATE TABLE #tmpEmailStatus(
	SalesOrderNo char(7) NOT NULL,
	CustomerNo char(7) NULL,
	EmailAddress varchar(50) NOT NULL,
	PackageNo varchar(4) NULL,
	TrackingID varchar(30) NULL,
	ShipToCity varchar(20) NULL,
	ShipToState char(2) NULL,
	ShipVia varchar(15) NULL,
	ItemCode varchar(15) NULL,
	QuantityShipped smallint NULL,
	QuantityOrderedRevised smallint NULL,
	FreightAmt decimal(12,5) NULL,
	PromiseDate datetime NULL
)
INSERT INTO #tmpEmailStatus(
	SalesOrderNo, CustomerNo, EmailAddress, PackageNo, TrackingID, ShipToCity, ShipToState,ShipVia,ItemCode,QuantityShipped,QuantityOrderedRevised,FreightAmt,PromiseDate
)
SELECT     SalesOrderNo, CustomerNo, EmailAddress, PackageNo, TrackingID, ShipToCity, ShipToState,ShipVia,ItemCode,QuantityShipped,QuantityOrderedRevised,FreightAmt,PromiseDate
FROM         dbo.vEmailShippingStatus
WHERE CustomerNo=@Customer
GROUP BY SalesOrderNo, CustomerNo, EmailAddress, PackageNo, TrackingID, ShipToCity, ShipToState,ShipVia,ItemCode,QuantityShipped,QuantityOrderedRevised,FreightAmt,PromiseDate
 
 
 
DECLARE @tableHTML  NVARCHAR(MAX) ;
 
SET @tableHTML =
    N'<H3>Order Status</H3>' +
		N'<div><H5>Execution Date: <font color="blue">' + CONVERT(NVARCHAR(30), getdate(), 113) + ' PST' + '</font></H5></div>'+ 
    N'<table border="1">' +
    N'<tr><th>SalesOrder</th>' +
    N'<th>CustomerNo</th>' +
    N'<th>PackageNo</th>' +
    N'<th>TrackingID</th>' +
    N'<th>ShipToCity</th>' +
    N'<th>ShipToState</th>' +
    N'<th>ShipVia</th>' +
    N'<th>ItemCode</th>' +
    N'<th>QuantityShipped</th>' +
    N'<th>QuantityOrderedRevised</th></tr>' +
    CAST ( ( SELECT td = SalesOrderNo,       '',
                    td = CustomerNo,		 '',
					td = PackageNo,			 '',
					td = TrackingID,		 '',
					td = ShipToCity,		 '',
					td = ShipToState,		 '',
					td = ShipVia,			 '',
					td = ItemCode,			 '',
					td = QuantityShipped,	 '',
					td = QuantityOrderedRevised
              FROM #tmpEmailStatus
 
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;
 
drop table #tmpEmailStatus
 
EXEC msdb.dbo.sp_send_dbmail @recipients=@EmailRecipient,
    @subject = 'OverDue Sales Orders',
    @body = @tableHTML,
    @body_format = 'HTML',
@profile_name='Email';
FETCH NEXT FROM db_cursor INTO @Customer,@EmailARecipient;
END
 
CLOSE db_cursor;
DEALLOCATE db_cursor;

Open in new window

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

617 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