Add the end date to record

I have records with a start date. I would like to add a column that is the
end date, or the next record's start date.
So if I had
Store, startdate, retail
1, 10/15/2005, 2.50
1, 10/30/2005, 3.00
1, 11/19/2006, 2.00
The new records would be:
Store, startdate, retail, end date
1, 10/15/2005, 2.50, 10/30/2005
1, 10/30/2005, 3.00, 11/19/2005
1, 11/19/2006, 2.00,
Thanks for any suggestions.
Alter Table MyTable
Add EndDate Datetime
Go
I haven't a clue what your table is called let anyone any Primary Keys
etc but the update statement would look something similar to :
Update MyTable
Set EndDate = '2005-10/-0'
Where Store = 1
And StartDate = '2005-10-15'
HTH
Barry|||I understand your query, but my table have 2 million rows with many differen
t
stores and start dates.
So I need to add the startdate value from the next (in order of startdate)
record that matches on store and add that to my end date column.
Thank you for your assistance.
"Barry" wrote:

>
> Alter Table MyTable
> Add EndDate Datetime
> Go
> I haven't a clue what your table is called let anyone any Primary Keys
> etc but the update statement would look something similar to :
> Update MyTable
> Set EndDate = '2005-10/-0'
> Where Store = 1
> And StartDate = '2005-10-15'
> HTH
> Barry
>|||please try this:
update tbl1
set enddate=(select min(startdate) from tbl1 t where
t.startdate>tbl1.startdate and t.store=tbl1.store)
dean
"AshleyT" <AshleyT@.discussions.microsoft.com> wrote in message
news:666F7AD4-1868-476F-8D37-A1ECF3C8DF76@.microsoft.com...
>I understand your query, but my table have 2 million rows with many
>different
> stores and start dates.
> So I need to add the startdate value from the next (in order of startdate)
> record that matches on store and add that to my end date column.
> Thank you for your assistance.
> "Barry" wrote:
>|||Without any DDL or Sample Data its very difficult to try to help you.
Please see http://www.aspfaq.com/etiquette.asp?id=5006 for more info
and help
Thanks
Barry|||this answered it perfectly. thanks much!
"Dean" wrote:

> please try this:
> update tbl1
> set enddate=(select min(startdate) from tbl1 t where
> t.startdate>tbl1.startdate and t.store=tbl1.store)
> dean
> "AshleyT" <AshleyT@.discussions.microsoft.com> wrote in message
> news:666F7AD4-1868-476F-8D37-A1ECF3C8DF76@.microsoft.com...
>
>