Data issue on records

[lthomas6]

I am running a Crystal 10 against a SQL database.
Notice ID Date
10011 2009-04-03 23:20:56.187
10011 2010-05-13 13:50:31.560
10011 2010-05-14 10:05:48.693

I am trying to capture into the report 2 things: 1. the first sent notice and 2. the last sent notice. This should display in the report as the below based on the information above from the table.

Name First Sent Last Sent
John Mark 4/3/2010 5/14/2010

Currently it is displaying as below:
John Mark 4/3/2010
John Mark 5/13/2010
John Mark 5/14/2010

Any help would be greatly appreciated. The report has been created by the crystal database expert of bringing in the tables from the database.

Thanks.

 

[fisheromacse]

I think you could create two SQL Expressions, a {%Min} and a {%Max}

//{%Min}
(
Select Max("YourField")
from "YourTable"
where "YourTable"."NoticeID" = "SecondTable"."NoticeID"
)


//{%Max}
(
Select Max("YourField")
from "YourTable"
where "YourTable"."NoticeID" = "SecondTable"."NoticeID"
)

then suppress the values not equal to Min or not equal to Max. Right click on the left sidebar on the desired section, choose 'Section Expert' -> click the suppress checkbox and then click on the 'X+1' next to the suppress option and enter the formula:
NOT({YourTable.Date}={%Max})
OR
NOT({YourTable.Date}={%Min})

 

[lthomas6]

I tried your suggestion, however when doing the below,it doesn't pull in any values into the report.

then suppress the values not equal to Min or not equal to Max. Right click on the left sidebar on the desired section, choose 'Section Expert' -> click the suppress checkbox and then click on the 'X+1' next to the suppress option and enter the formula:
NOT({YourTable.Date}={%Max})
OR
NOT({YourTable.Date}={%Min})

 

[fisheromacse]

you have to replace the table & field values with the ones from your database.
It is monday tho, so i will not promise/guarantee anything i type.

 

[lthomas6]

Yes that is what I did. I am pulling "your table" from holdnotices_history and the "second table" from holdnotices.
Perhaps it is because the date field could have the date repeated. So in otherwards the below:

Notice ID Date
10011 2009-04-03 23:20:56.187
10011 2009-04-03 23:30:56.187
10011 2010-05-13 13:50:31.560
10011 2010-05-14 10:05:48.693

Does that help?

 

[fisheromacse]

yes, if the date field repeats that would be a problem.

 

[lthomas6]

I wouldn't say the records are repeating itself, more like there could be 2 instances of the same date, however the timestamps would be different. Any other suggestions?

 

[fisheromacse]

as a thought, what happens if you put the min & max expressions in a group header based on Name?

 

[lbass]

You could just insert a group on the ID field and then insert a minimum and a maximum on the date field at the group level, and drag the name field into the group footer and suppress the detail section and group header.

-LB

 

[lthomas6]

lbass -
I'm not following what you are stating.
The report currently has a group1A on the HoldName.
Group 1B has the captions for the name, first sent, last sent
The details has the data for these captions. It is here where the suppression is done on the section expert stating the below:
NOT({HoldNotices_History.SentDate}={%max})
AND NOT({HoldNotices_History.SentDate} = {%min})

 

[lbass]

You have the SentDate in the detail section. Right click on it and insert a maximum at the group level. Then right click on it and insert a minimum at the group level. Then drag the results into the GH1b. You don't have to suppress the details unless you want to.

-LB

 

[lthomas6]

I see what you're saying now, thanks for the clarification however this does not produce the correct results since it will pull the maximum and minimum date however there could be 1 or more persons to a hold. This results in only pulling in one person not all persons associated with a hold.

Let me know if you need more info from me to help with this issue.

Thanks

 

[lbass]

You need to show a more accurate view of your data, with multiple names per hold. Also explain what fields you are grouping on, and in a situation with multiple names per hold, show what you would expect the results to look like.

-LB