Select MAX Date in Crystal Reports Using SQL

[former_member605908]

I'm very new to SQL, but have been using Crystal Reports for a while. I have the following table and need to marry the promise date (shown below) to the data in another table (NOT shown). The table below and the other table have a Many-to-Many Relationship and so I can't link them in Crystal directly. I also need to be able to group data by the PromiseDate (shown below) AND then group the report by other data for the other table.

Because I need groups based on data in both tables I haven't found any other Crystal Reports' techniques that will work (like using Shared Variables between the main report and a sub-report).

I need to be able to SELECT each Sales Order just once and return the Maximum Promise Date Listed in Crystal Reports. Once I have this Command created in Crystal Reports using a SQL statement I can then easily tie the two tables together.

Listed below is a sample of the table I'm trying to query via SQL in Crystal Reports Command, Also below is a sample of how I want the results of the Query to look:

SalesOrderNo ItemCode PromiseDate S34413 /C 8/1/2011 S34413 /C S34413 /C S34413 0901402 8/8/2011 S34413 /1PM 8/1/2011 S34413 /C S34413 /C S34932 /C 8/19/2011 S34932 /C 8/19/2011 S34932 /1 8/12/2011 S34932 /M95 8/12/2011 S34932 /C S34932 /C S34932 *592-EUTC 8/12/2011 S34932 /F 8/12/2011

The Data I need returned would look like this:

SalesOrderNo PromiseDate S34413 8/8/2011 S34932 8/19/2011

[former_member605908]

I appreciate the reply and your willingness to help me out (on a side note sorry about the Data not being laid out appropriately...I didn't see that mess until after I submitted).

I just need the proper Syntax for the SQL Command.

I thought it should be

SELECT SalesOrderNo, PromiseDate

FROM {Table}

WHERE PromiseDate = Max(PromiseDate)

Was I even close?

[DellSC]

You're going to need a command for this. If you would create link the tables together in Crystal and add the fields you need to a dummy report, you can then go to Show SQL Query... on the Database menu. From there, if you'll copy the query and paste it here along with the following information, I can help you write the query.

- Any parameters you need to use.
- Filter criteria from the Select Expert
- The type and version of database that you're connecting to.

Also, for more information about how to work with commands, see this blog post.

-Dell