Combined the continuous records into one

[albertleung]

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)

 

[lbass]

Insert a group on ID. Insert a minimum on date1 and drag it into the group header. Then create a formula for date2:

if isnull({table.date2}) then
date(9999,9,9) else
{table.date2}

Insert a maximum on this and drag it into the group header. Right click on the formula->format field->suppres->x+2:

currentfieldvalue = date(9999,9,9)

This will make the null the maximum date and it will appear as a blank, otherwise the most recent date will appear. Finally suppress the detail section and group footer.

-LB

 

[albertleung]

Thank you so much lbass. 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.

 

[lbass]

You are changing your requirements? Not quite clear. What happened to date1 = 11/9/2009?

-LB

 

[albertleung]

Yes, I have changed the requirement.
From the following records:

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
...

I only need one record as of the following:
ID Date1 Date2
1111111 10/14/2009 (blank)

Same result as I asked at the very beginning. However, for now, I need multiple records for the same ID if possible. I really need help for that, thanks in advance.

 

[lbass]

You say you need only one record and then that you need multiple records. Not following.

-LB

 

[albertleung]

One record for eliminating continuous records. Actually the first Date2 is as same as the second Date1 and you will see it goes along these records. However, the blank Date2 means the person (ID: 1111111) is not done yet, so that's the final Date2 I need. And I need the very beginning Date1 which is 10/14/2009 for this person. And of course I need the other records for this person if the date is not continuous like the example I posted previously.

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
...

Hope you can help me out since I stuck here. Thank you very much.

 

[lbass]

Okay, now I see what you mean. Keep the early formula I suggested for Date 2, and I think you should keep the group on ID and sort ascending on {table.date1} and then on {@date2}. Then create the following formula and add it to the detail section:

whileprintingrecords;
datevar start;
datevar end;
datevar prev := start;
if onfirstrecord or
{Table.ID} <> previous({Table.ID}) then
start := {Table.Date1} else
start := start;
if not onfirstrecord and
{Table.Date1} = previous({Table.Date2}) then
start := start else
start := {Table.Date1};
if onfirstrecord or
{Table.ID} <> previous({Table.ID}) then
end := {Table.Date2} else
if not onlastrecord and
{Table.ID} = next({Table.ID}) and
{Table.Date2} <> next({Table.Date1}) then
end := date({Table.Date2}) else
end := end;
if onlastrecord or
{Table.ID} <> next({Table.ID}) then
end := {Table.Date2};
totext(start,"M/dd/yy") &
space(24-len(totext(end,"M/dd/yy"))) & (
if end <> date(9999,9,9) then
totext(end,"M/dd/yy") else
"In Process"
); //Or "Pending", etc.
//adjust the 24--higher to increase the space or lower to limit the space between dates

In the section expert->details->suppress->x+2, enter:

{table.date2} = next({table.date1})

Then remove the original date fields and display the earlier formula in their place.

-LB

 

[lbass]

Sorry, that should have been:

whileprintingrecords;
datevar start;
datevar end;
datevar prev := start;
if onfirstrecord or
{Table.ID} <> previous({Table.ID}) then
start := {Table.Date1} else
start := start;
if not onfirstrecord and
{Table.Date1} = previous({@Date2}) then
start := start else
start := {Table.Date1};
if onfirstrecord or
{Table.ID} <> previous({Table.ID}) then
end := {@Date2} else
if not onlastrecord and
{Table.ID} = next({Table.ID}) and
{@Date2} <> next({Table.Date1}) then
end := date({@Date2}) else
end := end;
if onlastrecord or
{Table.ID} <> next({Table.ID}) then
end := {@Date2};
totext(start,"M/dd/yy") &
space(24-len(totext(end,"M/dd/yy"))) & (
if end <> date(9999,9,9) then
totext(end,"M/dd/yy") else
"In Process"
); //Or "Pending", etc.

-LB