Newest Date

[kristinaloupe]

I need a formula that chooses the newest date per record. I have a table that holds many events (some duplicates) along with the date of that event. I want to choose a specific event and it's corresponding date ---but I want only the most recent date/event.

HELP!!!! Thanks.

 

[Madawc]

If it is several dates per record, you need a formula field that compairs them. I don't think there's any way round the long-winded process of saying If date1 > than date2 and date1 > than date3 and date1 > than date3 etc., repeated for the other dates until all possible combinations are exhausted. You might also need to check for nulls: a null date will stop any formula and you'd need a if not isnull(date1) and

If there is more than one record per account, group by account and get a summary total for each date, showing the maximum. Then compair as before.

Madawc Williams (East Anglia, UK)

 

[kristinaloupe]

Hmmmmm...

What I have is the event of Case Opened several times --- each with a seperate date. So I am not sure how I would do a date1>date2 etc.. because the dates are all in the same table and are the same field...

Any more suggestions? Thanks so so much, this is driving me nuts.

 

[rosemaryl]

Dear Kristinaloupe,

Can you show us what the table looks like with some example data?

Also, what version of Crystal and what database?

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[kristinaloupe]

Ok.. I have added a portion of the table..
The VBKey is the main key for the record.
EventID is the record that can be duplicated and the event date next to it is the date(manual entry by user) the record was entered.

Problem: I have several cases that have 2 or more entries of the EventID number with different dates. I only want to the newest date.



Event
VBKey Number EventID EventDate
4982 1 0 78 08/20/1997
4982 2 0 3 08/15/1997
4982 3 0 5 09/03/1997
4982 4 0 48 09/02/1997
4982 5 0 8 05/19/1998
4982 6 0 47 01/01/1998
4982 31 0 4 01/11/2000
4982 35 0 12 08/30/1999
4982 36 0 11 09/30/1999
4982 37 0 11 10/30/1999
4982 40 0 157 09/21/1999
4982 41 0 155 09/21/1999
4982 42 0 158 09/23/1999
4988 1 0 78 08/20/1997
4988 2 0 3 08/15/1997
4988 3 0 68 09/10/1997
4988 4 0 12 09/25/1997
4991 1 0 78 08/20/1997
4991 2 0 3 08/17/1997
4991 3 0 68 09/29/1997
4993 1 0 78 08/20/1997
4993 2 0 3 08/12/1997
4993 3 0 68 09/05/1997
4993 4 0 12 09/25/1997
4993 34 1 111 08/06/1999
4995 1 0 78 08/20/1997
4995 2 0 3 08/12/1997
4995 3 0 68 09/10/1997
4995 4 0 12 09/25/1997
5001 1 0 78 08/20/1997

 

[kristinaloupe]

Crystal Version 10
SQL database

sorry forgot to add these in my prior post...

thanks so much for looking at this for me.

 

[rosemaryl]

Dear Kristinaloupe,

Ok. Good information.

However, in the data presented above I see no repetition of Event ID per vbkey.

Regardless, if the goal is simply to show the last date for an Event ID you could Group by VBKey, Group by Event Number, Group by Event ID and then display the data in the group footer. (you may need to add a sort for event date ascending). This would show the max date. If you need the max date regardless of event number then leave it out of the grouping.

If you don't want to select the records at all that aren't equal to the max date then create a sql expression. (View/Field Explorer/SQL Expression/New and give it a name)
then write something like the following, of course correcting for your actual table/view names... )

(Case when 
(Select P.[Event Date] 
from
"Owner."MyTable" P
WHERE 
P.[VB Key] = "ReportTable"."VB KEY"
AND
P.[Event ID] = "ReportTable"."Event ID"
) 
=
(Select Max(T.[EVENT DATE)
FROM "Owner."MyTable" T
WHERE 
T.[vb key] = "ReportTable"."VB KEY"
AND 
P.[Event ID] = "ReportTable"."Event ID")
then 1
else 0
end
)

Then in your selection criteria you would add:

{%NameofExpression] = 1

That should only return records where the date for the event id for vbkey is the max.

I hope that helps.

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[kristinaloupe]

Thank you... I will try this and let you know.