Asger Blond
ab-mi at post3.tele.dk
Thu Sep 23 12:57:44 CDT 2010
Paul, Probably you want to perform the updates inside each group of PersonID's and prevent a NULL for StartCode of the first row in each group. To group PersonID's and to prevent updates of the first row in each PersonID group use this query: WITH cte (RowNumber, PersonID, StartCode, EndCode) AS (SELECT ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY PersonID), PersonID, StartCode, EndCode FROM YourQuery) UPDATE cte SET StartCode = (SELECT EndCode FROM cte AS cte2 WHERE cte.RowNumber = cte2.RowNumber+1 AND cte.PersonID = cte2.PersonID) WHERE RowNumber > 1 Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Asger Blond Sendt: 23. september 2010 18:14 Til: 'Discussion concerning MS SQL Server' Emne: Re: [dba-SQLServer] SQL Server 2005 - Cursors Or Not Cursors 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