Adding a DateEnd column on a table

[roswald]

I have a table that contains a BeginDate field along with a Plan_id field that we will use to sort recs. What I want to do is add a EndDate column to this table or create a new table with Plan_id, BeginDate and the new EndDate column.

The current table will be sorted by Plan_id and BeginDate (Ascending order) and what I want to do is populate the EndDate of each record with the next records (BeginDate -1) providing the Plan_id is the same for both recs. The last record in the sort will have a EndDate value of Null because that is the currently active record.

Can anyone show me how to write the SQL to accomplish without using a ForwardOnly cursor?
I am sure it can be done with a select stmt.

Thanks for your help!

bob

 

[bborissov]

What about:

UPDATE MyTable SET EndDate = (SELECT TOP 1 DateAdd(dd,BeginDate,-1) FROM MyTable MyTbl WHERE MyTbl.Plan_id = MyTable.Plan_id AND MyTbl.BeginDate > MyTable.BeginDate ORDER BY BeginDate)

not tested well

Borislav Borissov