Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2013
    Posts
    19

    Unanswered: Windowing Function Range Between Dates

    Hi,

    We have a table that looks something like

    Code:
    LN_NBR RPT_DATE SN_AMT
    My requirement is to select the MIN(SN_AMT) for a given LN_NBR within a given set of RPT_DATE. The RPT_DATE range is dynamic. The starting point of the range of RPT_DATE would be "The Last Day of the month 2 months before the business date" while the end point would be "The Last Day of the month 1 month before the supplied business date".

    I am trying to implement this using a windowing function that looks like

    Code:
    SELECT MIN(SN_AMT) OVER (PARTITION BY LN_NBR ORDER BY RPT_DATE RANGE BETWEEN (StartDate) AND (EndDate)
    Can someone please help me with what should go into the StartDate and EndDate?

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    jerome.r. I would write the SQL pretty much like you wrote the sentence.

    Select the MIN(SN_AMT) (SELECT MIN(SN_AMT) for a given LN_NBR (WHERE LN_NBR = given-value) within a given set of RPT_DATE range (RPT_DATE BETWEEN) , stating point range would be Last Day of month 2 months before business date (LAST_DAY(business_day - 2 MONTHS) and end point would be Last day of the month 1 month before business date (LAST_DAY(business_day - 1 MONTH).

    OR
    Code:
    SELECT MIN(SN_AMT)
    FROM table-name
    WHERE LN_NBR = given-value
      AND RPT_DATE BETWEEN LAST_DAY(business-day - 2 MONTHS)
                       AND LAST_DAY(business-day - 1 MONTH)
    (Depending on the version of DB2 (which you don't mention), the function LAST_DAY may or may not be available.)

Posting Permissions

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