Showing last record

[adnil]

Hi all,

We are using Crystal Xi and we have the following data:

Employee name Start date End date Reason
ABC 09/09/2010 11/09/2010 Fever
ABC 01/05/2011 07/05/2011 Migraine
XYZ 05/05/2011 05/07/2011 Depression

We need the present the data as below. Users will enter a date range and the report shows all the incidents with a Start date that falls between the selected date range, grouped by the Reason and also show the Reason from the last incident, if any. so if users select 15/04/2011 to 15/05/2011, the report will look like:

Group by Reason: Migraine
Employee name Start date End date Last reason
ABC 01/05/2011 07/05/2011 Fever

XYZ will not appear in the report as he only has 1 incident. We are almost done but we are struggling to show the last reason. Any suggestions please?

Thank you.

 

[SkipVought]

Hi,

Why are you getting Fever, when the Reason should be Migraine?

You must have some other criteria that you have not divulged.

Please post the SQL that results from your criteria selection(s).

 

[hilfy]

Try this:

Within your group, sort by date. Then, instead of putting the data in the details section, put it in the Group Header (ascending sort) or Group Footer (descending sort).

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[adnil]

Hi SkipVought

The reason why I am getting fever is because the report groups the data by the reason of the incident that has a start date that falls in between the selected date range. in the details section, the report will then show the name of the employee, the start date and end date of the incident as well as the reason of the last incident, in ABC's case is Fever. This is the sql so far:


SELECT
E.FORENAME,
E.SURNAME,
O.REASON,
O.START_DATE,
O.END_DATE

FROM
EMPLOYEE E INNER JOIN OCCURENCE O ON E.EMPLOYEE_ID=O.EMPLOYEE_ID

WHERE
O.START_DATE>=TO_DATE ('15-04-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND O.START_DATE<TO_DATE ('15-05-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
ORDER BY O.REASON

The only thing that is missing from this now is the reason of the last incident.
Thank you.