Solved

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

Posted on 2009-07-02
2
268 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)
  • 2
2 Comments
 
LVL 42

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 42

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

912 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

24 Experts available now in Live!

Get 1:1 Help Now