Unix script to process data by comparing it to same key records

Question

I have a problem in which Input file has Effective date and End date records. First 6 fields are the keys (12345A). We need to update End date(18 position) based on effective(8 position) date of the next record with same key - 1 day. For the records with newest effective date, 9999-12-31 should remain in End date. Below are the input and output expectations. Could someone please help me in this. I am fetching the file A as table unload and this has to be send later to interfacing application which is expected the end date to be populated like this, we can not change end date on table itself thats why we are trying to change it on unload file.

There is no data separator/delimiter and yes length of keys will always remain same.

Input (FILEA):

12345A22021-01-259999-12-31 12345A12021-01-019999-12-31 12345B32021-02-159999-12-31 67899C12021-03-019999-12-31 67899D32021-05-249999-12-31 67899D22021-04-029999-12-31 

Output (FILEB):

12345A22021-01-259999-12-31 12345A12021-01-012021-01-24 12345B32021-02-159999-12-31 67899C12021-03-019999-12-31 67899D32021-05-249999-12-31 67899D22021-04-022021-05-23 

Answer 1

As long as the effective date of the preceding record with same ID shall be taken as end date, you can do it with standard sed and the N;P;D pattern like this:

sed '$!N;s/^\(.\{6\}\)\(.\)\(.\{10\}\)\(.*\n\1.\{11\}\).*/\1\2\3\4\3/;P;D' 

But as soon as you need to change the date, you need a utility that understands a calendar like GNU date:

date -d "2021-09-08 yesterday" +"%Y-%m-%d" 

will output 2021-09-07. I doubt many versions of date will do the trick that way. But with a current GNU date and GNU sed, this should work:

sed -nE 'G s/^(.{6})(.{11}).*\n\1.(.{10}).*/\1\2\3/p s/\n.*//p s/^(.{7})(.{10})/echo \1$(date -d "\2 yesterday" +"%Y-%m-%d")/e h' fileA > fileB 

Let me try to explain:

• We use option n to suppress default output and option E for extended regular expressions (just for readability)
• G appends the hold space, where we are going to keep the previous line with adapted date (see below)
• s/^(.{6})(.{11}).*\n\1.(.{10})/\1\2\3/p replaces the date of the current line with the adapted date from hold space, only if it has the same ID (thus the backreference \1) and prints it
• s/\n.*//p simply removes the appended line if no replacement did take place and prints it
• s/^(.{7})(.{10})/echo \1$(date -d "\2 yesterday" +"%Y-%m-%d")/e make use of GNU sed's execute flag to send the date to date utility to decrement it
• h saves this changed line to hold space