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)

Cursor

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-12 : 08:39:59

Kim writes "I am trying to find a way to scan through a table, grab the first record, until it finds a match with the first record. If the two record matches, then I want to run thru some case statements and calculations on those two records…do this until there are no matches. If it does not match, I will make the second record my current record and scan thru the table until I find a match for that. I am looking to do a cursor…but it is not a good idea, because of poor performance. Trying to find the best way to do this. Thinking of recursive, but don’t know much about it and don’t know how to write one.

This is what I have so far.

declare @prior_year smallint
declare @prior_month smallint
declare @prior_rodometer numeric(6)
declare @prior_excess bit
declare @current_uic char (6)
declare @current_sn char (15)
declare @current_nsn char (13)
declare @current_year smallint
declare @current_month smallint
declare @current_rodometer numeric(6)
declare @current_excess bit
declare @rodometer_diff numeric(6)
declare @month_diff numeric(6)

begin tran
CREATE TABLE #temptable (uic CHAR(6),sn CHAR(15),nsn CHAR(13),yr SMALLINT,
mth SMALLINT,rodometer numeric,excess bit)

CREATE TABLE #tablerecord (uic CHAR(6),sn CHAR(15),nsn CHAR(13),cmth SMALLINT,
pmth SMALLINT,crodometer numeric,prodometer numeric,
calcrodometer numeric)

CREATE TABLE #tablerecord2 (uic CHAR(6),sn CHAR(15),cnsn CHAR(13),cmth SMALLINT,
pmth SMALLINT,crodometer numeric,prodometer numeric,
calcrodometer numeric)
commit tran

begin tran
create index nsnsncymth on ulsrc (nsn,sn,cymth)

declare record_cursor cursor for select distinct uic,sn,nsn,substring(cymth,1,4) as yr,
substring(cymth,5,6) as mth,rodometer,
excess_ok
from ulsrc
where nsn in ('2320011289552','1040014138332') --and sn = '117959'

open record_cursor
fetch next from record_cursor into @prior_uic,@prior_sn,@prior_nsn,@prior_year,
@prior_month,@prior_rodometer,@prior_excess
while @@fetch_status = 0
begin
fetch next from record_cursor into @current_uic,@current_sn,@current_nsn,
@current_year,@current_month,@current_rodometer,
@current_excess

while (@prior_uic = @current_uic) and (@prior_sn = @current_sn)
and (@prior_nsn = @current_nsn)
-- if (@prior_uic = @current_uic) and (@prior_sn = @current_sn)
-- and (@prior_nsn = @current_nsn)
begin
select @month_diff = 0
set @month_diff = @current_month - @prior_month
If @month_diff = 1
begin
select @rodometer_diff = 0
set @rodometer_diff = @current_rodometer - @prior_rodometer
insert #tablerecord select @current_uic,
@current_sn,@current_nsn,@current_month,
@prior_month,@current_rodometer,
@prior_rodometer,@rodometer_diff

end
else if @month_diff > 1
begin
select @rodometer_diff = 0
set @rodometer_diff = @current_rodometer - @prior_rodometer
insert #tablerecord2 select @current_uic,
@current_sn,@current_nsn,
@current_month,@prior_month,
@current_rodometer,
@prior_rodometer,
@rodometer_diff
-- fetch next from record_cursor into
-- @current_uic,@current_sn,
-- @current_nsn,@current_year,
-- @current_month,@current_rodometer,
-- @current_excess

end
fetch next from record_cursor into
@current_uic,
@current_sn,@current_nsn,
@current_year,@current_month,
@current_rodometer,@current_excess

--select @rodometer_diff = 0
--set @rodometer_diff = @current_rodometer - @prior_rodometer
--insert #temptable select @current_uic,@current_sn,
-- @current_nsn,@current_

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources