Bob THis is fascinating, a very different way of looking at it. What I don't follow - and don't see in your email, is how you handle the holidays without having a calendar of the actual dates!!?
David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: "Thompson Technology Consultants" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[email protected]> Sent: Friday, February 18, 2005 9:55 PM Subject: [RBG7-L] - RE: "Working days" logic > > > 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 > >
