process for previous month data range

[RC99]

we need to process previous month's data. so if it is any day in March we want only february data. We currenly use jobhiararchy system interacting with a param.ini file, which is set to provcess the previous run's date until th eporesent, in conjunction with an ETL_RUN_CTL tavble that stores the last run's completion date. w usually run for example for src begin date = 2/28 to src end date = 3/1. or if we don't run for a few days it might be 2/28 to 3/3

our internal code is like this :

WHERE FIELD_HISTORY.MODIFY_DATE >= TO_DATE('#SrcBegDt#', 'yyyy-mm-dd hh24:mi:ss') and FIELD_HISTORY.MODIFY_DATE < TO_DATE('#SrcEndDt#', 'yyyy-mm-dd hh24:mi:ss')

with the #SrcBegDt# and #SrcEndDt# parameters linked to our param in file and processed against our run ctl table for begin date.

but we have the need now for something that would be automated in data stage to extract previous month's data only.
I did something like this in Oracle function 5 years ago - but it's a distant memory - was very tedious as I remember. It took in leap years, etc.

thanks ahead of time :D

[clshore]

You can use the Oracle add_months() function to get previous month/year:

prev_month_year = to_char(add_months(sysdate,-1),'MMYYYY')

So your condition would look something like this:

WHERE to_char(FIELD_HISTORY.MODIFY_DATE, 'MMYYYY') =
to_char(add_months(sysdate,-1), 'MMYYYY')

Remember to add checks for null dates, etc.

Carter

[Sainath.Srinivasan]

Or use MONTHS_BETWEEN Oracle function.

[RC99]

You can use the Oracle add_months() function to get previous month/year:

prev_month_year = to_char(add_months(sysdate,-1),'MMYYYY')

So your condition would look something like this:

WHERE to_char(FIELD_HISTORY.MODIFY_DATE, 'MMYYYY') =
to_char(add_months(sysdate,-1), 'MMYYYY')

Remember to add checks for null dates, etc.

Carter

thanks
wouldn't htis suplly us with simply the month?

but would that get the data from, say if it was March - we need to get all Loans processed between 2/1 to 2/28? we would need to get the beginning of month date and the end of month date, and process where modify_date is between them. No??
thanks

[Sainath.Srinivasan]

Then Months_Between(TransactionDate, FixedDate) <= 1 will do the trick.

[clshore]

That's the whole point, it compares the month/year of the candidate record to month/year of previous month, and processes if the same (isn't that what you want?)

So if you run it right now:

select to_char(add_months(sysdate,-1), 'MMYYYY') from dual

You get the month/year of the previous month (year is important):

022005

And this gets the month/year from your record:

... to_char(FIELD_HISTORY.MODIFY_DATE, 'MMYYYY')

If they are equal, means the date is in the range '2005/02/01-2005/02/28'
and the record should be processed.

Carter

You can use the Oracle add_months() function to get previous month/year:

prev_month_year = to_char(add_months(sysdate,-1),'MMYYYY')

So your condition would look something like this:

WHERE to_char(FIELD_HISTORY.MODIFY_DATE, 'MMYYYY') =
to_char(add_months(sysdate,-1), 'MMYYYY')

Remember to add checks for null dates, etc.

Carter

thanks
wouldn't htis suplly us with simply the month?

but would that get the data from, say if it was March - we need to get all Loans processed between 2/1 to 2/28? we would need to get the beginning of month date and the end of month date, and process where modify_date is between them. No??
thanks

[clshore]

No, I don't think so:

select
months_between(to_date('2005-02-01','YYYY-MM-DD'),sysdate) lo,
months_between(to_date('2005-02-28','YYYY-MM-DD'),sysdate) hi
from dual

gives:

lo hi
------- -------
-1.466 -.595

We want to process all records in range 2005-02-01 to 2005-02-28, but above would exclude the first record using your logic.

Carter

Then Months_Between(TransactionDate, FixedDate) <= 1 will do the trick.

[Sainath.Srinivasan]

You are correct. The months_between was intended to use exact one month in date rather than to obtain any date that lies in the last month.

[ray.wurlod]

Create two simple routines FirstOfPreviousMonth and LastOfPreviousMonth and invoke them through Routine Activities in a job sequence. Use the return value from the routine to supply the value to the job parameters.

Code: Select all

FUNCTION LastOfPreviousMonth(Arg1)
* Arg1 is an internal format date, for example @DATE.
* If empty, @DATE is substituted.

If (Arg1) = "" 
   Then CurrentDate = @DATE
   Else CurrentDate = Iconv(Arg1, "DYMD")

FirstOfCurrentMonth = Iconv(Oconv(CurrentDate, "D-YM") : "-01", "DYMD")
LastOfPreviousMonth = FirstOfCurrentMonth - 1

Ans = Oconv(LastOfPreviousMonth, "D-YMD[4,2,2]")

RETURN(Ans)

Code: Select all

FUNCTION FirstOfPreviousMonth(Arg1)
DEFFUN LastOfPreviousMonth(X) Calling "DSU.LastOfPreviousMonth"

EndDate = LastOfPreviousMonth(Arg1)

Ans = EndDate[1,8] : "01"

RETURN(Ans)

[RC99]

thanks
Carter and Ray
I will try BOTH of these - to add to our repetoire for the customer here- will keep you posted on results

GRACIAs seniors - you're the men

[RC99]

You are correct. The months_between was intended to use exact one month in date rather than to obtain any date that lies in the last month.

thx for the input

results to come
Bob