Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select statment problem in cursor using datetimes


Select statment problem in cursor using datetimes

Author
Message
rons-605185
rons-605185
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 162
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
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
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.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24167 Visits: 37930
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



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
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... ;-)



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
rons-605185
rons-605185
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 162
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24167 Visits: 37930
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).

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
rons-605185
rons-605185
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 162
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



Garadin
Garadin
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1523 Visits: 4107
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.

Seth Phelabaum
Consistency is only a virtue if you're not a screwup. ;-)

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24167 Visits: 37930
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search