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

Paul Hartland paul.hartland at googlemail.com
Thu Sep 23 09:53:25 CDT 2010


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



More information about the dba-SQLServer mailing list