Dynamically created SQL statement

ssmith001 · #1 (**permalink**) 11-10-06, 18:31

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.

sco08y · #2 (**permalink**) 03-04-07, 16:57

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.

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech