Date between two dates - comparision

[b_boby]

Guys,
How to compare date1( non key) from file1 to check in Between two dates i.e date2A and date2B ( both non keys) on file2. There is no common key between these two files to use hash file(server) or look up(PX). I can move this data to DB and use join there I completely lost performance and don't have space on system. Can you halp me in this? Thanks for your help.

Bobby Smith

[chulett]

Not sure why this was posted twice, except it looks like one was meant to go in the PX forum. You might want to delete it. (never mind, too late for that)

There's no easy answer... but there is an answer! This post has a way to do what you need to do in a hash, or can be adopted to do so. Hard to digest, but once it's firmly planted in your head it's not all that difficult to setup and it works great. (Thanks Ken!) I'm happily using the technique in a number of jobs.

Check it out. Otherwise, AFAIK, you are stuck with a SQL solution.

edit - Except for, apparently, the other options posted by Sainath!

[willpeng]

Not sure this would help performance wise, but I would design my job this way.

So both files do not have match keys, I am assuming you are comparing the dates row by row. 1 row compare with 1 row. I would generate a new seq file and a hash file with generated keys (row 1 = key 1, etc).

While I generate the keys, I will also convert all the dates into internal format.

The next step is do a lookup of the hash file using generated key and set a stage var to do a number comparison of the internal dates and flag the record. Example 1 for within dates and 0 for not.

Now you got a seq file with a generated key represent row #, all your dates, internal dates, and a flag telling you if the date is within the dates. Then you can use it to do whatever you wish.