Solved

Selecting the Most recent last performed date for each record

Posted on 2016-09-29
3
20 Views
Last Modified: 2016-10-18
I had this question after viewing Maximum date for Crystal Reports.

I need to be able to select only the most recent complete date for each record.  I have tried everything I can think of and have failed miserably so far.  This can't be this hard.  I have tried everything I could find on the site.  Any help would be great.

Thanks
Rich

PM_ID_NUMBER      PM_GEN_WO_NUMBER      PM_DUE_DATE      PM_COMPLETE_DATE
00006514      00224806      20161231      20151201
00006514      00209934      20161231      20141208
00006514      00195108      20161231      20131216
00006514      00181568      20161231      20130204
00006514      00177627      20161231      20121115
00006515      00223658      20161130      20151027
00006515      00188866      20170731      20130701
00006515      00190107      20170831      20130819
00006515      00183780      20170331      20130321
00006515      00185105      20170430      20130401
00006515      00186295      20170531      20130506
00006576      00231802      20160930      20160516
00006576      00228092      20160930      20160301
00006576      00224832      20160930      20160104
00006576      00221174      20160930      20150910
00006576      00217433      20160930      20150608
00006576      00213522      20160930      20150311
00006576      00210092      20160930      20141215
00006577      00223670      20161130      20151102
00006577      00223671      20161130      20151102
00006577      00216222      20161130      20150604
00006577      00216223      20161130      20150506
00006577      00208837      20161130      20141110
0
Comment
Question by:RDMcLaurine
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 500 total points (awarded by participants)
ID: 41822109
If you are simply trying to display the dat you can do this with groups.

Add a group on PM_ID_NUMBER
Add a sort on PM_COMPLETE_DATE
   Make the sort descending
Put the fields in the group header rather than the detail section
Suppress the details and group footer


This works if you only are displaying the data as the report shows.
If you are doing any summaries or other calculations on the data then this idea fails because all the data is still in the report.


I attached a sample report

mlmcc
MaxDateData.xls
MaxDate-by-Grouping.rpt
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 500 total points (awarded by participants)
ID: 41822154
You can also use a COMMAND for the data source.  The SQL for it will be something like this

SELECT T1.PM_ID_NUMBER, T1.PM_GEN_WO_NUMBER, T1.PM_DUE_DATE, T1.PM_COMPLETE_DATE
 FROM   YourTable T1
WHERE T1.PM_COMPLETE_DATE =  (SELECT MAX(T2.PM_COMPLETE_DATE) FROM  YourTable T2 WHERE T2.PM_ID_NUMBER = T1.PM_ID_NUMBER )

Open in new window


mlmcc
MaxDate-using-a-Command.rpt
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question