pulling correct record

[lauriesamh]

Hi, I working in CR 8.5.

I have a report that pulls people based on their reenrollment with a product within a specific date range. I am having a problem in that I need the report to look at the last end-date where the person was eligible. Currently the report pulls the record prior to their reenrollment record. Example:
ID: 12345
Eligible 010102 Term 010205
Not Eligible 010305 Term 030105
Eligible 030205 Term:

Report should pull the term date of 010205. Currently the report pulls 030105.

This is the criteria that pulls the record that terms the day before the person is reenrolled.

{CMC_SBEL_ELIG_ENT.SBEL_INSQ_DT} >= {?Last Report Date Run} and {CMC_SBEL_ELIG_ENT.SBEL_INSQ_DT}< DATEADD("d",1,{?Eligibility Date})

Any help or ideas would be great!

 

[lbass]

Please explain your date format--can't tell whether this is:
MMddyy, ddMMyy, yyMMdd, etc., so it's hard to follow your example.

-LB

 

[Madawc]

If you group by person, you should be able to get the maximum date using a summary total. Right-click and choose [Insert] for summary totals.

Madawc Williams (East Anglia, UK)

 

[lauriesamh]

All,
Thanks for the help! I managed to figure out a way around it. The data is in MMDDYY format. If you are curious this is what I did:

{CMC_SBEL_ELIG_ENT.SBEL_INSQ_DT} >= {?Last Report Date Run} and {CMC_SBEL_ELIG_ENT.SBEL_INSQ_DT}< (DATEADD("d",1,{?Eligibility Date})+1)

Essentially I added one more day prior to the Not Eligible Date (i.e. 010305) which gives me the next prior records' "Eligible" last day (010205). Which was what I was looking for.

I appreciate all your help! Have a great day.