Update a value on current record based on previous record

[WS6 DAD]

This is probably very simple but I always find a way to over complicate things.

1. I need to identify the latest instance of each header Id, in the example below I want to update the Last_Instance field to say 'yes' based on the most recent 'Last_Update_Da te', in this case 11/2/07 12:02 AM.

2. I need to update the 'From_Date' to be 'Last_Update_Da te' of the previous instance. See examples below.

This is the table before updates
Header_ID From_Date Last_Update_Dat e Last_Instance
249701 NULL 10/19/06 11:01 PM NULL
249701 NULL 11/29/06 9:38 AM NULL
249701 NULL 8/13/07 10:00 AM NULL
249701 NULL 11/2/07 12:02 AM NULL

The table should look like this after updates

Header_ID From_Date Last_Update_Dat e Last_Instance
249701 10/19/06 11:01 PM 10/19/06 11:01 PM NULL
249701 10/19/06 11:01 PM 11/29/06 9:38 AM NULL
249701 11/29/06 9:38 AM 8/13/07 10:00 AM NULL
249701 8/13/07 10:00 AM 11/2/07 12:02 AM yes


Any help would be much appreciated.

[debasisdas]

As you have posted a question in the SQL Server Articles section it is being moved to SQL Server Forum.