Find previous business day

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
satheesh_color
Participant
Posts: 182
Joined: Thu Jun 16, 2005 2:05 am

Find previous business day

Post by satheesh_color »

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.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

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.
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
Sumith
Participant
Posts: 5
Joined: Tue Sep 04, 2018 8:37 am

Re: Find previous business day

Post by Sumith »

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
Thanks
Sumith
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Not really a complete/generic solution, though, is it? :cry:
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
satheesh_color
Participant
Posts: 182
Joined: Thu Jun 16, 2005 2:05 am

Post by satheesh_color »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

So, resolved it would seem - yes?
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
Post Reply