Find previous business day
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
Find previous business day
Hi All,
Do you have any idea on how to find the previous business/working day from the given date. is there any inbuilt function available or do we need to write any routine...
Kindly let me know your thoughts on the same.
Thanks.
Do you have any idea on how to find the previous business/working day from the given date. is there any inbuilt function available or do we need to write any routine...
Kindly let me know your thoughts on the same.
Thanks.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This is a universal problem best solved through use of a calendar file/table.
"Business day" could be any day of the week. For example, today is Monday in the USA, but it's a public holiday and therefore not a business day (officially) despite the fact that most stores are open and having sales.
"Business day" could be any day of the week. For example, today is Monday in the USA, but it's a public holiday and therefore not a business day (officially) despite the fact that most stores are open and having sales.
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.
If you don't have a calendar function, like in Control-M, a simple design suggestion:
Identify all jobs that depend on having an accurate previous business day date.
Have the last job update a previous business date file with the current date. This implies that all of those jobs must complete before the update.
The next business day jobs use the file for the previous business day date.
We use this design to avoid problems such as not all jobs conform to the holiday schedule. The challenge is grouping jobs that depend on the same date setting, and give each group its own file.
Identify all jobs that depend on having an accurate previous business day date.
Have the last job update a previous business date file with the current date. This implies that all of those jobs must complete before the update.
The next business day jobs use the file for the previous business day date.
We use this design to avoid problems such as not all jobs conform to the holiday schedule. The challenge is grouping jobs that depend on the same date setting, and give each group its own file.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
Re: Find previous business day
If you have list of holidays loaded into a file or a table, do a lookup, you can use the below query in case of DB2 and write it to a file:
SELECT CASE WHEN DAYNAME(CASE('#PprmCycledate#' AS DATE)) = 'Saturday'
THEN CAST('#PprmCycledate#' AS DATE) - 1 DAY
END A CycleDate FROM SYSIBM.SYSDUMMY1
SELECT CASE WHEN DAYNAME(CASE('#PprmCycledate#' AS DATE)) = 'Saturday'
THEN CAST('#PprmCycledate#' AS DATE) - 1 DAY
END A CycleDate FROM SYSIBM.SYSDUMMY1
Thanks
Sumith
Sumith
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
Hi All,
Appreciate for your information.
Currently we are building CalendarHoliday table for different regions(US/UK & ANZ)...which includes public holidays and weekends(Sat&Sun). Once this gets done we will do a lookup against the table to get the required date.
Basically we are looking to calculate the delivery date from the given date with the priority the customer would choose.
Thanks.
Appreciate for your information.
Currently we are building CalendarHoliday table for different regions(US/UK & ANZ)...which includes public holidays and weekends(Sat&Sun). Once this gets done we will do a lookup against the table to get the required date.
Basically we are looking to calculate the delivery date from the given date with the priority the customer would choose.
Thanks.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Code: Select all
SELECT A.THE_DATE FROM CALENDAR A WHERE A.THE_DATE = (SELECT MAX(B.THE_DATE) FROM CALENDAR B WHERE B.THEDATE <= A.THE_DATE AND B.BUSINESS_DAY = 'Y');
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.