DSXchange

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

you can always get the first day of next month then use datefromdayssince() (or something like that) function with number of days as '-1' to get the number of days in that month.

I know how to get number of days in previous month.

What I need is how to loop say from 1 to number of days in previous month in datastage.

Thx.

U need to use sequencer looping activities

Can you please give what stages

The Start Loop and End Loop stages, oddly enough. Optionally a User Variables stage as well to derive values at runtime, for example your number of times to loop. Read the docs, they are explained there with several examples.

Thanks all, I will give it a try.

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

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.

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

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

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.

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

Nope.

This is where your calendar / date dimension comes handy.

srinivas.g wrote:U need to use sequencer looping activities

U (one of our posters) did not ask the question.

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.

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.

Your routine could be made more efficient, and does not include a full leap year rule (there's also a "divisible by 400" part to the rule).