Francisco Tapia
fhtapia at gmail.com
Thu Sep 23 12:09:02 CDT 2010
excellent example... -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 > >