Re: Dynamic SQL script

my $current_month = 11;
my $num_days_of_supp = 120;

my @months_of_support = ( ($current_month-($num_days_of_supp/30)+1) ..
+ $current_month );
my $sql = 'SELECT GREATEST(ROUND((SUM'
  . join( '+', map { sprintf "mth_%02i", $_ } ( 0 .. 12 ) )
  . ") / 365 ) * $num_days_of_supp ), ROUND (SUM ("
  . join('+', map { sprintf "mth_%02i", $_ } @months_of_support)
  . ')))  QTY, b. p_dlrn, b.p_dlrsplrcode '
  . <<EOF;
FROM ...
EOF
[download]

That'll create the output you wanted from the input given (well, fill in the ... )-- but I'm not sure that's really what you want, given what you said. If mth_01 is the current month, why are you looking at mth_08 to mth_11 for the last 11 days?

Comment on Re: Dynamic SQL script Download Code

Replies are listed 'Best First'.
Re^2: Dynamic SQL script by sasrs99 (Acolyte) on Nov 15, 2006 at 21:37 UTC
MTH_00 is current month. I'll see if I can work with your suggestion. Stay tuned...	[reply]
Re^3: Dynamic SQL script by sasrs99 (Acolyte) on Nov 15, 2006 at 21:53 UTC
It works great with $current_month = 11. When I set $current_month = 10 I get (mth_07 + mth_08 + mth_09 + mth_10) but it should be (mth_09 + mth_10 + mth_11 + mth_12). $current_month = 9 should yield (MTH_10 + mth_11 + mth_12 + MTH_13), etc. I think we are close	[reply]
Re^4: Dynamic SQL script by jhourcle (Prior) on Nov 16, 2006 at 13:02 UTC
I think the problem is that I don't understand the logic you're using to determine the months to use. (if mth_00 is the current month, and mth_01 is the previous month, I'd have actually expected you to count from 0 out to the number of months back that you want, and the current date to be insignificant in the calculation) Anyway, just figure out which mth_xx fields you want, and adjust the line that sets `@months_of_support` so the logic is correct.	[reply] [d/l]
Re^5: Dynamic SQL script by sasrs99 (Acolyte) on Nov 16, 2006 at 16:49 UTC
Re^6: Dynamic SQL script by sasrs99 (Acolyte) on Nov 17, 2006 at 18:19 UTC