Update Previous Row - 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)

Update Previous Row

Author

Topic

ChetShah
Starting Member

37 Posts

Posted - 2010-03-09 : 09:42:08

Hi

I need to do row by row processing without using cursors as this has a performance hit. The table looks like this:

Row_Id Par_Row_Id StartDate EndDate Status PrevRow_id
1-7GYPPT 1-7GYPPQ 27/01/2010 27/01/2010 New Claim NULL
1-7GYPQ0 1-7GYPPQ 27/01/2010 17/02/2010 Application Form Sent NULL
1-7HD9CC 1-7GYPPQ 17/02/2010 19/02/2010 Application Form being checked NULL
1-7HDHEL 1-7GYPPQ 19/02/2010 04/03/2010 Processing NULL
1-7HMQEJ 1-7GYPPQ 04/03/2010 05/03/2010 Assistant Manager Review NULL
1-7HMX0J 1-7GYPPQ 05/03/2010 08/03/2010 Payment Authorised NULL
1-7HNQCE 1-7GYPPQ 08/03/2010 NULL Completed NULL
1-C4L5-425 1-C45O-487 23/12/2008 30/04/2009 New Claim NULL
1-5DCNL1 1-C45O-487 30/04/2009 22/05/2009 Processing NULL
1-5PAFX9 1-C45O-487 22/05/2009 05/03/2010 Eligible - awaiting maturity NULL
1-7HKN81 1-C45O-487 05/03/2010 08/03/2010 Offer Accepted NULL
1-7HOCW6 1-C45O-487 08/03/2010 08/03/2010 Payment Authorised NULL
1-7HOCWC 1-C45O-487 08/03/2010 08/03/2010 Completed NULL
1-7HOK1H 1-C45O-487 08/03/2010 NULL Completed NULL

For each Row i'd like to update the PrevRow_id column with previous rowid from Row_Id column. So first row stays NULL, then row 2 gets row1's row_id etc. Also if end date has a NULL don't update the next row as this has a new par_row_id value. The row after new par_row_id needs to be updated. The resulting table should look like this:

Row_Id Par_Row_Id StartDate EndDate Status PrevRow_id
1-7GYPPT 1-7GYPPQ 27/01/2010 27/01/2010 New Claim NULL
1-7GYPQ0 1-7GYPPQ 27/01/2010 17/02/2010 Application Form Sent 1-7GYPPT
1-7HD9CC 1-7GYPPQ 17/02/2010 19/02/2010 Application Form being checked 1-7GYPQ0
1-7HDHEL 1-7GYPPQ 19/02/2010 04/03/2010 Processing 1-7HD9CC
1-7HMQEJ 1-7GYPPQ 04/03/2010 05/03/2010 Assistant Manager Review 1-7HDHEL
1-7HMX0J 1-7GYPPQ 05/03/2010 08/03/2010 Payment Authorised 1-7HMQEJ
1-7HNQCE 1-7GYPPQ 08/03/2010 NULL Completed 1-7HMX0J
1-C4L5-425 1-C45O-487 23/12/2008 30/04/2009 New Claim NULL
1-5DCNL1 1-C45O-487 30/04/2009 22/05/2009 Processing 1-C4L5-425
1-5PAFX9 1-C45O-487 22/05/2009 05/03/2010 Eligible - awaiting maturity 1-5DCNL1
1-7HKN81 1-C45O-487 05/03/2010 08/03/2010 Offer Accepted 1-5PAFX9
1-7HOCW6 1-C45O-487 08/03/2010 08/03/2010 Payment Authorised 1-7HKN81
1-7HOCWC 1-C45O-487 08/03/2010 08/03/2010 Completed 1-7HOCW6
1-7HOK1H 1-C45O-487 08/03/2010 NULL Completed 1-7HOCWC

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-09 : 09:58:53

