Maximum Date for Unique Record
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=22202
Printed Date: 06 May 2024 at 1:24am
Topic: Maximum Date for Unique Record
Posted By: scsh4
Subject: Maximum Date for Unique Record
Date Posted: 23 Jan 2017 at 8:33am
Good Afternoon!
How would I write a statement to only return the record with the maximum EFFECTIVE_DATE, including records for all unique EID. I have thousands of records, below are just a few examples. Each EID could have anywhere from 5 to 50+ records, but I need to return a record for each EID, only the record with the maximum EFFECTIVE_DATE and excluding duplicates (as found in EID 125 and 152).
(Example of returned results)
EID FIRST_NAME LAST_NAME HOURLY_BASE EFFECTIVE_DATE
125 John Doe 18.25 01/01/2017
125 John Doe 18.25 01/01/2017
125 John Doe 16.25 01/01/2016
125 John Doe 14.25 01/01/2015
130 Bill Edwards 20.50 01/23/2017
130 Bill Edwards 19.75 06/01/2016
130 Bill Edwards 19.00 01/01/2016
130 Bill Edwards 17.00 08/25/2015
152 Scott Smith 32.50 01/01/2017
152 Scott Smith 32.50 01/01/2017
152 Scott Smith 30.00 01/01/2016
152 Scott Smith 26.00 06/01/2015
(Example of what I need returned)
EID FIRST_NAME LAST_NAME HOURLY_BASE EFFECTIVE_DATE
125 John Doe 18.25 01/01/2017
130 Bill Edwards 20.50 01/23/2017
152 Scott Smith 32.50 01/01/2017
Thanks in advance for any advice that is given!
|
Replies:
Posted By: hilfy
Date Posted: 23 Jan 2017 at 9:57am
There are two ways you can do this:
Easy way using just Crystal, but pulling way too much data, slowing down the report: (assumes that you want the data in EID order...)
1. Group by EID.
2. Sort by Effective_date descending.
3. Put all of the data in the EID group header section.
4. Suppress the details and EID group footer.
More difficult way that gets only the data you're looking for:
Write a command (SQL Select statement) to get the data instead of using tables in Crystal.
It might look something like this:
Select
a.EID, a.FIRST_NAME, a.LAST_NAME, b.HOURLY_BASE, b.EFFECTIVE_DATE
from Employee a
inner join Salary b
on a.EID = b.EID
where b.EFFECTIVE_DATE =
(Select max(EFFECTIVE_DATE)
from Salary b2
where b2.EID = a.EID)
For more information about how to use Commands, see my blog post here:
http://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: scsh4
Date Posted: 23 Jan 2017 at 10:39am
Hilfy,
I appreciate the response. Those being hypothetical fields, if I gave you the actual field names, could you outline an actual select statement for me?
The field names are as follows, if you're able:
TWAGE.EMPLOYEE_NO
TEMPLOYEE.FIRST_NAME
TEMPLOYEE.LAST_NAME
TWAGE.HOURLY_BASE_PAY
TWAGE.EFFECTIVE_DATE
Tables TWAGE and TEMPLOYEE and joined by EMPLOYEE_NO. I'm just not confident on my abilities to select by SQL command through CR. Also, would I put this select statement into the "Record Selection" window in the Formula Workshop? I really appreciate your help and thank you for what you've provided me so far!
|
Posted By: hilfy
Date Posted: 23 Jan 2017 at 11:48am
The Select statement would be in a "Command" in the Database Expert. It would completely replace ALL of the tables that you have in the report. See the link I provided above for information details on the best way to work with commands, but the two major rules are:
1. The command should pull ALL of the data you need for your report - DO NOT join it to tables or other commands.
2. Put ALL of the filter criteria in the Where clause of the command - DO NOT use the Select Expert to filter data.
Here's what your command would look like:
Select
e.EMPLOYEE_NO,
e.FIRST_NAME,
e.LAST_NAME,
w.HOURLY_BASE_PAY,
w.EFFECTIVE_DATE
from TEMPLOYEE e
inner join TWAGE w
on e.EMPLOYEE_NO = w.EMPLOYEE_NO
inner join (
Select EMPLOYEE_NO, max(EFFECTIVE_DATE) as maxDate
from TWAGE
group by EMPLOYEE_NO
) as mostRecent
on w.EMPLOYEE_NO = mostRecent.EMPLOYEE_NO
and w.EFFECTIVE_DATE = mostRecent.maxDate
Where
<whatever filter you need for your report...>
I did make a change from what I originally posted in that I took the date filter out of the Where clause and set it up for a join instead because that will be more efficient when it runs.
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
|