Re: Calculate working days using stored procedure

Sorry to follow up on my own post. I couldn't get Nil's post and I wanted to respond to it. My comments are separated by a -=- at the top and bottom to help eliviate confusion. -Mikey -=- Nils.Myklebust@nmdata.com (Nils Myklebust) on 06/30/98 04:21:26 PM Please respond to Nils.Myklebust@nmdata.com (Nils Myklebust) [SNIP] On Mon, 29 Jun 1998 07:51:27 -0500, Mike Segel <mikey@segel.NOSPAM-.KING.OF.MYDOMAIN.-NOSPAM.com> wrote: >Nils Myklebust wrote: >> >> This is a slightly difficult problem with many solutions. Here is one >> possible (I haven't tested this so check everything for yourself): >> >Actually it doesn't work too well. O? >The problem is that you are going to create a huge database of normal >days. Huge? 3650 + something for 10 years is hardly huge to me. And remember, most days would be automatically inserted by a program for the purpose (Jonathan's bussiness day functions could probably be used as a starting point - possibly reprogramming in some language fitting for a GUI). -=- Well If you are dealing with only one country and only one holiday schedule, you have roughly 250 working days. (365 - 104 weekends - holidays. YMMV) For 10 years, that 2,500 or so rows. Compared to 11 holiday days as in jack's example, or 110 holidays for 10 years. 2500 is >> than 110. -=- >The other problem is defining what a *Holiday* is. Easily handled by defining them in the table. Anything goes. You can easily create multiple callendars by simply adding another field to the table (for countries, special work schedules or whatever). -=- But now we have lets say Jul 14th. Thats a French holiday. So do we consider it a work day? So your workday table would have to have 250 rows per country except Germany which would have less. ;-) So if you have 10 countries, then your table is 10 times larger. (So will the holiday table too.) 25,000 >> 1,100 rows. -=- >I suggest that you have a date field, and a country code as a minimum. >For example, the US celibrates the 4th of July. France celibrates the >14th of July. Some may need it, others not. -=- If you are going to do something, do it right. The table structure should be generic enough to allow for multiple holidays depending on the region/national holiday schedule. The solution of determining a holiday should be universal such that you can plug the code in to any application you are writing. Its known as reusable code. Its a great concept, and the code/table to handle universal holidays isn't much larger. The table width is insigingicant based on the number of rows you will be managing. Remember the data within the rows are going to be application specific and are user maintained. -=- >Also if the *holiday* falls on a weekend, the holiday may be taken on >July 3rd, or July 5th. Handled by the table maintenance. >Let us also keep in mind that some *holidays* are still work days. Yes, as defined in the table. >What will work, yet will not be as efficient as a +10, would be a while >loop or some other >looping structure. The problem here is that for every round in the loop except for saturdays and sundays (if you as you assume here want to exclude those) you have to do a lookup in your holliday table. One should try to avoide single row reads when set oriented operations or other more efficient ways are possible. -=- Uhhm, Singleton selects occur all the time. Also, considering that you won't have too much data, you could do the selection once and store the dates in memory. ( Going back to Jack's example, 11 days per year is < 64 bytes of information. (4 byte word length for a DATE datatype.) Of course depending on your design YMMV) But again, I left the implementation design up to the developer. I just gave you a framework. How you implement the Is_Holiday() routine is up to you. ;-) -=- [SNIP] >Now unless Informix has changed their ways, small tables. (Under 2000 >rows do a sequential >scan rather than use the index.) So you want to maintain a smaller >table. Besides its easier >to maintain a list of holidays, rather than a list of work days. Not much when you have a rather simple program for the purpose. If you want to maintain the hollidays do so and let the program automatically mark/insert other days as working days. Hardly different at all. I still think that a full callendar table is the best and most flexible solution in most cases. Above all it gives the users complete control and full overview of what is defined as working days, hollidays and possibly other types of days as well as making it real easy to implement any type of callendar without changing any code except possibly in the maintenance program to make that job faster and simpler. The critical part that does the final calculations never has to be changed and allways works to the spesifications of the end user (not the programmer). Remember the customer is allways right (even when they are clearly wrong - but then they take the consequences and you charge for fixing it :-). The only negative aspect I can see is there may be some performance issues in some cases. In that case one of the other suggested coded (or partly coded) solutions may be faster. [SNIP] -=- Sigh. Managing holidays means you have less data. Again I used the example of 10 countries with holidays out for 10 years. In a financial application this is the norm. You are talking about trying to create a table of dates where a simple day of week function call will tell you if it is a working day or a weekend. Then you only have to check to see if its an applicable holiday. Again the data is always either provided for initially, or managed by the customer. Full calendar is not the best solution or the most flexible. You would have to write a simple program to load the table with the inital *working days*. The question is why? You have a function already present with Informix to determine the day of week. You can set up a knowledge based application to determine what constitutes a working day. That brings up a sticky issue with your solution. You track *workdays*. What happens when you have a company which runs 7x24 and the *workday* definition is different based on the employee or group of employees? If you tried to manage the *workdays* you will have a sticky situation. If you tried to only manage the *holidays* you will have a lot less rows. Ok, so in this example the need for country codes is not necessary. But that doesn't mean you have to change your database table. You are still provinding a generic solution. You will only have to change the Is_Workday() function which is application specific. Is_Holiday() and the database table structures don't have to change. Again, the software and schema are reusable. -=- Note to the peanut gallery. While this topic may seem trivial and too much thought has been wasted, it isn't as trivial as it may seem. In the financial world, you have to worry about business days and holidays when evaluating options, or even payment dates in some derivitives. The penalties for missing even a day could