Crystal Reports Show Latest Date 1

[hyde42]

I'm trying to show the most current date on two lines of data based on two different fields called ID#. Basically I have one member with two different SSN's. One SSN is real and one is a pseudo SSN. I need to see this member when one of the dates associated with the member is within a certain timeframe.

For example:

886001222 Mickey Mouse 2/1/2011
001648899 Mickey Mouse 12/1/2011

886456789 Donald Duck 2/1/2011
002546233 Donald Duck 6/1/2010

I want the report to be able to show Mickey Mouse only.

Any help would be most beneficial! Thanks! h

 

[Turkbear]

Hi,
Why is Mickey Mouse the one? Is it the 12/1/2011 Date?




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[hyde42]

Yes...I'll have a parameter that the end-user will put a date in for. Most likely, it'll be with the week they are running it for.

 

[lbass]

I think you should go to report->selection formula->GROUP and enter:

distinctcount({table.ssn},{table.name}) > 1 and
maximum({table.date},{table.name})={?DateRange}

-LB

 

[hyde42]

-LB...

I already have this in the report...

DistinctCount ({VW_HSN.CID_ID}, {@UniqueMember}) > 1 and
{LGCEE_ELIGIBILITY_EXTRACT.LGCEE_INSP_EXP_DT} = Maximum ({LGCEE_ELIGIBILITY_EXTRACT.LGCEE_INSP_EXP_DT},{VW_HSN.CID_ID})

 

[lbass]

And so???

If you want to see all records for someone with the maximum in the specified range, then:

DistinctCount ({VW_HSN.CID_ID}, {@UniqueMember}) > 1 and
Maximum ({LGCEE_ELIGIBILITY_EXTRACT.LGCEE_INSP_EXP_DT},{@UniqueMember})={?DateRange}

You shouldn't be using the ID as a group for this purpose.

What is the content of {@UniqueMember}--I'm assuming it doesn't include the ssn.

-LB

 

[hyde42]

Why wouldn't I want to use the ID as a group for this? I want to group by the VW_HSN.CID_ID...I want to see that Mickey Mouse has an 886 and a true SSN.

This is at least getting me the two ID's to show, now I just want to show the most recent of these items.

I don't want to see the Donald Ducks, just the Mickey Mouse's because they have the most recent of dates.

h

 

[lbass]

Please explain what the ID field is returning--I thought you said it was actually the ssn. Is it some other unique ID field?

Please also explain how you are currently grouping, and explain your parameter set up.

-LB

 

[hyde42]

The ID is returning both the true SSN and the pseudo SSN from the db. Mickey Mouse is listed twice each under his SSN and his pseudo ID.

I'm grouping on UniqueMember which consists of

{@LastNameShortened} & {CSO_CUS_ORG_Sub.CSO_FIRST_NM} & {CSO_CUS_ORG_Sub.CSO_DOB} & {CSO_CUS_ORG_Sub.CSO_SEX}

and then by VW_HSN_CID_ID which is either the pseudo ID or SSN.

What appears is what my example of Mickey and Donald shows.

I currently don't have a parameter yet.

My report criteria is calling for all active, subscribers which both Mickey and Donald are.

 

[lbass]

Did you try my last suggestion? It should return both values per member where the most recent date of one of them falls in the selected date range. If you only want the most recent row to return per member, then add another line so that it reads like this:

DistinctCount ({VW_HSN.CID_ID}, {@UniqueMember}) > 1 and
Maximum ({LGCEE_ELIGIBILITY_EXTRACT.LGCEE_INSP_EXP_DT},{@UniqueMember})={?DateRange} and
{LGCEE_ELIGIBILITY_EXTRACT.LGCEE_INSP_EXP_DT}=
Maximum ({LGCEE_ELIGIBILITY_EXTRACT.LGCEE_INSP_EXP_DT},{@UniqueMember})

If this doesn't return the result you are looking for, please explain exactly in what way it is not.

-LB

 

[hyde42]

Sorry...I skipped out for a bit...

I tried putting in the your suggestion but received zero results.

 

[lbass]

Please post what you put in the group selection area and what you entered for record selection.

-LB