Dan: I maintain a date lookup table of in my databases that simplifies a lot of the date calculations that I need. It covers the dates from 01/01/2002 through 01/02/2021.
A view based on the table has built-in calculations for starting and ending dates for weeks, starting and ending dates for calendar months and fiscal months plus a few other handy calculations. That being said, maybe the example below can help. Holidays and other special events can also be worked into this depending on how the table is structured. DROP VIEW TEMPVIEW CREATE TEMPORARY VIEW TEMPVIEW (DAY_DATE,WEEKDAY) + AS SELECT DAY_DATE,(IFLT((IDWK(DAY_DATE)),6,1,0)) + FROM LKUPDATE RETURN Using the view to check for number of business days between 01/01/2017 and 01/15/2017 returns these results: SELECT COUNT(*) AS TTL_DAYS,SUM(WEEKDAY) AS BUSI_DAYS FROM TEMPVIEW WHERE DAY_DATE BETWEEN 1/1/2017 AND 1/15/2017 TTL_DAYS = 15, BUSI_DAYS = 10 SELECT COUNT(*) AS TTL_DAYS,SUM(WEEKDAY) AS BUSI_DAYS FROM TEMPVIEW WHERE DAY_DATE BETWEEN 1/3/2017 AND 7/21/2017 TTL_DAYS = 200, BUSI_DAYS = 144 Hopefully this might give you some ideas or options. Regards. Mike Ramsour AK Steel Coshocton Works Quality Department Phone: 740-829-4340 Cell: 740-502-1659 From: Dan Goldberg <d...@lancecamper.com> To: "rbase-l@googlegroups.com" <rbase-l@googlegroups.com>, Date: 09/05/2017 03:28 PM Subject: RE: [RBASE-L] - calculate number of business days Sent by: rbase-l@googlegroups.com Thx that is what I was thinking of doing. Just a little slower but will work. Dan Goldberg From: karentellef via RBASE-L [mailto:rbase-l@googlegroups.com] Sent: Tuesday, September 5, 2017 12:07 PM To: rbase-l@googlegroups.com Subject: Re: [RBASE-L] - calculate number of business days I'm not aware of any function that will do it. Here's a simple while loop, in case you don't already do this. vCount would have the #days SET VAR vDays = (.vedate - .vbdate) SET VAR vCount INT = 0, vLoop INT = 0 WHILE vLoop <= .vDays THEN SET VAR vTestDate = (.vbdate + .vLoop) IF (IDWK(.vTestDate)) BETWEEN 1 AND 5 THEN SET VAR vCount = (.vCount + 1) ENDIF SET VAR vLoop = (.vLoop + 1) ENDWHILE Karen -----Original Message----- From: Dan Goldberg <d...@lancecamper.com> To: rbase-l <rbase-l@googlegroups.com> Sent: Tue, Sep 5, 2017 1:04 pm Subject: [RBASE-L] - calculate number of business days I need to calculate the number of business days between to date values. Which gives me the number of days. But I need to calculate the number of days without weekends. I cannot find a function that will achieve the results in need. Does anyone have an idea on how to calculate it? TIA Dan Goldberg -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to rbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to rbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to rbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. Confidentiality Notice This message is intended exclusively for the individual or entity to which it is addressed and may contain privileged, proprietary, or otherwise private information. If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please notify the sender immediately by e-mail and delete all copies of the message. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to rbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
