Probability of Cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Probability of Cursor

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-14 : 00:12:34

Ben writes "Basically Im trying to research whether or not a cursor is a logical solution to my problem. I have a database with two main tables. One table (main) consists of records of every person in a company. The other table (sub) consists of people ready for promotion. The main table needs to be run through and test each record to see if a person's number of days from hiring date to present date is the minimum it takes for him/her to be promoted. If an employee is going to a certain level, he/she must have a certain number of days since he/she passed the previous promotion. If one is found insert the record into the sub table. This needs to be done once a day.
Ive never worked with cursors before, but Ive been told that it may be my solution.

Table Main Layout
------------------
ssn
Name
TimeInServicedate
promotionRate

Same for sub

any suggestions
Thanks"

olily
Starting Member

37 Posts

Posted - 2002-05-14 : 01:08:34

I hope this would help. You need to modify the code below to suit your requirements.

'------------------------------------------------
DECLARE @SSN INT, @ENAME VARCHAR(10), @TIMEINSERVICEDATE DATETIME, @PROMOTIONRATE INT
DECLARE EMPCURSOR CURSOR FOR

SELECT * FROM MAINTABLE
WHERE DATEDIFF(d,TIMEINSERVICEDATE,GETDATE()) > 1
ORDER BY ENAME

OPEN EMPCURSOR

FETCH NEXT FROM EMPCURSOR INTO
@SSN, @ENAME, @TIMEINSERVICEDATE, @PROMOTIONRATE

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO SUBTABLE VALUES (@SSN, @ENAME, @TIMEINSERVICEDATE, @PROMOTIONRATE)
FETCH NEXT FROM EMPCURSOR INTO
@SSN, @ENAME, @TIMEINSERVICEDATE, @PROMOTIONRATE
END

CLOSE EMPCURSOR
DEALLOCATE EMPCURSOR

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-05-14 : 01:47:18

Is this not the same functionality???


INSERT INTO SUBTABLE (Columns)
SELECT Columns FROM MAINTABLE 
WHERE DATEDIFF(d,TIMEINSERVICEDATE,GETDATE()) > 1

DavidM

"SQL-3 is an abomination.."

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-14 : 09:15:03

quote:

One table (main) consists of records of every person in a company.
-- Ben

...this is iterative based thinking...

quote:

One table (main) of company personnel.
-- Page47

...this is set-based thinking...RDBM systems are set based creatures.

Based on you problem statement, I see no need for a cursor. byrmol is on target. I suggest you write it both ways (with a cursor and with an INSERT statement) and run both. Take a look at the query plans. Take a look at performance. Understand why set based is the true path.

<O>

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2002-05-14 : 11:19:47

i attempted the pure insert method but the date timeinrate is char(6) in yymmdd format. I cant get sql to convert it to a date time.

Heres what i tried

Insert (ssn, name, uic) into sub
Select (ssn, name, uic)
From main
where DateDiff(mm, getdate(), convert(datetime, timeinrate)) > 1

I get a cannot convert char to datetime error

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-14 : 11:25:48

Insert (ssn, name, uic) into sub
Select (ssn, name, uic)
From main
where DateDiff(mm, getdate(), convert(datetime, convert(datetime,timeinrate))) > 1

<O>

Edited by - Page47 on 05/14/2002 11:26:31

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2002-05-14 : 12:20:44

i still get a error that it cannot convert. If i do
Select Cast(timeinrate as datetime)
from main

i get
1992-05-23 00:00:00

but for some reason if i put an insert in front of it I get a cannot convert error.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-14 : 12:24:38

We're gonna need to see the exact ddl (create table statement) for [main] and [sub] as well as you exact dml (insert..select..) statement.

<O>

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2002-05-14 : 12:31:42

how do i get the create table statement?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-14 : 12:39:08

quote:
how do i get the create table statement?

you write it. how did you create the tables in the first place?

<O>

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-14 : 12:41:40

He might have used enterprise manager to create it.

If you did that just run the sql script generator on it. It'll make up some coding for you.. put that in here

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2002-05-14 : 12:44:16

my bad I should of known that. here is the actual tables Navm is the main table Scrublist is the sub table
NAVM:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NAVM]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[NAVM]
GO

