November 20, 2009 at 9:39 am
I am going nuts trying to get this code to work with the final criteria! Trying to extract Notes that meet date and type criteria from Notes table using a cursor and temporary table. Part of the code removes the time from datetime fields so that only notes that are xx days old will be extracted/selected. Everything is working correctly except I cannot figure out how to get my final criteria to work. The final criteria is
notes where @entrydateconv = @Today - 10 or @entrydateconv = DATEADD(day,-10,getdate()) [i.e. where notes are 10 days old from today]. I know that this involves a select statment but not sure where in the code to put it. I have tried various placements and none work as the final criteria needs to be after the SET @entrydateconv = dateadd(dd, datediff(dd,0, @noteentrydate ),0) statement that removes time from entrydateconv datetime field and this is placed after the Fetch statement.
CODE:
set ANSI_NULLS ON
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
DECLARE
@Today datetime
, @EntryDate datetime ---- not used ??? 11-20-09
, @entrydateconv datetime
SET @Today = dateadd(dd, datediff(dd,0, GetDate() ),0) --- removes time from datetime works correctly
DECLARE CRMNotesPullCrs CURSOR LOCAL STATIC FOR
SELECT
n.entrydate
, n.notetype
, i.employeename
, i.title
, i.emailaddress
, x.companyname
, c.firstname
, c.middleinit
, c.lastname
, n.companyid
, n.employeeid
, i.employeeid
, n.contactid
, c.contactid
, n.entrydate --- used for entrydate conversion
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
OPEN CRMNotesPullCrs WHILE 1 = 1
BEGIN
FETCH CRMNotesPullCrs INTO
@noteentrydate
, @notetype
, @icompemployeename
, @icomptitle
, @icompemailaddress
, @xcompcompanyname
, @contactfirstname
, @contactmiddleinit
, @contactlastname
, @notecompanyid
, @noteemployeeid
, @icompemployeeid
, @notecontactid
, @concontactid
, @entrydateconv
IF @@FETCH_STATUS <> 0 BREAK
BEGIN
SET @entrydateconv = dateadd(dd, datediff(dd,0, @noteentrydate ),0) --- removes time from entrydateconv datetime field
comment: works correctly
END
Comment - problem is here trying to code select statement to get the final criteria to work.
BEGIN
SELECT 1
WHERE
--@notetype = 'E-Mail Sent' ---and
----@entrydateconv = @Today - 40
@entrydateconv = DATEADD(day,-29,getdate())
end --- select statement
Comment: remaining code works correctly
BEGIN
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
END -- CRMNotesPullCrs
CLOSE CRMNotesPullCrs
DEALLOCATE CRMNotesPullCrs
BEGIN
SELECT * from @tempnotes
--------ORDER BY i.employeename, x.companyname, n.entrydate
END
SET NOCOUNT OFF
November 20, 2009 at 1:43 pm
A few things to notice:
1) (regarding your question)
The problem is with @entrydateconv = DATEADD(day,-29,getdate()) .
Since you "normalized" @entrydateconv, your WHERE condition will only be true exactly at midnight for each day.
You should change it to
@entrydateconv = dateadd(dd, datediff(dd,0, GetDate() ),-29)
2) question in general
You should change your syntax when joining tables.
Instead of
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
I'd recommend
FROM dbo.NOTE_tblNote n
INNER JOIN dbo.ICOMP_tblEmployee i ON n.employeeid = i.employeeid
INNER JOIN XCOMP_tblCompany x ON n.companyid = x.companyid
INNER JOIN CON_tblContact c ON n.contactid = c.contactid
3) and finally:
What is the specific reason to use a cursor?
I strongly recommend to try to change your cursor into a set-based approach to help performance.
November 20, 2009 at 2:38 pm
Not having any test data or the table structure either, here is a possible set-based solution to your cursor-based solution you are currently using.
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
);
insert into @tempnotes
SELECT
n.entrydate
, n.notetype
, i.employeename
, i.title
, i.emailaddress
, x.companyname
, c.firstname
, c.middleinit
, c.lastname
, n.companyid
, n.employeeid
, i.employeeid
--, n.contactid
--, c.contactid
--, n.entrydate --- used for entrydate conversion
FROM
dbo.NOTE_tblNote n
inner join dbo.ICOMP_tblEmployee i
on (n.employeeid = i.employeeid)
inner join XCOMP_tblCompany x
on (n.companyid = x.companyid)
inner join CON_tblContact c
on (n.contactid = c.contactid)
where
n.entrydate >= dateadd(dd, datediff(dd, 0, getdate()), -10) -- Notes entered 10 days ago
and n.entrydate < dateadd(dd, datediff(dd, 0, getdate()), -9) -- but not 9 days ago
select
*
from
@tempnotes -- what data was entered
November 20, 2009 at 3:04 pm
I thought about providing some sample code for a set based solution, too. But since I couldn't find any place in the code where the OP used the variable @entrydateconv (other than to assign it) I decided to leave it with the verbal comment until there is more information...
Proves at least one thing: A saint is a saint. And I am not... 😉
November 20, 2009 at 3:19 pm
Per your post below, what is the advantage doing the inner joins versus the code I wrote? I have been only doing T-SQL for a short time and appreciate any tips. Can you explain the differences and pros/cons between the 2 coding methods shown below?
2) question in general
You should change your syntax when joining tables.
Instead of
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
I'd recommend
FROM dbo.NOTE_tblNote n
INNER JOIN dbo.ICOMP_tblEmployee i ON n.employeeid = i.employeeid
INNER JOIN XCOMP_tblCompany x ON n.companyid = x.companyid
INNER JOIN CON_tblContact c ON n.contactid = c.contactid
November 20, 2009 at 3:24 pm
rons-605185 (11/20/2009)
Per your post below, what is the advantage doing the inner joins versus the code I wrote? I have been only doing T-SQL for a short time and appreciate any tips. Can you explain the differences and pros/cons between the 2 coding methods shown below?2) question in general
You should change your syntax when joining tables.
Instead of
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
I'd recommend
FROM dbo.NOTE_tblNote n
INNER JOIN dbo.ICOMP_tblEmployee i ON n.employeeid = i.employeeid
INNER JOIN XCOMP_tblCompany x ON n.companyid = x.companyid
INNER JOIN CON_tblContact c ON n.contactid = c.contactid
Putting your join criteria in the FROM CLAUSE is the ANSI STANDARD and is required in SQL Server 2005 and later when using outer joins.
Also, it makes your code cleaner when you separate the join criteria between tables (FROM CLAUSE) and the query filter criteria (WHERE CLAUSE).
December 1, 2009 at 7:37 am
Per Lynn's quoted reply below. Being new to SQL (my background was some light VB programming) I am unclear by what is meant by a set based solution. Most of the time I am working in SQL to derive specific rows of data meeting a specific criteria.
In the task I am currently working on, we have a table with Notes - basic fields are note entry date, note author, author's e-mail address, note type, the note itself, and the associated companyname. Normally I would search row by row for notes that meet a specified criteria, usually using a cursor. Are you stating to run a different type of query and pull all the row results into a "set"? If so, what is the difference between running a cursor and pulling rows together in a set? Sorry, but I am unclear about the "set-based solution".
Please advise.
Lynn Pettis (11/20/2009)
Not having any test data or the table structure either, here is a possible set-based solution to your cursor-based solution you are currently using.
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
);
insert into @tempnotes
SELECT
n.entrydate
, n.notetype
, i.employeename
, i.title
, i.emailaddress
, x.companyname
, c.firstname
, c.middleinit
, c.lastname
, n.companyid
, n.employeeid
, i.employeeid
--, n.contactid
--, c.contactid
--, n.entrydate --- used for entrydate conversion
FROM
dbo.NOTE_tblNote n
inner join dbo.ICOMP_tblEmployee i
on (n.employeeid = i.employeeid)
inner join XCOMP_tblCompany x
on (n.companyid = x.companyid)
inner join CON_tblContact c
on (n.contactid = c.contactid)
where
n.entrydate >= dateadd(dd, datediff(dd, 0, getdate()), -10) -- Notes entered 10 days ago
and n.entrydate < dateadd(dd, datediff(dd, 0, getdate()), -9) -- but not 9 days ago
select
*
from
@tempnotes -- what data was entered
December 1, 2009 at 8:13 am
rons-605185 (12/1/2009)
Per Lynn's quoted reply below. Being new to SQL (my background was some light VB programming) I am unclear by what is meant by a set based solution. Most of the time I am working in SQL to derive specific rows of data meeting a specific criteria.In the task I am currently working on, we have a table with Notes - basic fields are note entry date, note author, author's e-mail address, note type, the note itself, and the associated companyname. Normally I would search row by row for notes that meet a specified criteria, usually using a cursor. Are you stating to run a different type of query and pull all the row results into a "set"? If so, what is the difference between running a cursor and pulling rows together in a set? Sorry, but I am unclear about the "set-based solution".
Please advise.
This is a very common issue among VB programmers. VB uses loops for... everything. SQL does not operate well in a loop. SQL is a set based language. Will the same loops / procedural constructs that you're so used to using in VB *work* in SQL? Yes, they will. But they will be extremely inefficient and could take exponentially longer than a "set based" solution in SQL would. It's not a question of function, it's a question of performance.
It's probably going to be hard to start thinking of it in a different manner (I work with a lot of VB / .NET programmers and they often have a hard time really getting the concept of 'set based' as opposed to prodecural), but if you're going to be writing much T-SQL, you really need to start trying to think that way.
December 1, 2009 at 8:22 am
Okay, so let us take another tact. Let us start with a table of customers across the US. You want a list of customers in California, for instance.
Here is a simple table for this:
create table dbo.Customer (
CustomerID int identity(1,1),
CustomerName varchar(50) not null,
CustomerAddress varchar(50) not null,
CustomerCity varchar(50) not null,
CustomerState char(2) not null,
CustomerZipCode varchar(10) not null
);
A set-based solution for finding all customers in California (CA):
select
CustomerID,
CustomerName,
CustomerAddress,
CustomerCity,
CustomerState,
CustomerZipCode
from
dbo.Customer
where
CustomerState = 'CA';
I'd write a cursor-based solution, but I sometimes have issues with posting code with variable declarations from work.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply