Francisco Tapia
fhtapia at gmail.com
Thu Sep 23 12:23:43 CDT 2010
for those of you who might want to take an extra read on sql server cursors (non BOL) http://www.sqlteam.com/article/cursors-an-overview -Francisco http://bit.ly/sqlthis | Tsql and More... On Thu, Sep 23, 2010 at 9:13 AM, Asger Blond <ab-mi at post3.tele.dk> 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. >> >> -- >> 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 >> >> >