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

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

LVL 44
zephyr_hex (Megan)DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zephyr_hex (Megan)DeveloperAuthor Commented:
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
zephyr_hex (Megan)DeveloperAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.