Ken, some years ago I did the same thing with a slightly different
design. I had completely forgotten it until now, as I have not worked
with that client (they went SQL Server because their database was
exceeding the 2 Gb file limits before there was 9.5 (64) ). Thanks for
reminding me.
Albert
On 5/7/2015 9:05 PM, Ken Shapiro wrote:
One of the things I did in an application was to build a full fledged
calendar into it. I defined a table with calendar date, flag for is
weekday and flag for is holiday. I have an procedure to populate the
table for any year and then the users can add in the federal holidays.
Using simple SQL statements you can do a lot of what you are asking in
this thread. You can embellish the table by adding in columns for next
and previous business days, day of the week, week number, month number
and whatever else you might want. Granted it’s a bit of work to get it
going, but you always then have it and the concept is reusable.
Ken
*From:*[email protected] [mailto:[email protected]] *On Behalf Of
*Karen Tellef
*Sent:* Wednesday, May 06, 2015 5:10 PM
*To:* RBASE-L Mailing List
*Subject:* [RBASE-L] - Re: Previous business day function?
Yes, what I needed was a business date that is "so many business days"
before a certain date, and the starting date can be a Saturday or
Sunday. The code I posted works so I'll stick with that for now, but
I'll probably test yours!
Karen
-----Original Message-----
From: Javier Valencia <[email protected]
<mailto:[email protected]>>
To: RBASE-L Mailing List <[email protected] <mailto:[email protected]>>
Sent: Wed, May 6, 2015 3:56 pm
Subject: [RBASE-L] - Re: Previous business day function?
Karen,
The code I posted before assumed that the starting day was always a
weekday and not a weekend. The following code should give you the
“previous” business date regardless of day of the week:
SET VAR vPWD = (IFEQ(TDWK(.vDate),'Monday',(.vDate -
3),IFEQ(TDWK(.vDate),'Sunday',(.vDate - 2),(.vDate -1) )))
For example if your starting date is Saturday, Sunday or Monday, it
will always will give you the date for the previous Friday. Is this
more along the lines of what you need?
The code above is the “previous” business date equivalent of the next
business day (DNW)…I think…
Now, if you need a date that is so many “business” days before a
certain date – say, what business day/date is 18 days before a
certain date? - then I am sure the code above can be modified to
include that as well…
Javier,
Javier Valencia, PE
O: 913-829-0888
H: 913-397-9605
C: 913-915-3137
*From:*[email protected] <mailto:[email protected]>
[mailto:[email protected] <mailto:[email protected]?>] *On Behalf Of
*Karen Tellef
*Sent:* Wednesday, May 06, 2015 1:26 PM
*To:* RBASE-L Mailing List
*Subject:* [RBASE-L] - Re: Previous business day function?
Yes, I already have the code to determine the number of business days
back (I'll copy the full code here in case anyone else finds it
useful). But the DNW function is so darn handy that I was just hoping
for a reverse-DNW!
-- Set the number of days to go backwards
SET VAR vDays = (.vRDays + .vPDays + .vSDays)
-- We start with the Due Date
SET VAR vBDate = .vDDate
SET VAR vCount INT = 0, vLoop INT = 1
WHILE vLoop <= .vDays THEN
SET VAR vTestDate = (.vBDate - 1)
IF (IDWK(.vTestDate)) BETWEEN 1 AND 5 THEN
SET VAR vLoop = (.vLoop + 1)
ENDIF
SET VAR vBDate = .vTestDate
ENDWHILE
Karen
-----Original Message-----
From: Javier Valencia <[email protected]
<mailto:[email protected]>>
To: RBASE-L Mailing List <[email protected] <mailto:[email protected]>>
Sent: Wed, May 6, 2015 1:15 pm
Subject: [RBASE-L] - RE: Previous business day function?
Karen,
Yu can probably use the following workaround;
SET VAR vDate DATE = .#DATE -- or whatever date you want
SET VAR vPWD = (IFEQ(TDWK(.vDate),'Monday',(.vDate -3),(.vDate-1)))
The function TDWK return the “Day” of the week as text.
Now, if your date is on a Monday, it will return the previous Friday,
otherwise it will return the previous day. Just like the DFNW it does
not consider holidays, although you can create stored function with
lookup table that does that for you.
You can also use the function :
IDWK(arg)
Which will return the day of the week as an INTEGER and then instead
of “Monday” you compare it to 1.
Probably other ways to do it but the ones above seem to be pretty
straight forward.
Javier,
Javier Valencia, PE
O: 913-829-0888
H: 913-397-9605
C: 913-915-3137
*From:*[email protected]
<mailto:[email protected]>_[_mailto:[email protected]
<mailto:[email protected]?>_] *On Behalf Of *Karen Tellef
*Sent:* Wednesday, May 06, 2015 10:49 AM
*To:* RBASE-L Mailing List
*Subject:* [RBASE-L] - Previous business day function?
I'm happily using the DNW(.vdate) function with a loop to go out
business days. But now I also need a function that goes backwards to a
PREVIOUS business day. I don't see a function for that, right? So I
have to do the day of the week thing?
Karen
--
A democracy ..." can only exist until the majority discovers it can vote itself
largess out of the public treasury."
Attributed to Alexander Fraser Tytler 1747-1813
