help in script

[ali_hijazi]

Dear

I got a table that contains foreach employee the date and the amount that he got as employee package

the table is as follows:

EmpId, Date, Amount

1,      30/6/2012, 1500

1,      30/7/2012, 1550

1,      31/8/2012, 1550

....

2,     30/6/2012, 2200

....

Another table contains each employee and the date he got bonus

as follows:

EmpId, BonusDate, Bonus

1,    2/5/2012  , 200

1,    15/7/2012, 150

2,    1/4/2012, 125

2,    1/9/2012, 175

....

what I want is the following to have a table that contains the employee, the date of package and the amount along with the last bonus less than or equal to the package date

i.e.

EmpId, Date, Amount , LastBonusDate, LastBonus

1,      30/6/2012, 1500, 2/5/2012, 200

1,      30/7/2012, 1550,15/7/2012, 150

1,      31/8/2012, 1550,15/7/2012, 150

....

2,     30/6/2012, 2200, 1/4/2012,125

....

Please advise

Thank you

[flipside]

I think this works ...

Packages:
LOAD
*,
num(EmpId,'10000') & num(Date,'00000') as KeyComp
INLINE [
EmpId, Date, Amount
1,     30/06/2012, 1500
1,     30/07/2012, 1550
1,     31/08/2012, 1550
2,     30/06/2012, 2200
2,     29/07/2012, 2101
2,     20/08/2012, 1323
2,     30/09/2012, 3123];

Bonuses:
LOAD
*,
num(BonusEmpId,'10000') & num(BonusDate,'00000') as BonusKeyComp
INLINE [
BonusEmpId, BonusDate, BonusAmount
1,    02/05/2012, 200
1,    15/07/2012, 150
1,    31/08/2012, 950
2,    01/04/2012, 125
2,    01/09/2012, 175];

ComboTbl:
LOAD
RowNo() as ComboTblRef,
EmpId as ComboEmpId,
KeyComp as ComboKey
resident Packages;

left join (ComboTbl)
LOAD
BonusEmpId as ComboEmpId,
BonusKeyComp as ComboBonusKey
resident Bonuses;

left join (ComboTbl)
LOAD
ComboTblRef,
max(ComboBonusKey) as ComboLink
resident ComboTbl
where ComboKey - ComboBonusKey >=0
group by ComboTblRef;

left join (Packages)
LOAD
ComboKey as KeyComp,
max(ComboLink) as BonusKeyComp
resident ComboTbl
group by ComboKey;

drop table ComboTbl;

flipside

[ali_hijazi]

nop

[flipside · Accepted solution]

The screenshot below is the table objects created after running my code, is this not what you required?

[ali_hijazi]

Sorry it's yes