| Author |
Message |
scsh4
Newbie
Joined: 23 Jan 2017
Location: United States
Online Status: Offline
Posts: 2
|
   Topic: Maximum Date for Unique Record
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!
Edited by scsh4 - 23 Jan 2017 at 8:36am
|
 IP Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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
Edited by hilfy - 23 Jan 2017 at 9:57am
|
|
|
|
IP Logged |
|
scsh4
Newbie
Joined: 23 Jan 2017
Location: United States
Online Status: Offline
Posts: 2
|
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!
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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
|
|
|
|
IP Logged |
|
|
|
