Windowing Function Range Between Dates

[jerome_rajan]

Hi,

I wasn't too sure if this was the right place to post but I'm sure there are some SQL gurus here too. Kindly help.

We have a table that looks something like

Code: Select all

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 all

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?

[chulett]

What database?

[jerome_rajan]

Db2. I'm sorry, I ought to have mentioned it.

[chulett]

So basically you are looking for SQL to derive the two "last day of the month" values? Do you have access to a date dimension, anything that tracks "business days" in your organization?

[MrBlack]

I don't have access to DB2 but here's how you'd do it in Oracle, maybe you can translate the logic to DB2

Code: Select all

SELECT SYSDATE AS Today, 
    TRUNC(ADD_MONTHS(SYSDATE, -1), 'Month') -1 AS Minus_2_Months,
    TRUNC(ADD_MONTHS(SYSDATE, 2), 'Month')-1 AS Plus_1_Month
  FROM dual

So for the start date, it would be take the DAY (May 29) go back one month (Apr 29), truncate to the first of April (Apr 1) then subtract one day = March 31

Then the end date would be Day (May 29), add 2 months (July 29), truncate to the first of July (July 1) then minus a day = June 30

So in my example I use SYSDATE you can either replace that with a variable or a system day and that would provider your moving window.

Did I understand your question correctly?

[chulett]

Looks like I may have been attempting to over-complicate it... I misread it to include a number of business days since x, hence my question regarding a date dimension. As long as "the business date" equates to the current system date then you should be able to do this fairly easily.

[ray.wurlod]

Server jobs (and BASIC Transformer stages) give you access to the MONTH.LAST and MONTH.FIRST transforms (as well as a host of others). That might also be a nice solution.

[jerome_rajan]

Server Jobs aren't an option in my project. Is there a way to achieve it in my parallel job?

[jerome_rajan]

Got a hang of how I should do it. Will post the solution once I get through.

[ray.wurlod]

Server Jobs aren't an option in my project. Is there a way to achieve it in my parallel job?

BASIC Transformer stage.

[ray.wurlod]

Date of the first of the month can be obtained using DateFromDaysSince() or DateOffsetByDays(), using MonthDayFromDate() as the offset argument.

Date of the last day of the previous month can be obtained using DateFromDaysSince() or DateOffsetByDays() using (MonthDayFromDate() + 1) as the offset argument.