Need help with SQL

[Nifrabar]

Next scenario:

Given a simplified table with next contents
Fld1 Fld2 Fld3 Fld3
1 1 2000 123
2 1 2001 123
3 3 2000 789
4 3 2001 789
5 3 2002 789
6 1 2000 246
7 1 2000 135
8 3 2000 888

Fld1 = ID
FLd2 = Activity
Fld3 = year
Fld4 = Personal ID

The table has a little to do with bookkeeping.
I want to add records for e.g. year 2001 for records having Fld2 = 1 meanwhile there must already exist a record with year 2000 like record 6 (no duplicates alowed)
After automatic update table must look like:
Fld1 Fld2 Fld3 Fld3
1 1 2000 123
2 1 2001 123
3 3 2000 789
4 3 2001 789
5 3 2002 789
6 1 2000 246
7 1 2000 135
8 3 2000 888
9 1 2001 246 (based upon record 6)
10 1 2001 135 (based upon record 8)

Any suggestions if and how this can be done using SQL?

TIA
-Bart

 

[Olaf Doschke]

I think record 10 is based on record 7.

So you simply need a record copy with the year changed?

Local lnOldYear, lnYear, lnActivity
lnOldYear = 2000
lnYear = 2001
lnActivity = 1

Insert Into Table (Activity,Year,PersonalID) Select Activity, lnYear AS Year, PersonalID From Table As T2 Where T2.Activity = lnActivity and T2.Year = lnOldYear

This does not check , if the needed record already exists. Like in your example record 2 already is the needed copy of record 1 for 2001 instead of 2000.

You might add "NOT Indexseek(expression(activity,year,personalID),.F.,"Table","Tagname") to check if the record does not exist already. Or check that with a subselect. But I'm not sure if INSERT INTO (fieldlist) SELECT (Fieldlist) FROM ... will work, if the select has a subselect. Maybe first select the needed records and then APPPEND them.

Bye, Olaf.

 

[Nifrabar]

Hello Olaf,

Yes for sure your rightre. record 10 > 7.
I figured out how to proceed and ended by creating two temp. cursors:
- a cursor containing records to be copied for next year
- a cursor containing records already existing in next year

By looping trough the first, checking with the second I ended with the insert into as third action.

Thanks for your help.
-Bart