Ron, Here is a post from Bob Thompson that may help you.
Jan --message follows The solution I came up with is not one single sql command, but it does not require any Declare cursors, tables holding all non-work days and does not require adjustments for passing years or months. Due to Rbase's internal calendar, it also is not affected by leap years. The basis of my logic is determining the day of the week the order date was placed. I.E. 1 for Monday, 2 for Tuesday , 3 for Wednesday etc. I was able to use the TDWK function to obtain this and simple IFEQ statements. Then I had three possible scenarios to take into consideration. 1. The order date and today's date were in the same week 2. The order date and today's date were in different weeks and the order date's "day of the week" was earlier than todays day of the week. I.E. The order was placed on a Tuesday and today is a Thursday. 3. The order date and today's date were in different weeks and the order date's "day of the week" was later than todays. I.E. The order was placed on a Thursday and today is a Monday. With the above, I can calculate out the number of working days for any two dates with the following 3 commands. In my case I was updating a temporary table named Temptab2 used in a view. The column IntDayWk in temptab2 was updated from the sales order date to the "integer day of the week" value as described above. Note that in an earlier section of the program, company policy was any order date that was a Saturday or Sunday was moved to Monday. -- Updates WorkDays column for records where salesorderdate and -- today's date are in the same week. Update Temptab2 set WorkDays = (.VReportDate - SalesOrderDate)+ where (IntDayWk = 1 and (.VReportDate - SalesOrderDate) <= 4) or+ (IntDayWk = 2 and (.VReportDate - SalesOrderDate) <= 3) or+ (IntDayWk = 3 and (.VReportDate - SalesOrderDate) <= 2) or+ (IntDayWk = 4 and (.VReportDate - SalesOrderDate) <= 1) -- Updates Workdays column for records that today's date and the -- salesorderdate are in different weeks and the SalesOrderDate integer -- is less than today's integer date. The below formula gives the number -- of weekends between the two dates. So the elasped number of days -- minus the number of weekend days gives work days. Update Temptab2 set WorkDays = + ((.VReportDate - SalesOrderDate) - (((INT((.VReportDate - SalesOrderDate) / 7))) * 2))+ where (IntDayWk <= .VcurrIntDay) and (+ (IntDayWk = 1 and (.VReportDate - SalesOrderDate) > 4) or+ (IntDayWk = 2 and (.VReportDate - SalesOrderDate) > 3) or+ (IntDayWk = 3 and (.VReportDate - SalesOrderDate) > 2) or+ (IntDayWk = 4 and (.VReportDate - SalesOrderDate) > 1) or+ (IntDayWk = 5 and (.VReportDate - SalesOrderDate) > 1)) -- Updates Workdays column for records that today's date and the -- salesorderdate are in different weeks and the SalesOrderDate integer -- is greater than today's integer date. Again elasped days minus number -- of week end days gives work days. Update Temptab2 set WorkDays = + ((.VReportDate - SalesOrderDate) - (((INT((.VReportDate - SalesOrderDate) / 7)) + 1) * 2))+ where (IntDayWk > .VcurrIntDay) and (+ (IntDayWk = 1 and (.VReportDate - SalesOrderDate) > 4) or+ (IntDayWk = 2 and (.VReportDate - SalesOrderDate) > 3) or+ (IntDayWk = 3 and (.VReportDate - SalesOrderDate) > 2) or+ (IntDayWk = 4 and (.VReportDate - SalesOrderDate) > 1) or+ (IntDayWk = 5 and (.VReportDate - SalesOrderDate) > 1)) To account for holidays, I simply have a table "Holidays" that the company maintains the holiday dates in, which only number 11 records. These dates are also "week day" dates only. A simple count and subtract statement then reduces the number of work days by the counted holidays that fall between the order date and today's date. I have tested the above extensively and it confirms the logic. I get the number of work days (based upon a 5 day work week) for every order with these 3 update commands. I know I probably did a poor job explaining it, but here it is none the less. My explanation is far longer than the code! So with 3 commands on one table, I calculate the number of work days between any two dates. A fourth command subtracts any holidays. I do not have to maintain a table of 52 dates that changes each year and the speed is almost instant on over 1000 open orders. A declare cursor would be much slower. If a company works a seven day week, then the job would be easy, Simply elapsed days. Finding the number of weekends was the trick. Perhaps not the most elegant, but it works! Let me know if you have any questions in my long winded explanation! -Bob Thompson Technology Consultants 276 Chippewa LaPorte, IN 46350 219-324-2605 (Phone & Fax) 219-363-7441 (Cell) http://ttc.inc.home.comcast.net
