Hi clx, thank you very much for taking your time with this.

I have as well developed similar awk codes to what you have. Posting it as it might help others as well:

Code:

for files in dirs
   do
      ###search records from file using column 1 from records in lookup_file and store to temp file (normal join)
      awk 'FNR==NR{a[$1]=$0} NR>FNR && ($1 in a){ print $0,a[$1] } ' lookup_file files > tempfile
      ###compare the lookupdate (column 3 from the file) from start/end date (column 2 and 3 of lookup file) and write to report
      awk '{lookup=$3; start=$9; end=$10; getline;} start <= lookup && end >= lookup {print $0}' tempfile >> reportfile
   done

What the code does is to join records from the file to the matching records from the lookup.
So columns 2/3 from the lookup is now column 9/10 of the joined file. And since all the dates that i needed are already in 1 file, it was easier for awk to compare them. (I took away the adding of the last column as well since it was already available upon joining the records)

This took away a big chunk of code from my script and was able to breeze through 30 million lines in around 10 minutes, which is already tolerable.

Again, thanks!