Dynamic SQL script

sasrs99 has asked for the wisdom of the Perl Monks concerning the following question:

I apologize for the long post. I have a very interesting problem and I need some help. I have a table that holds history quantities (called HISTORYREPORTMONTHLYBYSKU), and it basically keeps a running 2 yrs worth of data. and the format is LOCATION, ITEM, and the quantities are in MTH_00, MTH_01, MTH_02, MTH_03, etc. MTH_00 is current month, MTH_01 is last month, MTH_02 is the month before that, etc. OK, here's the problem. The user enters a value equal to the number of days into my script. I need to run out and query this table and return the greatest of two caluculations. The first calculation is easy, it's simply the sum of the past 12 months divided by 365 times the number of days the user entered. The tricky part is the other calculation which is the sum of the quantities for a number of months in the past. Let me show you an example. Let's assume the user entered a # of days = 120 and today is Nov 15th, 2006. I need to sum 4 monthly buckets(120 days / 30 days per month. Yes, assume 30 days per month). So, for this example, I need to sum MTH_08 + MTH_09 + MTH_10 + MTH_11. If they had entered 90 days, I'd sum 3 buckets (90/30 days in the month) and the months would be MTH_09 + MTH_10 + MTH_11. I need a way to create a query that looks like this:

SELECT GREATEST(ROUND((SUM(mth_01 + mth_02 + mth_03
                                   + mth_04 + mth_05 + mth_06
                         + mth_07 + mth_08 + mth_09
                         + mth_10 + mth_11 + mth_12) / 365 ) * $num_da
+ys_of_supp )
                         , ROUND (SUM (mth_08 + mth_09 + mth_10 + mth_
+11)))  QTY, b.p_dlrnet, b.p_dlrsplrcode
    FROM pac.HISTORYREPORTMONTHLYBYSKU a
        , stsc.sku b
    WHERE a.loc                  = ?
    AND a.dmdunit                = ?
    AND a.loc                    = b.loc
    AND a.dmdunit                = b.ITEM
    GROUP BY a.loc
            , a.dmdunit
            , b.p_dlrnet, b.p_dlrsplrcode
[download]

Comment on Dynamic SQL script Download Code

Replies are listed 'Best First'.
Re: Dynamic SQL script by jhourcle (Prior) on Nov 15, 2006 at 21:33 UTC
`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?	[reply] [d/l]
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
Re^5: Dynamic SQL script by sasrs99 (Acolyte) on Nov 16, 2006 at 16:49 UTC
Some notes below your chosen depth have not been shown here