Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Dynamically created SQL statement

    I need help creating a Perl function that will dynamically create the following SQL statement:

    Each one of the mth_XX buckets contains a history quantity value.

    Code:
    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 ) * 120 ) 
        , 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			= 'K531'
    AND a.dmdunit		= '5-280X'
    AND a.loc			= b.loc
    AND a.dmdunit		= b.ITEM
    GROUP BY a.loc
           , a.dmdunit
           , b.p_dlrnet, b.p_dlrsplrcode
    The only part of this query that changes dynamically is this part: mth_08 + mth_09 + mth_10 + mth_11

    The months to include here are based on the number of days in the query, which in this case is 120. Yes, I am assuming all months have 30 days.

    The monthly buckets to use are determined by the current month. If the user enters a # of days = 60 and the current month is 11, the placeholder string should be "mth_10, mth_11". If they enter 90, the placeholder string should be "mth_09, mth_10, mth_11". If they enter 75 , the placeholder string should be "mth_09, mth_10, mth_11/2" (note that I want 1/2 of mth_11 value)

    I need some way of taking this number, dividing it by 30 and get the # of monthly buckets to use and then creating this placeholder string.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Okay, two things:

    If you're adding a + b + c + d, there's no need to wrap it in SUM().

    Either do SUM(a, b, c, d) or a + b + c + d, not both.

    The monthly buckets to use are determined by the current month. If the user enters a # of days = 60 and the current month is 11, the placeholder string should be "mth_10, mth_11". If they enter 90, the placeholder string should be "mth_09, mth_10, mth_11". If they enter 75 , the placeholder string should be "mth_09, mth_10, mth_11/2" (note that I want 1/2 of mth_11 value)
    Given $curmth is a value from 0 to 11, where 0 is january, and $numdays, you're looking for something like this:

    Code:
    @mths = $curmth .. ($curmth + $numdays / 30); # Gets a list of months
    $fracdays = $numdays % 30;  # Gets the remainder 
    push @mths, $_mths[-1] + 1 if $fracdays; # Add the fractional month if necessary
    @mths = map("mths_" . sprintf("%02d", $_ % 12 + 1)), @mths); # give months the proper names
    $_mths[-1] .= " * $fracdays / 30" if $fracdays; # add the fractional expression to the fractional month
    $placeholder = join(' + ', @mths); # glom it all into one string.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •