Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Combined the continuous records into one Post Reply Post New Topic
Author Message
albert
Newbie
Newbie


Joined: 16 Nov 2010
Online Status: Offline
Posts: 5
Quote albert Replybullet Topic: Combined the continuous records into one
    Posted: 17 Nov 2010 at 10:59am

Hi there, I use Crystal Reports XI. I have a question and need helps ASAP.
I have to compare the records' dates to determine whether they can be combined to one record or not. For example:

ID              Date1          Date2
1111111  10/14/2009  11/9/2009
1111111  11/9/2009    1/11/2010
1111111  1/11/2010    (blank)
1111111  1/11/2010    1/11/2010
1111111  1/11/2010    6/9/2010
 
The IDs on the above table are the same. And I want to get rid of the continuous records into one record.

Which is the result should be : 
ID              Date1          Date2
1111111  10/14/2009  (blank)
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 18 Nov 2010 at 3:38am

you could use a global variable or two.

have a formula that keeps the start/end dates and compare each record to the values and updates them as needed.  if it updates, set a flag to suppress the record (or maybe it has to be an additional detail section to suppress the next detail section)  when you get to your blank or whatever means the end of a continous record, the use your variables to print date1 and date2
 
something like:
shared datetimevar startDate;
shared datetimevar endDate;
shared boolenvar supFlag := false;
 
if {table.date1} = endDate then(
  endDate := {table.date1};
  supFlag := true;
);
"" //hide formula output
 
then in section expert you can use the supFlag to supress a section.
when you want print the value, write a formula to access startDate.
 
there will need to be some code to set/reset the startDate.
 
HTH
IP IP Logged
albert
Newbie
Newbie


Joined: 16 Nov 2010
Online Status: Offline
Posts: 5
Quote albert Replybullet Posted: 18 Nov 2010 at 4:10am
Thanks. What if I need the multiple Date1 and Date2 for the same ID? Do I have to sort by ID, Date1, and Date2 in ascending in order to make the formula work?

Original Data:
ID          Date1         Date2
1111111    3/16/2009      6/27/2009
1111111    8/20/2009      9/10/2009
1111111    10/14/2009     11/9/2009
1111111    11/9/2009      1/11/2010
1111111    1/11/2010      (blank)
1111111    1/11/2010      1/11/2010
1111111    1/11/2010      6/9/2010
1111111    8/20/2010      9/10/2010

Final Result:
ID          Date1         Date2
1111111    3/16/2009      6/27/2009
1111111    8/20/2009      9/10/2009
1111111    10/14/2009      (blank)
1111111    8/20/2010      9/10/2010

I am thinking I have to group by ID and Date1, any help? Thank you so much.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 22 Nov 2010 at 6:45am
sounds good.  I was just looking at the example and assuming that it would be ordered correctly.
 
Glad to help
IP IP Logged
albert
Newbie
Newbie


Joined: 16 Nov 2010
Online Status: Offline
Posts: 5
Quote albert Replybullet Posted: 09 Dec 2010 at 6:24am
Hi there, I still stuck in the same problem that I posted on Nov 16, 2010. Any help? Pinch
IP IP Logged
albert
Newbie
Newbie


Joined: 16 Nov 2010
Online Status: Offline
Posts: 5
Quote albert Replybullet Posted: 17 Dec 2010 at 7:12am
The idea is to eliminate the continuous or repeated records for the same person. The ID indicates a person, Date1 is an open date, and Date2 is a close date. If ID are same, and the date1 is equal to the previous date2, then we keep the earliest date1, which is the previous open date (date1) here and keep the latest close date (date2) if date2 is not empty.

However, empty Date2 means it is not close yet and I am going to take that rather than other Date2 if it is the continuous cases or same Date1 for these records with same ID as well.

If the open date (Date1) is after at least 3 days than the previous close date (previous Date2), we treat it as a new record and it is not continuous even though their ID are same.

I am able to compare two records at a time, but I have no idea to do it when it happens across more than 2 continuous records and not even mention to delete these records and arrange their open date and close date.
Please Help!
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.047 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.