473,889 Members | 2,022 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Calculating Business Days

Is anyone aware of a function (system or user defined) that will
calculate business days? For instance: I have a column in as table
called DATE. I want to be able to add five business days to that date
and come up with a new date. Is that possible.

Also, is there anyway that DB2 can be aware of holidays? Maybe load
them onto the server in some type of reference file or something.

I ask these questions because I'm working on a banking application and
these are two MAJOR hurdles for us to get over.

Any help would be appreciated.

Thanks!
Nov 12 '05
12 23934
AK
Christian,

I think your approach runs slower than mine if you need to add, for
instance, 10 business days. Also your approach would work 10 times
slower when adding 100 business days, because it menas 10 times more
iterations.
Also I think your approach uses up significantly more CPU, which is
precious because processors are pricey and IBM charges by the
processor too.

What do you think?
Nov 12 '05 #11
Hi AK,
I think your approach runs slower than mine if you need to add, for
instance, 10 business days. Also your approach would work 10 times
slower when adding 100 business days, because it menas 10 times more
iterations.


I used a generalised calender function (always returns a year of
days), you can add a day count parameter to the calendar function so
it wouldn't waste iterations.

I'd say there is a good chance a permanent calendar table and a
function built on top would run faster than a generated calendar, but
you never know - unless you have the time to test it out (Serge would
have a better idea of the performance than me). I just thought it was
worth presenting an alternative approach.

Christian.
Nov 12 '05 #12
AK
Hi Christian,

I'd say there is a good chance a permanent calendar table and a
function built on top would run faster than a generated calendar, but
you never know - unless you have the time to test it out (Serge would
have a better idea of the performance than me). I just thought it was
worth presenting an alternative approach.


I was doing comparisons like that 2 years ago with 7.1, at that time
recursion was consistently MUCH slower and it was using more CPU.
Since then, a lot could have changed, but I haven't re-evaluated that
old decision to avoid recursion whenever possible

One more thing: a query with a simple SQL UDF like the one I posted
might be re-written by the optimizer and get a much better plan. I
think queries with recursion cannot be rewritten.

Please correct me if I'm wrong
Nov 12 '05 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4260
by: David Stockwell | last post by:
I''m wondering if the Calendar object has an option to only do calculations based on business days (ie M-F). Additionally does it have a way to get holidays into itself? Currently I'm considering converting my calculations over to business days and am wondering if there is anything built-in. I've tried searching but so far haven't seen such a thing built in. I'm thinking I'm just going to have to bite the bullet and create my own...
1
3738
by: igendreau | last post by:
I have users inputting a "Request Date". Upon entering a date, I need Access to populate a second field ("Due Date"). When they enter their Request Date, I want Access to set the default value of Due Date = Request Date + 9 Business Days (Holidays don't matter. Just want Saturday and Sunday taken out). Any thoughts?
7
26000
by: Sam | last post by:
Hi, I use C# in my ASP.NET projects. Here's what I need to do: I want to add x business days to a given date i.e. add 12 business days to today's date. What is the best, fastest and most efficient way for me to do this? -- Thanks, Sam
8
7534
by: =?Utf-8?B?QWw=?= | last post by:
I am working in vb2005. how can I calculate business days (not including holidays and weekends) between 2 dates? thanks Al
1
7201
by: santhoshrms | last post by:
I need to be able to determine the number of business days between two dates. Is there a function/Select Query in DB2 that can be used to do this? ...
1
2453
by: ArchMichael | last post by:
i need help again on calculating business days excluding holidays i have a field called assign date and i need to calculate 7 business days excluding holidays ( already have a table for holiday) from that date. i have read some forums on getting total business day but not the other way around
5
24575
FishVal
by: FishVal | last post by:
IMHO, the following is not a how-to-do instruction to solve a particular problem but more a concept-proof stuff demonstrating possibilities of SQL. So, let us say the problem is to calculate business days count which is defined as count of days (optionally inclusive in the current implementation) excluding weekend days and holidays. Let us say periods to calculate are stored in table associated with contacts. keyPeriodID -...
0
1445
by: josephsimonbenn | last post by:
I need to calculate the number of business days employees work in each month using vba. The problem is, some employees work 3 days, some 4 and some 5. So i want to be able to turn on/off the days via checkboxes. I found this code... which will basically count the days that aren't saturday or sunday, but Can someone modify this code so that the days are only counted if the checkboxes are selected.... Checkboxes are called: ChkSunday,...
3
9952
by: PotatoChip | last post by:
I'm working in an Access XP database and I need to create a query which calculates what the date will be 6 business days after . I have no idea where to start and most posts I find on calculating business days pertains to subtracting data in two date fields. I can build an expression in my query to add the date, Date_Due: + 5 but that doens't address the business days. Thanks in advance!
0
9970
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
10794
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10896
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10443
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7153
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6031
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4652
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4251
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3259
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.