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]

In reply to Dynamic SQL script by sasrs99

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.