jwcolby
jwcolby at colbyconsulting.com
Thu Sep 23 10:07:27 CDT 2010
Paul, As long as this isn't doing tens of thousands of these at a shot I think this is the perfect application of a cursor. Cursors are slow but who cares if it is just a dozen at a time? John W. Colby www.ColbyConsulting.com On 9/23/2010 10:53 AM, Paul Hartland wrote: > 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. >