Need logic..

synsog · Post by **synsog** » Mon Oct 01, 2012 4:04 pm

I have source data like below
3902 10AM
3902 11AM
3902 2PM
3902 4PM

In target data base , i have data like this

rec1 3902 9:45AM
rec2 3902 9:50AM
rec3 3902 10:30AM
rec4 3902 11:10AM
rec5 3902 11:45AM
rec6 3902 1:30PM
rec7 3902 2:05PM
rec8 3902 2:35PM
rec9 3902 3:59PM
rec10 3902 4:23PM

Now for every source rec, i need to find the record with key match and max time of database records < source rec time

like i need to fetch the records;
3902 10AM rec2
3902 11AM rec3
3902 2PM rec6
3902 4PM rec9

how I can accomplish this scenerio in DS8.7 ?

Source data will be less than 1K every day, target data will be more than 10K every time...

Please help...

Thanks, in Advance.

ray.wurlod · Post by **ray.wurlod** » Mon Oct 01, 2012 5:59 pm

What have you already tried? Why did it not succeed?

ShaneMuir · Post by **ShaneMuir** » Wed Oct 03, 2012 4:03 am

I would suggest a sparse lookup with a custom sql to select exactly what you want.

swapnilverma · Post by **swapnilverma** » Wed Oct 03, 2012 5:24 am

I would create a view which have records with max time of database records and perform a left outer join or spars look up .

Hope this will help!