What Is The Best Way To Populate A Load File For A Date Lookup Dimension Table?

Informix 11.70.TC4: I have an SQL dimension table which is used for looking up a date (pk_date) and returning another date (plus1, plus2 or plus3_months) to the client, depending o

Solution 1:

The best technique would be to upgrade to 11.70.TC5 (on 32-bit Windows; generally to 11.70.xC5 or later) and use an expression such as:

SELECTDATE(given_date + n UNITS MONTH)
  FROM Wherever
...

The DATETIME code was modified between 11.70.xC4 and 11.70.xC5 to generate dates according to the rules you outline when the dates are as described and you use the + n UNITS MONTH or equivalent notation.

This obviates the need for a table at all. Clearly, though, all your clients would also have to be on 11.70.xC5 too.

Maybe you can update your development machine to 11.70.xC5 and then use this property to generate the data for the table on your development machine, and distribute the data to your clients.

If upgrading at least someone to 11.70.xC5 is not an option, then consider the Perl script suggestion.

Solution 2:

Can it be done with SQL? Probably, but it would be excruciating. Ditto for C, and I think 'no' is the answer for sed.

However, a couple of dozen lines of perl seems to produce what you need:

#!/usr/bin/perluse strict;
use warnings;
use DateTime;

my @dates;

# parse argumentswhile (my $datep = shift){
    my ($m,$d,$y) = split('-', $datep);
    push(@dates, DateTime->new(year => $y, month => $m, day => $d))
        || die"Cannot parse date $!\n";
}

open(STDOUT, ">", "output.unl") || die"Unable to create output file.";
my ($date, $end) = @dates;
while( $date < $end ){
    my @row = ($date->mdy('-')); # start with pk_dateformy $mth ( qw[ 1 2 3 ] ){
        my $fut_d = $date->clone->add(months => $mth);
        until   (
                    ($fut_d->month == $date->month + $mth
                       && $fut_d->year == $date->year) ||
                    ($fut_d->month == $date->month + $mth - 12
                       && $fut_d->year > $date->year)
                ){
                    $fut_d->subtract(days =>1); # step back until criteria met
                }
        push(@row, $fut_d->mdy('-'));
    }
    print STDOUT join("|", @row, "\n");
    $date->add(days =>1);
}

Save that as futuredates.pl, chmod +x it and execute like this:

$ futuredates.pl 04-01-201412-31-2020

That seems to do the trick for me.