Skip to Content

Select MAX Date in Crystal Reports Using SQL

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
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Mar 18 at 07:36 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 19 at 06:31 AM

    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?

    Add comment
    10|10000 characters needed characters exceeded