are you really on sql server 2000? (I wish I'd checked the forum before replying with this).

if you were on 2005 you could do this:



SET DATEFORMAT dmy

DECLARE @dump TABLE (
	[row_ID] VARCHAR(255)
	, [Par_Row_ID] VARCHAR(255)
	, [StartDate] DATETIME
	, [EndDate] DATETIME
	, [Status] VARCHAR(255)
	, [PrevRow_ID] VARCHAR(255)
	)

INSERT @dump
      SELECT '1-7GYPPT',	'1-7GYPPQ',		'27/01/2010',	'27/01/2010',	'New Claim',	NULL
UNION SELECT '1-7GYPQ0',	'1-7GYPPQ',		'27/01/2010',	'17/02/2010',	'Application Form Sent',	NULL
UNION SELECT '1-7HD9CC',	'1-7GYPPQ',		'17/02/2010',	'19/02/2010',	'Application Form being checked',	NULL
UNION SELECT '1-7HDHEL',	'1-7GYPPQ',		'19/02/2010',	'04/03/2010',	'Processing',	NULL
UNION SELECT '1-7HMQEJ',	'1-7GYPPQ',		'04/03/2010',	'05/03/2010',	'Assistant Manager Review',	NULL
UNION SELECT '1-7HMX0J',	'1-7GYPPQ',		'05/03/2010',	'08/03/2010',	'Payment Authorised',	NULL
UNION SELECT '1-7HNQCE',	'1-7GYPPQ',		'08/03/2010',	NULL,			'Completed',	NULL
UNION SELECT '1-C4L5-425',	'1-C45O-487',	'23/12/2008',	'30/04/2009',	'New Claim',	NULL
UNION SELECT '1-5DCNL1',	'1-C45O-487',	'30/04/2009',	'22/05/2009',	'Processing',	NULL
UNION SELECT '1-5PAFX9',	'1-C45O-487',	'22/05/2009',	'05/03/2010',	'Eligible - awaiting maturity',	NULL
UNION SELECT '1-7HKN81',	'1-C45O-487',	'05/03/2010',	'08/03/2010',	'Offer Accepted',	NULL
UNION SELECT '1-7HOCW6',	'1-C45O-487',	'08/03/2010',	'08/03/2010',	'Payment Authorised',	NULL
UNION SELECT '1-7HOCWC',	'1-C45O-487',	'08/03/2010',	'08/03/2010',	'Completed',	NULL
UNION SELECT '1-7HOK1H',	'1-C45O-487',	'08/03/2010',	NULL,			'Completed',	NULL

SELECT
	d.[row_ID]
	, d.[Par_Row_ID]
	, d.[StartDate]
	, d.[EndDate]
	, d.[Status]
	, dPrev.[Prev_row_Id]
FROM
	@dump d

	OUTER APPLY (
		SELECT TOP 1
			[row_Id] AS [Prev_Row_Id]
		FROM
			@dump d2
		WHERE
			d2.[Par_Row_ID] = d.[par_Row_id]
			AND d2.[endDate] <= d.[startDate]
			AND d2.[row_Id] <> d.[row_Id]
		ORDER BY
			d2.[endDate] DESC
		)
		dPrev
ORDER BY
	d.[Par_Row_ID]
	, d.[startDate]
	, d.[endDate]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ChetShah
Starting Member

37 Posts

Posted - 2010-03-09 : 10:06:11

Hi Charlie

Yes unfortunately we are on SQL 2K - planning to upgrade soon but need this query for 2K - any help would be appreciated

Thanks

Chet

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-09 : 10:23:55

Thats a real shame because as you can see it's actually pretty easy on 2005. OUTER APPLY can use indices well and is really fast! You'll really see a difference on upgrading -- the RANKING functions like ROW_NUMBER() alone are worth it.

If you need a good performing method in 2000 then I think you may want to look into a quirky update. (basically a way to abuse the UPDATE statement and get some row processing out of it)

Madhivanan is an expert on such things and he often posts here so you might get some good advice from him.

Here's a link in the meantime.
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-09 : 10:26:25

You could also try this:


SELECT
	d.[row_ID]
	, d.[Par_Row_ID]
	, d.[StartDate]
	, d.[EndDate]
	, d.[Status]
	, (SELECT TOP 1
			[row_Id] AS [Prev_Row_Id]
		FROM
			@dump d2
		WHERE
			d2.[Par_Row_ID] = d.[par_Row_id]
			AND d2.[endDate] <= d.[startDate]
			AND d2.[row_Id] <> d.[row_Id]
		ORDER BY
			d2.[endDate] DESC
		) AS [Prev_row_Id]
FROM
	@dump d

ORDER BY
	d.[Par_Row_ID]
	, d.[startDate]
	, d.[endDate]

But be warned though even though it looks very similar to the first code I posted it's actually a row by bloody row op so may not be that much quicker than your cursor.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources