[dba-SQLServer] SQL Server 2005 - Cursors Or Not Cursors

Asger Blond ab-mi at post3.tele.dk
Thu Sep 23 12:57:44 CDT 2010


Paul,
Probably you want to perform the updates inside each group of PersonID's and prevent a NULL for StartCode of the first row in each group.
To group PersonID's and to prevent updates of the first row in each PersonID group use this query:

WITH cte (RowNumber, PersonID, StartCode, EndCode) AS
(SELECT ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY PersonID), PersonID, StartCode, EndCode FROM YourQuery)
UPDATE cte SET StartCode =
(SELECT EndCode FROM cte AS cte2 
WHERE cte.RowNumber = cte2.RowNumber+1 AND cte.PersonID = cte2.PersonID)
WHERE RowNumber > 1

Asger

-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Asger Blond
Sendt: 23. september 2010 18:14
Til: 'Discussion concerning MS SQL Server'
Emne: Re: [dba-SQLServer] SQL Server 2005 - Cursors Or Not Cursors

Paul,
You can use a subquery to make this update. Following example uses a Common Table Expression and the Rank function Row_Number():

WITH cte (RowNumber, StartCode, EndCode) AS
(SELECT ROW_NUMBER() OVER (ORDER BY PersonID), StartCode, EndCode FROM YourQuery)
UPDATE cte SET StartCode =
(SELECT EndCode FROM cte AS cte2 
WHERE cte.RowNumber > 1 AND cte.RowNumber = cte2.RowNumber+1)

Asger

-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Paul Hartland
Sendt: 23. september 2010 16:53
Til: SQLServerList
Emne: [dba-SQLServer] SQL Server 2005 - Cursors Or Not Cursors

To all,

We have employees that can do more than one job in a day on our database,
when an employee is assigned to a job, the application takes the persons
home postcode and the jobs location postcode and stores them in a table, we
are now creating an estimated mileage.  I can do this based on the postcodes
supplied but it will be inaccurate as the start postcodes will always be the
employees home, when the start postcode for the second job should really be
the postcode of the previous job location.

At the moment I have a select query which returns results for an employee
where they are doing more than one job in a day, result example below:

PersonID     Date                 StartCode            EndCode
111111        29/09/2010        AA1 1AA             BB1 1BB
111111        29/09/2010        AA2 2AA             BB2 2BB
111111        29/09/2010        AA3 3AA             BB3 3BB
111111        29/09/2010        AA4 4AA             BB4 4BB

I am thinking of putting these results into a cursor and looping through the
records updating the startcode with the previous records endcode, so that
AA2 2AA will become BB1 1BB, AA3 3AA will become BB2 2BB etc

Is this the best way to do this, or could someone point me in a better
direction please.

Many thanks in advance for all your help.

-- 
Paul Hartland
paul.hartland at googlemail.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com






More information about the dba-SQLServer mailing list