SQL Expression Fields 1

[jpdmdc]

Hi,

I'm using Crystal Reports 10 Professional Version and the database is Sql Server 2005. An order might appear more than once in a table where we store the information of the calls we received from the customers for each order. If the customer calls us for some reason mulitple times for the same order than we'll have multiple records for the same order with multiple dates..I want to get the max date on each order we recieved. i need to group the data by rep but need to show the date he received a call for that orrder and also the max date for the same order.

Example : Mr.A received a call for order 1234 on 10/1
and Mr.B also received a call for same order on 10/2.

Then my report should look like tthe following:
Group 1 Rep A
Order ID Recv Date max date Final Call
1234 10/1/2007 10/2/2007 no
2323 10/8/2007 10/8/2007 yes

Group 1 rep B
Order ID Recv Date max date Final Call
1234 10/2/2007 10/2/2007 yes
2323 09/1/2007 10/8/2007 no

so, I want to use a SQL Expression to get the max date for each order which apprears in my report.

Could any one help please!

Thanks in Advance.

 

[lbass]

I don't know whether SQL expressions for statements like this still worked in CR 10 or not, but if they did, the syntax would look like this:

(select max(A.`Recv Date`) from table A where
A.`Order ID` = table.`Order ID`)

You could then use this in a conditional formula.

If this doesn't work (it would in 8.0), you could create a command like:

select max(table.`Recv Date`) as maxdate, table.`Order ID`
from table
group by table.`Order ID`

Then you could link the command to your table on both the order ID field. Then use {command.maxdate) in your conditional formla. In both cases the punctuation needs to be adapted to your datasource. Check database->show SQL query to see how the punctuation should be used.

-LB