Print Page | Close Window

"last" transaction

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1363
Printed Date: 19 May 2024 at 5:16am


Topic: "last" transaction
Posted By: cat917
Subject: "last" transaction
Date Posted: 23 Sep 2007 at 12:15am
I have created a report from 4 different databases on certain transactions from our manufacturing database.  I get WAY too many records - all I want is the latest date of each item.

-------------
Luna


Replies:
Posted By: BrianBischof
Date Posted: 24 Sep 2007 at 9:08am
I wrote about this in the book b/c I see this problem on a regular basis (Best Of The Forum, p. 433). You have to use a SQL Command object to make this work. 
SELECT * FROM Table AS T0
WHERE T0.DateField =
(SELECT MAX(T1.DateField)
FROM Table as T1
WHERE T0.PrimaryKey=T1.PrimaryKey)

In this SQL you have to replace "Table" with your table name. Replace "DateField" and "PrimaryKey" with the appropriate date field that you are sorting on and then use the name of the primary key field. This effectively uses a sub-query to find the last date for each primary key and then joins that resultset to the same table. Since the join is on the date field, then this lets you just get the last record that matches the date in the sub-query.


-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: betamax
Date Posted: 16 Dec 2011 at 5:42am
Thanks for posting this! It's exactly what I need. Unfortunately, it didn't work for me in either formula workshop or sql server management studio.
 
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'T0'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'T0'.
 
 

Posted By: kostya1122
Date Posted: 16 Dec 2011 at 8:58am
the query is correct
have you replaced
  
SELECT * FROM Table(your first table name) AS T0
WHERE T0.DateField(date column from your table) =
(SELECT MAX(T1.DateField(date column from your table)
)
FROM (your second table name)
 as T1
WHERE T0.PrimaryKey=T1.PrimaryKey)


Print Page | Close Window