Add the end date to record

12 Jan 2006 7:18 PM

AshleyT

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.

12 Jan 2006 7:23 PM

Barry

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

12 Jan 2006 7:45 PM

AshleyT

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.

Show quote

"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
>
>

12 Jan 2006 8:00 PM

Dean

please try this:

update tbl1
set enddate=(select min(startdate) from tbl1 t where
t.startdate>tbl1.startdate and t.store=tbl1.store)

dean

Show quote

"AshleyT" <Ashl***@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:
>
>>
>>
>> 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
>>
>>

12 Jan 2006 8:28 PM

AshleyT

this answered it perfectly. thanks much!

Show quote

"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" <Ashl***@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:
> >
> >>
> >>
> >> 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
> >>
> >>
>
>
>

12 Jan 2006 8:02 PM

Barry

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