Re: Calculate working days using stored procedure

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). >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). >You will need to create a *holiday* table. I suggested adding hollidays if you need those for some purpose. >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. >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. >int day_count; >DATE start_date, curr_date; >curr_date = start_date; >while(day_count >0) { > if ( Is_weekday(curr_date) ){ > if (Is_holiday(curr_date) == 0) { > day_count--; > } > } > cur_date++; >} > >Where: > DATE is the datatype for DATE; > And Is_weekday checks to see that the day of week is not 0 or 7 > (Going from memory of the Informix function to return the day of week) > > Is_holiday is the function that checks the database. > > Implementation is left as an exercise for the student. > >Caveats: > While this may be slower than a simple arithmatic function, it does >allow for the > flexibility for the program/application to define what they consider a >working day. > > Most US companies have 7 paid vacation days which can be set on a >whim. Some companies > choose 6 holidays, then have a floating 7th day. Germany has 21 >national holidays. > >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. >HTH >-Uncle Mikey > >> If you create a table thus: >> >> create table calendar( >> caldate date, >> workdayno integer);>> >> primary key caldate >> index on workdayno >> >> and insert at least the dates that are working days with workdayno >> starting on 1 for the first date that is a workday and increased by 1 >> for every following date that is also a workday, you can do as follows >> for your particular calculation: >> >> select caldate from calendar >> where workdayno = (select workdayno + 10 from calendar where caldate = >> mdy(6,27,1998))>> >> For a more general calendar I would probably put in a few more fields >> like a marker for hollidays and possibly others. >> This table will not contain many rows even if you need dates for 10 or >> 20 years. >> >> On Wed, 24 Jun 1998 22:36:22 GMT, sjsyau@my-dejanews.com wrote: >> >> >Please help me here. >> > >> >Given: >> > >> >Today's date + Working Days >> >Today's date - Working Days >> > >> >I need to find out the result in date format. The working days means to >> >exclude weekends and holidays. >> > >> >Ex, >> > 06-27-1998 + 10 >> >should = 07-10-1998 Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html (Now with ODBC info under "Third party products".)