Jim and Sami,
Sorry for the delay in posting my reply. It has been a busy week. 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 -----Original Message----- From: Jim Buddemeyer [SMTP:[EMAIL PROTECTED] Sent: Tuesday, February 15, 2005 4:31 PM To: [EMAIL PROTECTED] Subject: [RBG7-L] - RE: "Working days" logic I had fun playing with this one evening last week and came close to the right formula but occasionally would be a day off. I came up with a counting loop that skipped the weekends (using IDWK function) which worked but it was not a formula. I would enjoy seeing the code that works. Jim Buddemeyer Director Clinical Engineering Boone Hospital Center. >>> [EMAIL PROTECTED] 02/11 10:11 PM >>> Thank for the replies on the working days. After getting a headache trying to figure out how to accomplish this without creating a table of weekend dates, (This would have to be maintained yearly, etc.) I finally figured out a logic equation that works. Rbase, with all the available functions is great and you can do anything! It is late and I will not post the solution unless someone really needs it. Although it works great, I am not too sure my explanation would fit in a general email! Anyway, thanks again for the suggestions and my project problem has been resolved! -Bob Thompson Technology Consultants 276 Chippewa LaPorte, IN 46350 219-324-2605 (Phone & Fax) 219-363-7441 (Cell) http://ttc.inc.home.comcast.net -----Original Message----- From: Troy Sosamon [SMTP:[EMAIL PROTECTED] Sent: Tuesday, February 08, 2005 10:50 PM To: RBG7-L Mailing List Subject: [RBG7-L] - RE: "Working days" logic Oops, I left some dots out: Set var date1 date = 01/01/2005 Set var date2 date = 02/07/2005 Select ((.date2 - .date1) - (count(*)) as NumWorkDays from nonWorkDays where DateOff between .date1 and .date2 You might need to use & in the where clause. You get the idea. -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Troy Sosamon Sent: Tuesday, February 08, 2005 9:32 PM To: RBG7-L Mailing List Subject: [RBG7-L] - RE: "Working days" logic There are a few ways to do this. The trickiest thing about this is if you have to take holidays into account too. This is what you do. Create a new table called nonWorkDays with a single date field called dateOff. You could get fancy and put a computed column for the day of week and maybe a comment to. Now add a row in this table for every non working day for the max date range you need. Be sure to put the holidays that you don't work. Now your calculation is easy. To get the number of working days between date1 and date2, you just need to do this: Set var date1 date = 01/01/2005 Set var date2 date = 02/07/2005 Select ((date2 - date1) - (count(*)) as NumWorkDays from nonWorkDays where DateOff between date1 and date2 There are several cleaver ways to do this by putting in a view, or maybe making a stored procedure, but this will always work. Troy -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Thompson Technology Consultants Sent: Tuesday, February 08, 2005 9:01 PM To: RBG7-L Mailing List Subject: [RBG7-L] - RE: "Working days" logic A reporting project includes the requirement of giving the age of orders in a system. The definition of age that is required is (Today's date - Date Order was placed) and subtract any weekends. I.E. Order age is number of working days from today's date back to the order entry date, weekends excluded. The orders can range from 1 day old to several weeks. An order placed on Monday with a "today" date of Friday would be 5 working days. However an order placed on Friday with a today's date of Tuesday would only be 3 working days although 5 calendar days had passed. Anyone have a good logic process of determining this? I want to avoid using a Declare Cursor if at all possible. Thanks for any assistance! -Bob Thompson Technology Consultants 276 Chippewa LaPorte, IN 46350 219-324-2605 (Phone & Fax) 219-363-7441 (Cell) http://ttc.inc.home.comcast.net
