Select Most Recent date grouped by Each week

[CARLEENHOP]

Hi

I have orders that get ammended, I am trying to extract the order details for the most recent ammendment. I have been able to do this by grouping via the order # and then entering:

"{AP21LOG_AUDIT.AUD_DATE} = maximum({AP21LOG_AUDIT.AUD_DATE},{SORD.NUM})" under the group selection

However, because I want to group it to show results for each week, once I group via the audit date and select to display each section to print weekly. I then lost the most recent order details (get them all). If i change this group to the 2nd level of grouping then its not correct as I will get order number then week, when I want week, then order numbers.

FYI - this report will more than likely end up as a sub report against a main report incase that impacts.

Using crystal 11.5 any help appreciated.

 

[IanWaterman]

If you group by date- weekly you can define maximum for the weekly group.

Maximum({SORD.NUM}, {AP21LOG_AUDIT.AUD_DATE}, "weekly")

I am not sure what you are trying to do, if this formula does not help, please show data as it is and what you want it to look like.

Ian

 

[Madawc]

You could get the data in a crosstab, but the crosstab would need to be at the start or end of the entire report.

If you can work in SQL, you could group, find the maximum within SQL, add the maximum to all records for an order.

Both solutions would remain valid if the report becomes a subreport.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

For this situation, you should create a SQL {%maxdate} expression something like this:

(
select max(`AUD_DATE`)
from SORD A, AP21LOG_AUDIT B
where A.`keyfield`=B.`keyfield` and
A.`Num` = Sord.`Num`
)

The punctuation would be specific to your database and you should replace the "keyfield" with the field you use to link the two tables.

Then you would go into report->selection formula->record and enter:

{AP21LOG_AUDIT.AUD_DATE}={%maxdate}

This will return only one row per Sord.Num, regardless of grouping.

-LB