Need Wayout To Join Resident Table To Itself With 2 Field And Using <= In Join

[#1]

Dear All,

I am bit new to QV and I am stucked in wrighting data load script to achieve above final result

Resident Table

Code	Date	Amount
A1	30/04/2011	1000
A1	31/05/2011	2000
A1	30/06/2011	3000

Intermediate Result After Join Itself

Code	Date	Amount	Date1	Amount1
A1	30/04/2011	1000	30/04/2011	1000
A1	31/05/2011	2000	30/04/2011	1000
			31/05/2011	2000
A1	30/06/2011	3000	30/04/2011	1000
			31/05/2011	2000
			30/06/2011	3000

Final Result

Code	Date	Amount
A1	30/04/2011	1000
A1	31/05/2011	3000
A1	30/06/2011	6000

I can solve it by Sql Query like select * from tab1 a inner join tab1 b on a.fld1=b.fld1 and b.fld2<=a.fld1

I know in QV we can join 2 tables on a single common field directly it not possible in QV, so please provide me the wayout for it.

Thanks In Advance

Regards

- IndianQvLover

[#2]

hi Sir,

I am understanding what you you are writhing,

Final Result Table is not a problem, problem is Intermediate Result Table because it is deriving to join itself on 2 field and join like code=code and Date<=Date it like Sql Query

"Select * from Table1 as a inner join Table2 as b on b.Code=a.Code and b.Date<=a.Date"

Hope I explain properly

Regards

[#3]

Hi, Anil

Sorry for late reply, I was working on your suggestion. It is working fine and I got desired final result.

Now I am working on the Mr. WhiteLine & Celambarasan Ashimulam suggestions.

Regards

IndianQvLover