Thanks for your response. I have fixed and posted the test data below. Also attached a text file with the same.
I am still unclear on the structure and how to code the nested cursors that you had suggested. I understand the concept - again it is the structure and coding that I am not getting. My stored procedure is also posted below.
Can you also explain and give an example of the new ANSI joins style versus what I am using now (??) ?
Thanks.
UPDATED AND FIXED TEST DATA:
------ If the test table already exists, drop it
IF OBJECT_ID('TempDB..#NOTE_tblNote', 'U') IS NOT NULL
DROP TABLE #NOTE_tblNote
---- Create the test table with
/****** Object: Table [dbo].[#NOTE_tblNote] Script Date: 12/02/2009 09:05:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[#NOTE_tblNote](
[NoteID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_NOTE_tblNote_NoteID] DEFAULT (newid()),
[CompanyID] [uniqueidentifier] NOT NULL,
[EmployeeID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_NOTE_tblNote_EmployeeID] DEFAULT ('{11111111-1111-1111-1111-111111111111}'),
[ContactID] [uniqueidentifier] NULL,
[OpportunityID] [uniqueidentifier] NULL,
[TaskID] [uniqueidentifier] NULL,
[CaseID] [uniqueidentifier] NULL,
[CampaignID] [uniqueidentifier] NULL,
[ProjectID] [uniqueidentifier] NULL,
[EntryDate] [datetime] NOT NULL CONSTRAINT [DF_NOTE_tblNote_EntryDate] DEFAULT (getdate()),
[NoteType] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NoteText] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NoteTextRTF] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NoteTextTrunc] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContactMade] [bit] NOT NULL CONSTRAINT [DF_NOTE_tblNote_ContactMade] DEFAULT ((0)),
[OleObjects] [image] NULL,
[BPartnerAvailable] [bit] NOT NULL CONSTRAINT [DF_NOTE_tblNote_BPartnerAvailable] DEFAULT ((0)),
[CustomerAvailable] [bit] NOT NULL CONSTRAINT [DF_NOTE_tblNote_CustomerAvailable] DEFAULT ((0)),
[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_NOTE_tblNote_CreationDate] DEFAULT (getdate()),
[ChangeDate] [datetime] NOT NULL CONSTRAINT [DF_NOTE_tblNote_ChangeDate] DEFAULT (getdate()),
[ChangeDateOffline] [datetime] NOT NULL CONSTRAINT [DF__NOTE_tblN__Chang__30E2A0C7] DEFAULT (getdate()),
[ChangedByID] [uniqueidentifier] NOT NULL CONSTRAINT [DF__NOTE_tblN__Chang__31D6C500] DEFAULT ('{11111111-1111-1111-1111-111111111111}'),
[ChangedByIDOffline] [uniqueidentifier] NOT NULL CONSTRAINT [DF__NOTE_tblN__Chang__32CAE939] DEFAULT ('{11111111-1111-1111-1111-111111111111}'),
CONSTRAINT [PK_NOTE_tblNote] PRIMARY KEY NONCLUSTERED
(
[NoteID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [eCRM_crmtest1]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_CAMP_tblCampaign] FOREIGN KEY([CampaignID])
REFERENCES [dbo].[CAMP_tblCampaign] ([CampaignID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_CAMP_tblCampaign]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_CON_tblContact] FOREIGN KEY([ContactID])
REFERENCES [dbo].[CON_tblContact] ([ContactID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_CON_tblContact]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_ICOMP_tblEmployee] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[ICOMP_tblEmployee] ([EmployeeID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_ICOMP_tblEmployee]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_OPP_tblOpportunity] FOREIGN KEY([OpportunityID])
REFERENCES [dbo].[OPP_tblOpportunity] ([OpportunityID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_OPP_tblOpportunity]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_PROJ_tblProject] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[PROJ_tblProject] ([ProjectID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_PROJ_tblProject]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_SERV_tblCase] FOREIGN KEY([CaseID])
REFERENCES [dbo].[SERV_tblCase] ([CaseID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_SERV_tblCase]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_TASK_tblTask] FOREIGN KEY([TaskID])
REFERENCES [dbo].[TASK_tblTask] ([TaskID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_TASK_tblTask]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_XCOMP_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[XCOMP_tblCompany] ([CompanyID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_XCOMP_tblCompany]
----- END Create Table script
------ 7 row(s) data
------ All Inserts into the IDENTITY column
SET IDENTITY_INSERT #NOTE_tblNote ON
------ Insert the test data into the test table
INSERT INTO #NOTE_tblNote
(NoteID,CompanyID,EmployeeID,ContactID,OpportunityID,TaskID,CaseID,CampaignID,ProjectID,EntryDate,NoteType,NoteText,NoteTextRTF,NoteTextTrunc,ContactMade,OleObjects,BPartnerAvailable,CustomerAvailable,CreationDate,ChangeDate,ChangeDateOffline,ChangedByID,ChangedByIDOffline)
SELECT '335551BB-415E-48C0-A218-C7814C29A718','34B1D274-D622-428F-B11E-11247CE293BC','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','E44AACF0-C835-4A04-A96F-B8273C1700F7','NULL','NULL','NULL','NULL','NULL','2009-10-16 09:54:49.000','E-Mail Received','Original E-Mail Address: test1@dummycompany.com','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Original E-Mail Address: test1@dummycompany.com\par }','Original E-Mail Address: test1@dummycompany.com','0','0x00000000','0','0','2009-10-16 10:00:21.463','2009-12-01 10:30:44.073','2009-10-16 10:00:21.463','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT 'E9C16DE1-613F-4CBD-8651-BBD92A68A377','0617409F-5D39-4C51-9708-368407D336BD','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','D3B59963-2B28-459E-85F7-A1E4987FAAB7','NULL','NULL','NULL','NULL','NULL','2009-10-16 09:56:51.000','Contact','Original E-Mail Address: test2@dummycompany.com','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Original E-Mail Address: test2@dummycompany.com\par }','Original E-Mail Address: test2@dummycompany.com','0','0x00000000','0','0','2009-10-16 10:02:26.963','2009-10-16 10:02:26.963','2009-10-16 10:02:26.963','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT 'E36A3A02-0C67-4233-9740-16F6EE8E3D46','305299B1-81DE-4EDF-BCD5-4D82FFACDD91','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','9BC2D972-2602-457B-974F-BCCB33971493','NULL','NULL','NULL','NULL','NULL','2009-10-16 09:52:42.000','Contact','Original E-Mail address: test3@dummycompany.com','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Original E-Mail address: test3@dummycompany.com\par }','Original E-Mail address: test3@dummycompany.com','0','0x00000000','0','0','2009-10-16 09:58:24.323','2009-10-16 09:58:24.323','2009-10-16 09:58:24.323','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT 'DB1F0867-7515-4E45-AE69-29D353CBE90D','750C7124-CBBB-4D1A-B8DA-854DB2E2DF0C','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','E64A2EE8-DA05-482D-BCAA-8C4AD100C6DD','NULL','NULL','NULL','NULL','NULL','2009-10-22 14:41:15.000','Contact','Original E-Mail address: test4@dummycompany.com','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Original E-Mail address: test4@dummycompany.com\par }','Original E-Mail address: test4@dummycompany.com','0','0x00000000','0','0','2009-10-22 14:46:56.960','2009-10-22 14:46:56.960','2009-10-22 14:46:56.960','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT '115BBFCB-117C-4668-9BD5-1C09E777FB34','3E67D88D-042D-4E27-B65A-9DC31962B0F2','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','1A4EC154-AC62-4327-AB99-A0FD0B79BEB6','NULL','NULL','NULL','NULL','NULL','2009-10-16 09:53:53.000','Contact','Original E-Mail address: test5@dummycompany.com','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Original E-Mail address: test5@dummycompany.com\par }','Original E-Mail address: test5@dummycompany.com','0','0x00000000','0','0','2009-10-16 09:59:27.777','2009-10-16 09:59:27.777','2009-10-16 09:59:27.777','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT 'AD8EB582-6174-4971-9934-4D501A0D7115','957FFCD7-3077-4A99-8465-D1DF5458A146','49230AD2-14FC-4470-83BF-D07EE95EED17','9938A6C4-25AD-443E-8397-98F51446496C','NULL','NULL','NULL','NULL','NULL','2009-11-18 09:49:30.000','E-Mail Sent','Test','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Test\par }','Test','0','0x00000000','0','0','2009-11-18 09:55:46.140','2009-11-18 09:55:46.140','2009-11-18 09:55:46.140','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT 'CA83B9A3-9099-495E-8184-985B75FCFEEC','6BCBCFC8-8E8E-49AD-87CB-FB49797005DF','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','D5641FB2-A787-493B-A7DA-96B17C12FE0E','NULL','NULL','NULL','NULL','NULL','2009-11-24 15:21:54.000','Phone Call','test1','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 test1\par }','test1','0','0x00000000','0','0','2009-11-24 15:22:04.467','2009-11-24 15:22:04.467','2009-11-24 15:22:04.467','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111'
------ Set the IDENTITY_INSERT back to normal
SET IDENTITY_INSERT #NOTE_tblNote OFF
----- End Data Insert script
--------- START STORED PROCEDURE CODE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 2009/11/19 rss - new
-- Procedure to pull specific CRM Notes for e-mail notification
ALTER PROCEDURE [dbo].[QVI_CRMNotesNotificationSP]
AS
SET NOCOUNT ON
--- Creates temporary @tempnotes table to hold report ouput
DECLARE @tempnotes TABLE
( entrydate datetime
, notetype nvarchar(30)
, employeename nvarchar(50)
, title nvarchar(30)
, emailaddress nvarchar(255)
, companyname nvarchar(60)
, firstname nvarchar(20)
, middleinit nvarchar(1)
, lastname nvarchar(30)
, companyid uniqueidentifier
, employeeid uniqueidentifier
, icompemployeeid uniqueidentifier
)
DECLARE
@noteentrydate datetime
, @notetype nvarchar(30)
, @icompemployeename nvarchar(50)
, @icomptitle nvarchar(30)
, @icompemailaddress nvarchar(255)
, @xcompcompanyname nvarchar(60)
, @contactfirstname nvarchar(20)
, @contactmiddleinit nvarchar(1)
, @contactlastname nvarchar(30)
, @notecompanyid uniqueidentifier
, @noteemployeeid uniqueidentifier
, @icompemployeeid uniqueidentifier
, @notecontactid uniqueidentifier
, @concontactid uniqueidentifier
, @subj nvarchar(255)
, @body nvarchar(4000)
, @temp nvarchar(255)
, @to nvarchar(1000)
, @message nvarchar(4000)
DECLARE CRMNotesPullCrs CURSOR LOCAL STATIC FOR
SELECT
n.entrydate --- Notes entry date
, n.notetype --- Note Type
, i.employeename --- Note Author
, i.title --- Note Author Title
, i.emailaddress --- Note Author E-Mail address
, x.companyname --- Company/Account associated with Note
, c.firstname --- Contact associated with Note
, c.middleinit
, c.lastname
FROM dbo.NOTE_tblNote n, dbo.ICOMP_tblEmployee i, XCOMP_tblCompany x, CON_tblContact c
WHERE
n.employeeid = i.employeeid
and n.companyid = x.companyid
and n.contactid = c.contactid
and n.notetype = 'E-Mail Sent'
and DATEDIFF(day, n.entrydate, GETDATE()) between 0 and 90 ---- variable
order by i.emailaddress
---- Start Cursor Processing
OPEN CRMNotesPullCrs WHILE 1=1
BEGIN
FETCH CRMNotesPullCrs INTO
@noteentrydate
, @notetype
, @icompemployeename
, @icomptitle
, @icompemailaddress
, @xcompcompanyname
, @contactfirstname
, @contactmiddleinit
, @contactlastname
IF @@FETCH_STATUS <> 0 BREAK
---- End Cursor Processing
BEGIN --- @tempnotes temporary table
INSERT INTO @tempnotes
(
entrydate
, notetype
, employeename
, title
, emailaddress
, companyname
, firstname
, middleinit
, lastname
, companyid
, employeeid
, icompemployeeid
)
VALUES
(
@noteentrydate
, @notetype
, @icompemployeename
, @icomptitle
, @icompemailaddress
, @xcompcompanyname
, @contactfirstname
, @contactmiddleinit
, @contactlastname
, @notecompanyid
, @noteemployeeid
, @icompemployeeid
)
END --- @tempnotes temporary table
---- new code rss 11-30-09
BEGIN --- e-mail distribution section
SET @to = @icompemailaddress
SET @subj = 'Notes Follow-up Notice'
SET @message = 'EntryDate Author E-Mail Address CompanyName FirstName LastName' + CHAR(10) + CHAR(13)
----- WHILE @@FETCH_STATUS = 0 ---- not sure if this statement is needed ????????
BEGIN
SET @temp = (select CAST (@noteentrydate AS nvarchar(12)) ) + ' ' + @icompemployeename + ' ' + @icompemailaddress + ' ' + @xcompcompanyname + ' ' + @contactfirstname + ' ' + @contactlastname + ' ' + CHAR(10)
SET @message = @message + @temp
END --- e-mail distribution section
BEGIN --- dbmail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Job-Mail'
, @recipients = @to
, @subject = @subj
, @body = @message
, @body_format = 'HTML'
---- 'This is a test message from CRM via SQL Database Mail. This message is a reminder to follow-up on sales calls.'
--- Start debug/testing print statements
print ' '
print ' '
print 'email output start set'
print @to
print @subj
print @message
print 'email output end set'
print ' '
--- End debug/testing print statements
END --- db mail code
END -- CRMNotesPullCrs
CLOSE CRMNotesPullCrs
DEALLOCATE CRMNotesPullCrs
BEGIN --- data into @tempnotes temporary table
SELECT * from @tempnotes
ORDER BY employeename, companyname, entrydate
------- order by firstname --- for testing only
END --- data into @tempnotes temporary table
END
SET NOCOUNT OFF