Unanswered: Windowing Function Range Between Dates
We have a table that looks something like
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
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?
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).
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.)