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

OK Asger (and Francisco), but this is a perfect example of... WTF over?

I for one haven't a clue what this code does, nor do I have a clue how to figure out what this code 
does.

If you are going to recommend code like this you need to tell us how it works, otherwise I for one 
will go "ok, now where did I put that cursor..."



John W. Colby
www.ColbyConsulting.com

On 9/23/2010 12:13 PM, Asger Blond wrote:
> 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.
>