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!!
Last Day of Month Future Past Derivation?
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 145
- Joined: Thu Oct 26, 2017 10:43 am
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.
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
-
- Premium Member
- Posts: 145
- Joined: Thu Oct 26, 2017 10:43 am
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 145
- Joined: Thu Oct 26, 2017 10:43 am