Last Day of Month Future Past Derivation?

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Last Day of Month Future Past Derivation?

Post by jackson.eyton »

Hi guys,
I am working on replicating some trending jobs from our old data warehouse system. Those jobs had to be run manually and the last day of the month manually entered as the trending date. The issue is that sometimes the trend was run before or after the actual last day of month depending on where the last day of the month actually aligns with processing. I was thinking that there must be a way to collect the date for the last day of the month, and IF the current date is only 1-4 days into the month then get and use the previous months last day date. Every time I go to write out this derivation I keep confusing myself. Any help anyone could provide would be very greatly appreciated!!
-Me
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Just to clarify, are you asking about using parallel date functions within a parallel job or is this being done within a sequence job?

One way to approach it is to take the day of the month of the current system date, then test if that is <= 4 (or some cut-off day number), if so then reconstruct the date in the same month and year and use 1 as the day of the month number, then take that result and subtract 1 day from it to get the last day of the previous month.
Choose a job you love, and you will never have to work a day in your life. - Confucius
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

Hi again qt_ky!
This is indeed for a parallel job, I found the functions necessary are not available in sequence jobs. I was able to take your direction there and get something working rather well!
I had some other things I needed to do as well and was able to apply similar logic. What I have done so far is exactly what you said, take the current date and get the date of the first day of the following month, and subtract 1 day. That gives me the end of the current month. IF the day of the month of the current date is <= 4 then I just get the first day of the current month and subtract 1 day to get the last day of the previous month.

I am having an issue comparing variable values in triggers on a sequence job now but I will make a new post for that here soon if I can't figure it out.

Thanks again!!!

Variables Used:
CurrentYear = YearFromDate(CurrentDate())

CurrentMonth = MonthFromDate(CurrentDate())

CurrentDay = MonthDayFromDate(CurrentDate())

LastDayOfCurrentMonth = DateFromDaysSince(-1, DateFromComponents(YearFromDate(CurrentDate()), MonthFromDate(CurrentDate())+1, 1))

LastDayOfPrevMonth = DateFromDaysSince(-1, DateFromComponents(YearFromDate(CurrentDate()), MonthFromDate(CurrentDate()), 1))

ClosestEndOfMonthDate = IF CurrentDay <= 4 THEN LastDayOfPrevMonth ELSE LastDayOfCurrentMonth
-Me
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

MONTH.LAST and MONTH.FIRST are available as Transforms in server jobs.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Glad we could help! Thanks for sharing the variables you implemented.
Choose a job you love, and you will never have to work a day in your life. - Confucius
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

ray.wurlod wrote:MONTH.LAST and MONTH.FIRST are available as Transforms in server jobs. ...
That is good to know, and the only purpose of this particular job is to collect various date details and run some conditional logic on them so I might recreate the job as a server job. Thanks!
-Me
Post Reply