Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: reading records backwards Post Reply Post New Topic
Author Message
kammertime
Newbie
Newbie


Joined: 18 Jun 2012
Online Status: Offline
Posts: 3
Quote kammertime Replybullet Topic: reading records backwards
     Posted: 19 Jun 2012 at 5:59am
Let me start by stating that I am a novice , at best, when it comes to Crystal Reports. I am using Ver 11.5 in a MRP database.
 
I have a purchase order file with a current due date field. I need to calculate the po due date by subtracting the stock lead time from the current due date which is simple to do. The problem is that i need to eliminate "non work" days from the equation. I also have a "shop calendar" file which consists of 2 fields; the calendar date and a boolean field that is true if it is a "work" day and false if a "non work" day.  The current due date in the purchase order file is linked to the calendar date in the shop calendar file.
How do I "read backwards" the shop calendar file until I have the correct po due date ?
Here is an example:
current po due date = 6-19-12
stock lead time = 3 days
Shop calendar records:
6-19-12  true
6-18-12  true
6-17-12  false
6-16-12  false
6-15-12  true
6-14-12  true
The po due date would be 6-14-12
 
Any help would be appreciated,
John
 
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 19 Jun 2012 at 11:28am
I don't think you're going to be able to do this directly in Crystal because the logic is too complex.  What type of database are you using?  Do you have the option of creating a stored function in your database?  If so, you could pass the current due date and the number of lead days into the function and get back the po due date using a query something like this:
 
Select Min(CalendarDate)
from (
  Select CalendarDate
  from ShopCalendar
  where CalendarDate < date parameter
    and WorkDay = 'true'
  order by CalendarDate desc)
where rownum <= lead days parameter
 
-Dell
IP IP Logged
yggdrasil
Senior Member
Senior Member
Avatar

Joined: 19 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 150
Quote yggdrasil Replybullet Posted: 20 Jun 2012 at 1:13am
I do a similar thing in many reports where I need to know Days Between.
I have a formula where you insert your start and end dates  as below.
 This already deals with weekends, but needs another formula called Holidays for public holidays which are non-working days in your country. For the format of that see further down 
 
//Days Between
WhileReadingRecords;
Local DateVar Start := date({Command.Review Date});   // place your Starting Date here
Local DateVar End := date({@end_date});  // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateTimeVar Array Holidays;
Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0)  +
(if DayOfWeek(End) = 7 then -1 else 0);  
Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays ) in 2 to 6 and
     Holidays in start to end then Hol:=Hol+1 );
Weeks + Days - Hol-1
 
 
 
//Holidays
 
BeforeReadingRecords;
DateTimeVar Array Holidays := [
datetime (2012,01,02,00,00,00),
datetime (2012,04,06,00,00,00),
datetime (2012,04,09,00,00,00),
datetime (2012,05,07,00,00,00),
datetime (2012,06,04,00,00,00),
datetime (2012,06,05,00,00,00),
datetime (2012,08,27,00,00,00),
datetime (2012,12,25,00,00,00),
datetime (2012,12,26,00,00,00),
datetime (2013,01,01,00,00,00),
datetime (2013,03,29,00,00,00),
..........
datetime (2020,12,28,00,00,00)
];
0


Edited by yggdrasil - 20 Jun 2012 at 1:14am
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum