You might try something like this (I haven't tested this, so you may have to tweak the syntax a bit):
StringVar result := "Good";
DateVar checkDate;
DateVar endDate;
NumberVar numDays := 0;
BooleanVar notFulfilled := false;
if IsNull({myTable.FulfilledDate}) then
(
endDate := CurrentDate;
notFulfilled := true;
)
else
endDate := {myTable.FulfilledDate};
else
(
checkDate := {MyTable.RequestDate};
While (result = "Good") and checkDate < endDate Do
(
checkDate := checkDate + 1;
if DayOfWeek(checkDate) in 2 to 6 then
numDays := numDays + 1;
if numDays > 3 then
if notFulfilled then
result := "Open";
else
result :="Bad";
)
);
result
If the result is "Bad", then it doesn't meet the standard, if it's "Open" then it needs to be watched.
This doesn't account for holidays, though. The challenge is that the holiday dates change every year and are based on which country you're in. So, Crystal doesn't have a way of identifying whether any given date is a holiday. The only way I know of to handle this involves the creation of a "calendar" table in the database that has something like the following format:
CalDate DateTime
DaySequence Number(6, 0)
CalDate is, of course, the date in question. DaySequence gets incremented according to the following rules:
Holidays have the same value as the preceding day.
Weekend days have the same value as the last non-holiday day before the weekend. So, for example, looking at the first week of July 2013, you might have something that looks like this:[code]
CalDay DaySequence
---------------- ---------------
7/1/2013 401
7/2/2013 402
7/3/2013 403
7/4/2013 403
7/5/2013 404
7/6/2013 404
7/7/2013 404
7/8/2013 405
It then becomes fairly easy to write a stored function in the database to get the number of business days between two dates by subtracting the DaySequence of the first date from the DaySequence of the second date. This function could be called in a SQL Expression in Crystal to get the value you're looking for.
-Dell
IP Logged
Sheri
