Moving Data From one column to another on different rows

[Trighosts]

table: 4 columns

col1: userID, col2: YearRef, col3: PastYearData, col4: CurrentYearData
example:
col1 col2 col3 col4
1 2003 xyz abc
2 2203 qrs zzz
1 2004 abc 123
2 2204 zzz 456


Each year I need to keep track of current data (CY) and past data (PY). Looking for a simple way in SQL to copy old CY to new row's PY. Tried working with UPDATE - SET but I am not sure how to reference the table to itself. Any other sugestions?

 

[Jamfool]

declare @userID varchar(15)
declare @YearRef datetime
declare @PastYearData varchar(15)
declare @CurrentYearData varchar(6)
declare @updateyear datetime
declare @updateyearplus datetime
select @updateyear = CONVERT(DATETIME, '01/01/2003', 101)
select @updateyearplus = CONVERT(DATETIME, '12/31/2003', 101)


DECLARE CUR_updateData SCROLL CURSOR FOR
SELECT userID,
YearRef,
PastYearData,
CurrentYearData

FROM table_name
Where (YearRef >= @updateyear) and (YearRef <= @updateyearplus)

OPEN CUR_updateData
FETCH NEXT FROM CUR_updateData
INTO @userID,
@YearRef,
@PastYearData,
@CurrentYearData

while @@fetch_status = 0

BEGIN
select @YearRef = DATEADD(year, 1,@YearRef)
insert into table_name (userID,YearRef,PastYearData,CurrentYearData)
values (@userID,@YearRef,@CurrentYearData,null)

FETCH NEXT FROM CUR_updateData
INTO @userID,
@YearRef,
@PastYearData,
@CurrentYearData

END

CLOSE CUR_updateData
DEALLOCATE CUR_updateData



Hope this helps

 

[PruSQLer]

That's some ANSI solution there, Jamfool. Trighosts, I'm a little confused by the values you presented as years in Col 2 but if those values are consistent (e.g. 2003, 2004 etc) the following should work:

update ansi.update_prob t1
set col3 =
(Select col4
from ansi.update_prob t2
where t2.col2 = 2003
and t1.col1 = t2.col1)
Where t1.col2 = 2004

 

[Trighosts]

Jamfool

Thank you very much. I will have to make some modification. However, you have put me in the right direction.

Any day I learn something new is a good day.

Thanks

 

[r937]

you can avoid the update entirely with a different table design

you have:

userID, YearRef, PastYearData, CurrentYearData

change this to:

userID, YearRef, YearRefData

in addition to the fact that no update is required, you save storage space too

and you can still get the original (PastYearData, CurrentYearData) through a view

rudy

http://r937.com/

 

[r937]

[afterthought]

> Looking for a simple way in SQL to
> copy old CY to new row's PY

it's not that simple

you want to use CurrentYearData to update PastYearData

but what if there is no data for the current year?

you might lose a userid that way

[/afterthought]