Mark Breen
marklbreen at gmail.com
Thu Sep 23 10:24:07 CDT 2010
Hello Paul, I agree with John also. After years of being told to keep away from Cursors, I use them a little and find them quick and easy to work with. You can probably handle your (CurrentRec-1) situation with straight SQL, but the nice thing with cursors is they are easy to debug, and you can even use print statements to record progress. Remember, in the oracle world, they use cursors extensively and are proud to do so. I have always been perplexed at the these two approaches in MS SQL versus Oracle. While writing this email, I see that Francisco has replied also. I know that Francisco is one of our best SQL guru's, so in my opinion, his opinion trumps mine, but nevertheless, my comments remain, and John's comments about performance remain also. Maybe another way to say this is a) the cursor approach will take you about 15 minutes to write and another 5-10 to test it, if it does not work first time. b) if you can write a set theory based approach i.e. straight sql statement, in around that time, then do it with SQL, otherwise use a quick cursor. Good luck and enjoy it which ever approach you take, Mark Enjoy Mark On 23 September 2010 16:07, jwcolby <jwcolby at colbyconsulting.com> wrote: > 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. > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >