Yes, Bob it does - I missed that in your first posting 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: Saturday, February 19, 2005 4:17 PM Subject: [RBG7-L] - RE: "Working days" logic
> David, > I do mention in the email below (see 12th paragraph) that I keep a table > of Holidays. This holiday table normally only has 9 entries, (New Years, > Easter, Memorial Day, July 4th, Labor Day, 2 days for Thanksgiving and > 2 days at Christmas). Sometimes additional days may be added. > > The company maintains this one file, as it could very well change from > year to year. Sometimes they may close production down for the week > of July 4th for maintenance etc. Also, if a holiday falls on a weekend, > the company then records the Friday or Monday date of which is the > "day off". Keeping a holiday file of only a few dates is really no issue > and gives a lot of flexibility. What I did not want to do, was have to > create a table of every weekend date and maintain that from year to > year. Either way would work I suppose, just a preference. > > To account for the holidays, as mentioned, I simply perform a count of rows in > this table where the holiday date is between the order date and today's > date and subtract from the calculated number of working days. So in > essence, I calculate the number of working days then subtract any > holidays that may fall between the two dates. > > Hope that clears it up. > > -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: David M. Blocker [SMTP:[EMAIL PROTECTED] > Sent: Saturday, February 19, 2005 7:09 AM > To: RBG7-L Mailing List > Subject: [RBG7-L] - RE: "Working days" logic > > 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 > > > > > >
