Thank you very much! ---- Original Message ---- From: [EMAIL PROTECTED] To: [email protected] (RBG7-L Mailing List) Subject: RE: [RBG7-L] - Re: Difference in days Date: Mon, 31 Oct 2005 14:34:23 -0800
>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 >