CREATE TABLE [dbo].[NAVM] (
[NM_SSN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NM_NAME] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_SEX] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_RACECD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_CITZCD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_BRCL] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_EAOS] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_GCTASVAB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_FILLER_1] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_AFQT] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_GCTSC_WK] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_ARISC_AR] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_MECHSC_EI] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_CLERSC_VE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_UIC] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_PUIC] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_NEC] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_PRATE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_ADSD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_TIRDATE] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_ADVDATE] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_PPG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_RT_AUTH_CD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_RATE_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_ACED] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_SPI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_SPIG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_ECD] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_FILLER_2] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_DOB] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NM_TERM_CHAR] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ON [PRIMARY]

Scrublist:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SCRUBLIST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SCRUBLIST]
GO

CREATE TABLE [dbo].[SCRUBLIST] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Cycle] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UIC] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pgrade] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prate] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Erate] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PGain] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Earlies] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Del] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrigFlag] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AddFlag] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sdate] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stime] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Query that I am using:

Insert into Scrublist(ssn, name, uic, prate, pgrade)
Select nm_ssn, nm_name, nm_uic, nm_prate, nm_ppg
From NAVM
where datediff( getdate(), convert(datetime, convert(datetime, nm_tirdate)) > 1

Appreciate the help

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-14 : 12:56:42


Insert into Scrublist(ssn, name, uic, prate, pgrade) 
Select nm_ssn, nm_name, nm_uic, nm_prate, nm_ppg 
From NAVM 
where datediff(dd, getdate(), convert(datetime, nm_tirdate)) > 1

take a look at datediff syntax in bol, the first arg is the datepart(which part of the date to calculate the difference) . . .

the above statement compiles for me. so all that's missing on this thread is the error you get and some sample data that makes it happen . . . we're gonna get to the bottom of this my friend . . .

NOTE: in an above post I used the convert() function twice. I don't know what I was thinking . . . I guess I didn't see that it was already there . . . my apologies . . .regardless, there is no need to convert twice . . .

<O>

Edited by - Page47 on 05/14/2002 12:57:13

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2002-05-14 : 13:12:41

My apologies the datdiff is actually:
datediff(mm, getdate(), convert(datetime, nm_tirdate)) >= 6

my error is exactly
Server: Msg 241, Level 16, Line 1
Syntax error converting datetime from character string

sample data:

ssn, uic, tirdate, name, prate, pgrade
0001, 001, 980701, test, SN, E4

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-14 : 13:28:45

curiosity

try running

select convert(datetime, nm_tirdate) from Navm

If this returns an error, it could be quite simply your data in that column is incorect. I sat around trouble shooting a convert error for an hour before I realized it was because the guy had entries in the column we were converting like the 45 of september 2001 (09-45-01) in it.

take a look at some of the numbers you have in there... Shot in the dark as I'm not fully reading your question... but I have seen this happen

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-05-14 : 13:45:54

And you might also try the handy

select nm_tirdate
from Navm
where Isdate(nm_tirdate) = 0

to determine which rows (if any) aren't valid dates. I support an app that stores dates in non-datetime format and the data provider is ALWAYS sending me junk dates.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-05-14 : 13:50:07

quote:
date timeinrate is char(6) in yymmdd

If your timeinrate is in the above format, you can run into a problem not knowing whether or not the timeinrate is in the 1900s or 2000s.
This is easily solved currently with a case statement, but you might want to consider redesigning the table to add a datetime column for this data. I am guessing that the problem may be in the convert funtion. M.E and Page47, take a look at this and let me know if you all think this is the problem. If so, then how would you guys handle the century problem, with a case statement in the where clause?

Insert into Scrublist(ssn, name, uic, prate, pgrade)
Select nm_ssn, nm_name, nm_uic, nm_prate, nm_ppg
From NAVM
where datediff(dd, getdate(), cast(substring(nm_tirdate,3,2)+'/'+Right(nm_tirdate,2)+'/19'+LEFT(nm_tirdate,2) as datetime)) > 1

Jeremy

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2002-05-14 : 14:24:51

i found a few records that had only 5 chars and a few that were spaces.

Thank you very very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources