Most Current Effective Date 2

[Meg09]

Hi,

I am writing a crystal report and am working with a table that contains pay rates and effective dates. If a person has had pay increases, they have multiple pay rates and effective dates within the table.

My question is, how would I write the formula to state that I only want the most current effective date?

Thanks,
Meg

 

[spcc07]

Go to the field explorer->SQL Expression->new and name it "Maxdate" and enter:

(
Select MAX(A.'eff_date') from table A
where A.'person' = table.'person'
)

Then, go to report ->selection formula ->record and enter:

{%maxdate} = {table.eff_date}

You will need to use the punctuation that your specific datasource uses. Replace table with your table name and leave 'A' as is.

 

[lbass]

spcc07,

Gosh, that sounds an awful lot like the language I use in my posts about SQL expressions.

A comment: The above should work in v.8 and 8.5, but at some point--v.9 or 10 through v.XI, the syntax no longer works, and you must omit the table reference within the parens for the summary, as in:

(
Select max(`eff_date`)
From table A
where A.`person` = table.`person`
)

I believe this was corrected in CR2008.

Note also that the punctuation is not a single quote, but the character on the key to the left of the 1 in the top row of the keyboard--not sure what it is called. This is the punctuation that works for Access-based databases, but not for Oracle.

-LB

 

[lbass]

PS. I meant to say that another approach (if you don't want to work with SQL expressions) is to group on employee and then go to report->selection formula->GROUP and enter:

{table.effectivedate} = maximum({table.effectivedate},{table.employee})

This will return only the record with the most recent effective date. You would have to use running totals if you want to do any calculations since non-group selected records would contribute to inserted summaries. The SQL expression route is cleaner, though a bit harder to implement.

-LB

 

[spcc07]

lbass,
You helped me with it over a year ago and I was just trying to help. Sorry it looked so much like your exact post.

SP

 

[franceshd]

Hey, lbass. This answer is just what I need, But with a twist. I want to use the most recent paycheck per employee. But the table with the date does not contain the EmployeeNumber. My (non-SQL) GroupSelection is:
PRCKHEAD.CHECK_DATE} =
Maximum ({PRCKHEAD.CHECK_DATE}), {PRCKHEMP.EMPL_NO}

It gets the error that from the comma onward is not a valid part of the formula
PRCKHEAD = Check Header Table
PRCKHEMP = Employee Check History Table

THANKS SSOOO MMMUUUUCCCCCHHHHHHHH!!!!!

 

[franceshd]

I tried adding the following but only got ONE person out of 9000 employees.

{PRCKHEAD.CHECK_DATE} =
Maximum ( {PRCKHEAD.CHECK_DATE} ) AND

{PRCKHEMP.PR_RUN_ID} = {PRCKHEAD.PR_RUN_ID} AND

{PRCKHEMP.EMPL_NO} = Maximum ( {PRCKHEMP.EMPL_NO} )

THANKS in advance for your help !!

 

[lbass]

First you should be linking the tables in the database expert, rather than in a selection formula. And isn't there some way of identifying the employee in each table? I'm also wondering whether you should be checking the check date in the history table instead of teh header PRCKHEAD table--but then I don't know your database.

You need to have a group inserted on {PRCKHEMP.EMPL_NO}. Then go to report->selection formula->GROUP and enter:

{PRCKHEAD.CHECK_DATE} = Maximum({PRCKHEAD.CHECK_DATE}), {PRCKHEMP.EMPL_NO})

The argument after the comma is the group condition, and it tells the report to return the most recent date per that group (employee).

-LB

 

[franceshd]

Tah-Dah! Got it!
THANKS SO MUCH, lbass.
You always have the answer!
I didn't know if this 2-week-old post would be seen and that's why I started a new post after awhil. Sorry for the trouble.
I'm an old dinosaur (COBOL) and had never used a data base system until last year when our department entered the 21st century with Crystal/SQL. It is a totally different animal and sometimes it's a struggle.
Thanks again. Tek-Tips is the best site on the internet for help and information.
Frances