Solved

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

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

Author Comment

by:zephyr_hex
Comment Utility
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 earned 500 total points
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Copy Database Wizard 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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 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

18 Experts available now in Live!

Get 1:1 Help Now