DSXchange

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!!

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.

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

MONTH.LAST and MONTH.FIRST are available as Transforms in server jobs.

Glad we could help! Thanks for sharing the variables you implemented.

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!