Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select statment problem in cursor using datetimes Expand / Collapse
Author
Message
Posted Friday, November 20, 2009 9:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:00 AM
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
Post #822509
Posted Friday, November 20, 2009 1:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:15 PM
Points: 6,842, Visits: 13,370
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
Post #822688
Posted Friday, November 20, 2009 2:38 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
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




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)
Post #822720
Posted Friday, November 20, 2009 3:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:15 PM
Points: 6,842, Visits: 13,370
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
Post #822740
Posted Friday, November 20, 2009 3:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:00 AM
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
Post #822748
Posted Friday, November 20, 2009 3:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
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).



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)
Post #822751
Posted Tuesday, December 1, 2009 7:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:00 AM
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

Post #826677
Posted Tuesday, December 1, 2009 8:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 2:24 PM
Points: 1,519, Visits: 4,082
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
Post #826718
Posted Tuesday, December 1, 2009 8:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
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.




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)
Post #826726
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse