Date Ranges

[conrads57]

I have report that I need to choose a range of eligibility records based on a date parameter. I would like to display the min start date and max end date for each period of eligibility if there is no status change and no break in consecutive months of coverage. I am using Crystal XI and Oracle 10g.

Here is an example of our data:

Mem ID Plan ID Start Dt End Dt Status
255099 1917486 10/01/2003 06/30/2004 A
255099 1917493 07/01/2004 03/31/2005 A
255099 1917518 04/01/2005 12/31/2005 A
255099 1917522 01/01/2006 01/31/2006 A
255099 1917526 02/01/2006 06/30/2006 A

255099 1917530 07/01/2006 08/31/2006 H

255099 3359315 12/01/2006 01/31/2007 H

255099 4093936 04/01/2007 12/31/2099 A

If I choose 03/31/2006 in my parameter it falls in the first period listed and then I need to display 10/01/2003 as the min start date and 06/30/2006 as the max end date. It should not include the next record of 07/01/2006 to 08/31/2006 since the status changed to "H". Can anyone help me with this?

Thanks

 

[dgillz]

How can you have only have one date in a date range parameter? Why don't you enter both dates?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[conrads57]

The end user does not know the end date without looking it up. They are taking phone calls all day long from members and are on a quota for how many calls they must answer, they try to make there workflow as streamlined as possible. That is why they would like the end date to populate automatically.

We are not using "URGENT" software.

Thanks

 

[synapsevampire]

As dgillz points out, there's no reason for a range if you're not entering the second date.

So for the second date use a formula based on the parameter:

if month({?MyDateParm}) > 6 then
cdate(year({?MyDateParm})+1,6,30)
else
cdate(year({?MyDateParm}),6,30)

I believe that's what you're after here...

-k

 

[conrads57]

Aside from the parameter is it possible to group the date together to show the different periods?

 

[CoSpringsGuy]

I think the range being referred to is the eligibility range to return based on the single parameter date input by the user.

I was working through this problem yesterday but have been sidetracked with my own projects. If I can get back on it I will but I thought I would throw out what I believe to be a clarification of the original post in case someone else has a quick answer. Unless I am wrong about that.

 

[CoSpringsGuy]

My initial thought was:
sort the records by Mem ID then by Start Dt (looks like they already are)

create a whilereadingrecords variable {startdate} where {startdate} remains the same if the End Dt is the same as Start dt of previous record and the status is A (if not reset variable)

create a formula {maxenddate} that returns the End Dt if the parameter date falls between {startdate} and End Dt else null (or a dummy date)

place {startdate} and {maxenddate} on the details and suppress those records that do not contain a valid date in {maxenddate}

 

[conrads57]

CoSpringsGuy you are correc. The range being referred to is the eligibility range to return based on the single parameter date input by the user.