Finding an Open Shop Day 1

[pticarl]

7.6.100a Macola (SQL)
Crystal Reports v7.0

I am working on a Vendor Performace report and have run into a problem. I would like to compare when we receive materials on a PO against the request date of the PO. I can do this part fine, however the problem comes in when I want to determine if the receipt date is within +/-2 business days of the request date (this determines if the order was received "on time"). I would like to check against the shop calendar for open shop days as this will remove any hassles with having to manually track weekends or holidays.
I currently am using a Crystal Report with the POLINHST_SQL table. Any ideas how I can validate the receipt date to be within that 2 business day window? For some reason I can't get my head around this part of the process.
My goal is to track the number of receipts/orders per month and having running totals of early, on time and late deliveries. I will then calc. a vendor rating based on these numbers.

TIA and sorry if this is too vague

 

[crystalreporting]

You have a number of options - the first two DO NOT reference the shop calendar:

Option 1: Use a formula to calculate the number of working days (excluding weekends and holidays) e.g.

dateVar array holidays := [Date(2001,1,1),Date(2001,2,19),Date(2001,5,28),Date(2001,7,4),
Date(2001,9,3),Date(2001,11,22),Date(2001,12,25)];
dateVar range drange := {@requested} to {@received};
dateVar datecheck := {@requested};
numberVar wkday := Day (datecheck) ;
numberVar WrkCount := 0;
while (datecheck in drange) do
(

Wkday := Day (datecheck);
if Wkday in [2 to 6] then WrkCount := WrkCount + 1;
if datecheck in holidays then WrkCount := WrkCount -1;
datecheck := datecheck + 1;

);
if wrkcount < 0 then wrkcount := 0;
wrkcount

The formulas {@requested} and {@received} would contain the normal NumberToDate functions to convert the PO numeric dates to 'real' dates. The holidays date array has the seven holidays normally observed. Make the adjustments you need to reflect the holidays in your organization.

Option 2: Use a Crystal UFL (user function library) to calculate the difference between two dates - this UFL has several functions and makes use of text files to signal legal holidays and weekends.

You can download the UFL from:

http://support.seagatesoftware.com/communityCS/FilesAndUpdates/

BusinessDays.zip.asp

Option 3: Use a subreport to access the Shop Calendar. Link your subreport to the PO requested date field in your main report, then edit your subreport and change the criteria to look at any open shop calendar dates -2 or +2 days around your linked requested date. Sort your subreport on date, and use the min and max functions to get the earliest and latest shop dates. Pass these values into shared variables, and supress all of the sections in the subreport. Back in your main report, you can then access the shared variables to test if your date is within + or -2 days using actual shop calendar days.

Enjoy!



Peter Shirley

http://www.linkcorp.com

 

[pticarl]

Peter,

I wanted to stick with the Shop Calendar table as we also use some non-standard holiday days as well as scheduled plant shutdowns etc. To that end I have taken a similar approach in the subreport that you suggested but with a little different twist.
I pass to the subreport the the receipt and request dates without having them automtically selecting data. I used them in the subreport select by pulling all open days from the Shop Calendar between the two values and perform a running total on them. I assign the early/late days to a shared variable that I use to test early/ontime/late shipments based on the number of days in the shared var as well as the receipt/request days.

It seems to work ok also but I can't get one of the running totals to come out like it should. Once I get a final solution I will post back.


Thanks for the help!
Carl

 

[crystalreporting]

You mean I typed all of that for nothing?!?! Just kidding, glad you came up with a solution.

Peter Shirley

http://www.linkcorp.com

 

[pticarl]

Peter,


Sorry for not replying that I had come up with a possible solution. I appreciate the effort you put into your post and it helped me a good bit by giving a concrete example to contrast to my report.

Thanks again and a star for you!!


Carl