Loop for previous month number of days
Moderators: chulett, rschirm, roy
Loop for previous month number of days
Hi,
I have a job that I have to run trhu the number of days in previous month.
For example if the previous month is Jan or March 31 days.
In Feb 28 or 29 depending on leap year.
Any thoughts?
Thanks
I have a job that I have to run trhu the number of days in previous month.
For example if the previous month is Jan or March 31 days.
In Feb 28 or 29 depending on leap year.
Any thoughts?
Thanks
Champa
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Participant
- Posts: 251
- Joined: Mon Jun 09, 2008 5:52 am
Re: Loop for previous month number of days
You can try using the below oracle query to get the number of days in previous month and then loop your job to run number of days coming out of below query.Champa wrote:Hi,
I have a job that I have to run trhu the number of days in previous month.
For example if the previous month is Jan or March 31 days.
In Feb 28 or 29 depending on leap year.
Any thoughts?
Thanks
SELECT TRUNC(ADD_MONTHS(SYSDATE, -1), 'MONTH') AS LAST_MONTH_FIRST_DATE,
last_day(add_months(sysdate, -1)) + 1 as CURRENT_MONTH_FIRST_DAY, substr((last_day(add_months(sysdate, -1)) + 1) - TRUNC(ADD_MONTHS(SYSDATE, -1), 'MONTH'),1,2) AS NO_OF_DAYS
FROM DUAL
Re: Loop for previous month number of days
You can try using the below oracle query to get the number of days in previous month and then loop your job to run number of days coming out of below query.Champa wrote:Hi,
I have a job that I have to run trhu the number of days in previous month.
For example if the previous month is Jan or March 31 days.
In Feb 28 or 29 depending on leap year.
Any thoughts?
Thanks
SELECT TRUNC(ADD_MONTHS(SYSDATE, -1), 'MONTH') AS LAST_MONTH_FIRST_DATE,
last_day(add_months(sysdate, -1)) + 1 as CURRENT_MONTH_FIRST_DAY, substr((last_day(add_months(sysdate, -1)) + 1) - TRUNC(ADD_MONTHS(SYSDATE, -1), 'MONTH'),1,2) AS NO_OF_DAYS
FROM DUAL
-
- 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:
U (one of our posters) did not ask the question.srinivas.g wrote:U need to use sequencer looping activities
The first person personal pronoun in English is spelled "you", not "u".
Please strive for a professional standard of written English on DSXchange, to aid those whose first language is not English.
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.
I did use start loop/end loop stages.
Also wrote a routine in datastage manager to get days in previous month.
_____________________________________________________________
* The routine gives the number of days in previous month and previous month's year & month.
*
Ans=0
ArgOut=0
TODAY = OCONV(Date(),'D4-')
YY = TODAY[7,4]
MM = TODAY[1,2]
MM = MM - 1
IF MM = 0 THEN MM = 12; YY = YY - 1
IF MM = 1 OR MM = 3 OR MM = 5 OR MM = 7 OR MM = 8 OR MM = 10 OR MM = 12 THEN DAYS = 31 ELSE DAYS = 30
IF ((MM = 2) AND (YY/4 = 0)) THEN DAYS = 29 ELSE IF MM = 2 THEN DAYS = 28
ArgOut=DAYS
*ArgOut=29
*ArgOut2=200912
ArgOut2=YY:MM
____________________________________________________________
The topic is resolved.
Also wrote a routine in datastage manager to get days in previous month.
_____________________________________________________________
* The routine gives the number of days in previous month and previous month's year & month.
*
Ans=0
ArgOut=0
TODAY = OCONV(Date(),'D4-')
YY = TODAY[7,4]
MM = TODAY[1,2]
MM = MM - 1
IF MM = 0 THEN MM = 12; YY = YY - 1
IF MM = 1 OR MM = 3 OR MM = 5 OR MM = 7 OR MM = 8 OR MM = 10 OR MM = 12 THEN DAYS = 31 ELSE DAYS = 30
IF ((MM = 2) AND (YY/4 = 0)) THEN DAYS = 29 ELSE IF MM = 2 THEN DAYS = 28
ArgOut=DAYS
*ArgOut=29
*ArgOut2=200912
ArgOut2=YY:MM
____________________________________________________________
The topic is resolved.
Champa
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: