determining most recent date field

[termid0g3]

I am using the following formula to pull the most recently updated records from a db:

{payout.ppend} = DateTime (2003, 05, 31, 00, 00, 00) and
{employee.timekeep2} = "1504670" and
{employee.eff_dt} = Maximum ({employee.eff_dt})

The eff_dt field is a date field that is updated each time a user's demographic information is updated. Each record has multiple xx/yy/xx eff_dt date values. I only want to retrieve demographic data for the most recent eff_dt.

The formula returns the following error right now:
THE MAX FUNCTION CANNOT BE USED BC IT MUST BE EVALUATED
LATER

Is there a better way to approach this problem??

Any help is greatly appreciated!!!!

Thank you,
Paul

 

[synapsevampire]

You might want to include any groupings you have within this report, as they directly impact this sort of thing.

If you want only one row, then this is very simple, but I'm guessing that you want to know the maximum(date) for some particular grouping.

Ther are 2 ways to approach this in Crystal, either use a Top N report, or group by the field of interest, and use a Report->Edit Selection Formula->Group and place something like:

{employee.eff_dt} = Maximum ({employee.eff_dt}, {employee.employeeid)

This assumes the maximum eff_dt epr employeeid, but I've no idea what you really want.

If this doesn't resolve, provide specific information, such as groupings, example data and expected output.

-k

 

[termid0g3]

I followed your advice and met the same error described above.

Perhaps a SQL version of my formula will help clear things up.. check this out:

SELECT "employee"."employee", "employee"."eff_dt", "employee"."timekeep2", "payout"."ppend", "payout"."reg_hrs", "payout"."ot1_hrs", "payout"."reg_pay", "payout"."ot1_pay", "payout"."earncode", "payout"."hrs", "payout"."oth_pay", "payout"."tot_pay"

FROM "NEMC_PROD"."dbo"."payout" "payout" INNER JOIN "NEMC_PROD"."dbo"."employee" "employee" ON "payout"."employee"="employee"."employee"

WHERE (&quot;payout&quot;.&quot;ppend&quot;>={ts '2003-05-31 00:00:00'} AND &quot;payout&quot;.&quot;ppend&quot;<{ts '2003-05-31 00:00:01'}) AND &quot;employee&quot;.&quot;timekeep2&quot;='1504670' AND &quot;employee&quot;.&quot;eff_date&quot;=MAX({&quot;employee&quot;.&quot;eff_dt&quot}

ORDER BY &quot;employee&quot;.&quot;employee&quot;, &quot;employee&quot;.&quot;eff_dt&quot;

This is a DB that contains employee demogrphic information. It was designed poorly, if an existing employee's demographic info is updated, a new record is created with the eff_date field set to CurrentDate. With this query I hope to pull only the most recent updates.

This may help explain:

John Doe appears 3 times in the DB

Record=1, Employee=John Doe, Eff_dt=07/14/2003
Record=2 Employee=John Doe, Eff_dt=07/112003
Record=3 Employee=John Doe, Eff_dt=05/09/2001

I only want to retrieve Record 1. How can I do that using the Select Expert???

 

[Naith]

There are 4 ways you can do this.

Two of them have been touched on in the vamps post. Both these solutions work, so I would suggest that you double check that you are following either one of them accurately.

The alternatives are to use a subquery in your SQL so that your WHERE clause looks like this:

WHERE (&quot;payout&quot;.&quot;ppend&quot;>={ts '2003-05-31 00:00:00'} AND &quot;payout&quot;.&quot;ppend&quot;<{ts '2003-05-31 00:00:01'}) AND &quot;employee&quot;.&quot;timekeep2&quot;='1504670' AND &quot;employee&quot;.&quot;eff_date&quot;=
(
SELECT MAX({&quot;employee&quot;.&quot;eff_dt&quot;}) from &quot;employee&quot;
GROUP BY &quot;employee&quot;.&quot;id&quot;
)

At the moment, your SQL query is incorrect. This section of your code:

AND &quot;employee&quot;.&quot;eff_date&quot;=MAX({&quot;employee&quot;.&quot;eff_dt&quot}

is only going to return the guys whose eff_dt matches the most recent eff_dt in the database.

The last way you can do this, is to return all dates, group by employee id ascending, and eff_dt ascending, suppress your details section, and move all your information to eff_dt group footer. All dates will be returned, but only the latest data will be displayed. As you will still be processing all your excess data, rather than disgarding it on the database, this last option is the least efficient.

Naith