Page 1 of 1
Windowing Function Range Between Dates
Posted: Wed May 29, 2013 4:59 am
by 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
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?
Posted: Wed May 29, 2013 7:04 am
by chulett
What database?
Posted: Wed May 29, 2013 7:07 am
by jerome_rajan
Db2. I'm sorry, I ought to have mentioned it.
Posted: Wed May 29, 2013 7:23 am
by 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?
Re: Windowing Function Range Between Dates
Posted: Wed May 29, 2013 1:51 pm
by 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?
Posted: Wed May 29, 2013 2:17 pm
by 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.
Posted: Wed May 29, 2013 4:18 pm
by 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.
Posted: Thu May 30, 2013 12:00 am
by jerome_rajan
Server Jobs aren't an option in my project. Is there a way to achieve it in my parallel job?
Posted: Thu May 30, 2013 12:07 am
by jerome_rajan
Got a hang of how I should do it. Will post the solution once I get through.
Posted: Thu May 30, 2013 12:16 am
by ray.wurlod
jerome_rajan wrote:Server Jobs aren't an option in my project. Is there a way to achieve it in my parallel job?
BASIC Transformer stage.
Posted: Thu May 30, 2013 12:22 am
by 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.