October 31, 2006 at 4:12 pm
Hi,
Having a bit of trouble with a script.
I have a table such as :
AppNo |AppDate
---------------------
|200469151|27062005
|200469151|27052005
|200469151|21032006
|200469160|21032006
|200469160|27052005
Here, as you can see the AppNo is the same for a few different AppDates. I would like to modify the AppNo by adding 10,000,000 to it for dates which are greater than the previous ones.
So, I would like the end result to be:
AppNo |AppDate
---------------------
|300469151|27062005
|200469151|27052005
|400469151|21032006
|300469160|21032006
|200469160|27052005
I tried to do stuff like:
update @temp set AppNo = [AppNoMew] from
(select a.AppNo+10000000 as 'AppNoNew', a.AppDate
from @temp a, @temp b
where a.AppDate > b.AppDate
and a.AppNo = b.AppNo) a, @temp b
where b.AppNo = a.AppNo
and b.AppDate = a.AppDate
But that didn't work.
Am I trying to make it too complicated with the self-join and is there another easier way to get about it?
Thanks
October 31, 2006 at 7:35 pm
Here is some code that will do it for you - though I have used slow cursors so if it's a one off task then speed will not be an issue.
create
table app
(
AppNo
INT
, AppDate Datetime
)
INSERT
app (AppNo, AppDate)
values
(
200469151, '20050627')
INSERT
app (AppNo, AppDate)
values
(
200469151, '20050527')
INSERT
app (AppNo, AppDate)
values
(
200469151, '20060321')
INSERT
app (AppNo, AppDate)
values
(
200469160, '20060321')
INSERT
app (AppNo, AppDate)
values
(
200469160, '20050527')
select
* from app
DECLARE
@LoopCount INT
, @LastAppNo INT
SELECT
@LoopCount
= 0
, @LastAppNo = 0
DECLARE
curApp CURSOR
READ_ONLY
FOR
SELECT
AppNo
, AppDate
FROM
app
ORDER BY
AppNo
ASC
, AppDate ASC
DECLARE
@appno INT
, @AppDate DateTime
OPEN
curApp
FETCH
NEXT FROM curApp INTO @appno, @AppDate
WHILE
(@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF (@LastAppNo <> @appno)
BEGIN
SELECT
@LoopCount
= 1
, @LastAppNo = @appno
END
IF (@LoopCount <> 1)
BEGIN
UPDATE
a
SET
appno
= appno + (100000000 * (@LoopCount - 1))
FROM
app a
WHERE
appno
= @appno
AND
AppDate
= @AppDate
END
SELECT
@LoopCount
= @LoopCount + 1
END
FETCH NEXT FROM curApp INTO @appno, @AppDate
END
CLOSE
curApp
DEALLOCATE
curApp
select
* from app
drop
table app
Excuse the formatting (or lack there of)
November 1, 2006 at 8:32 am
If this table is not too large, (upto maybe a 100k rows) - then you can use the following set based approach.
create table test select (appNo int, appdate datetime)
insert into test select 200469151, convert(datetime, '27/06/2005', 103)
insert into test select 200469151, convert(datetime, '27/05/2005', 103)
insert into test select 200469151, convert(datetime, '21/03/2006', 103)
insert into test select 200469160, convert(datetime, '21/03/2006', 103)
insert into test select 200469160, convert(datetime, '27/05/2005', 103)
alter table test add prevappNo int
update test set prevappNo = appNo
select * from test order by prevappNo, appdate
update test set
appNo = appNo + 100000000 * (select count(*) from test t1
where t1.prevappNo = t.prevappNo
and t1.appdate < t.appdate)
from test t
select * from test order by prevappNo, appdate
When you are done, you can drop the prevappNo column. If you have millions of rows in this table, thenI would suggest creating a different table, insert into it, drop the old table and then rename the new one to the original name.
November 1, 2006 at 5:12 pm
Coolies...thanks for the responses guys; I reckon as Steve said that double cursor approach would be pretty performance hungry. I will try the method suggested by CK as I have around 200K rows.
Cheers for the help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply