Can sed be used to generate a load file for my date lookup fact table?

Question

I have an SQL fact table which is used for looking up a date and returning another date.

The table schema is as follows:

TABLE date_lookup
   (
pk_date DATE,
plus1_months DATE,
plus2_months DATE,
plus3_months DATE
   );

UNIQUE INDEX on date_lookup(pk_date);

I have a load file (pipe delimited) containing dates from 01-28-2012 to 03-31-2014.

The following is an example of the load file:

01-28-2012|02-28-2012|03-28-2012|04-28-2012|

01-29-2012|02-29-2012|03-29-2012|04-29-2012|

01-30-2012|02-29-2012|03-30-2012|04-30-2012|

01-31-2012|02-29-2012|03-31-2012|04-30-2012|

...

03-31-2014|04-30-2014|05-31-2014|06-30-2014|

The rules for this fact table are: If pk_date has more than 28 days in its month and plus1, plus2 or plus3_months only has 28, 29 or 30 days, then let plus1, plus2 or plus3 equal the last day of the following month.

My question is: Is it possible to use sed to generate more dates past 03-31-2014 following the above rules for my load file, or should I write a program to accomplish this?

Answer 1

I doubt that sed is the right tool for the job, in this case. I think you probably want to use awk, if you're already familiar with awk, otherwise, write a program.

I've known an engineer who used sed and awk to create MSC/NASTRAN input files, which had even stricter requirements than what you mention, but he was quite familiar with the tools, so cryptic and uninformative error messages from awk didn't bother him.

If you want to do this with awk, my advice would be to pre-process the input to remove '-' and '|' characters. Write the awk program in a separate file. Use text-field positions ($1, $2, $3...), and write SQL INSERT statements for the output. It will take several attempts, so automate as much as possible.

Answer 2

While it is technically possible to do date arithmetic in sed, it is not at all the right tool for the job. Use a tool like awk or perl which has integer arithmetic built in.

Your requirement is an unusual one for date manipulations, so you'll need a rich date manipulation library if you don't want to hard-code the date arithmetic. Perl's Date::Manip has functions like Date_DaysInMonth.

#!/usr/bin/env perl
use strict;
use warnings;
use Date::Manip;
use List::Util qw(/./);
sub shift_month {
    my ($delta, $y, $m, $d) = @_;
    $m += $delta;
    $y += $m / 12;
    $m %= 12;
    return ($y, $m, min($d, Date_DaysInMonth($m, $y)));
}
my $next_day = ParseDateDelta("1 day");
my ($cur, $end) = (ParseDate($ARGV[0]), ParseDate($ARGV[1]));
while (Date_Cmp($cur, $end) < 0) {
    my @ymd = UnixDate($cur, qw(%Y %m %d));
    foreach my $i (0..3) {
        printf "%02d-%04d-%02d|", @{[shift_month($i, @ymd)]}[1, 0, 2];
    }
    $cur = DateCalc($cur, $next_day);
    print "\n";
}