process for previous month data range
Moderators: chulett, rschirm, roy
process for previous month data range
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
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
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
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
thanksclshore wrote: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
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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
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
RC99 wrote:thanksclshore wrote: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
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
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
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
Sainath.Srinivasan wrote:Then Months_Between(TransactionDate, FixedDate) <= 1 will do the trick.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.