Date Range Evaluation

[smsherman]

Greetings,

Crystal 9:

I am trying to develop a formula to evaluate member records based on date ranges to calculate effective dates for coverage.


fields:
db.member_id; db.plan_id; db.start_date; db.end_date

A member id can have multiple start and end dates for each plan, but they can only have one plan at any given time.

Possible output:
member#1
01/01/06(start) - 01/01/06(end) : canceled status
02/01/06(start) - 02/28/06 (end) :end of plan
03/01/06(start - null(end): currently active

In the above refrenced example, I would want a formula that would evaluate all three date ranges, and return a start date of 02/01/06 and an end date with a null value.

if the 02/01/06 ended on 02/01/06 as well, I would only want the 03/01/06 returned.

if 01/01/06 ended on 01/31/06 in the very first example, then i would want the 01/01/06 returned as the start date for the record.

All dates start at the beginining of the month, and will go to the last day of the month if the plan was active. If the plan was canceled, then it begins and ends on the first day.

I currently have the records grouped by Member, then by plan status (active, canceled, covered)

An active always has a null record for the end date; the canceled always has start date = end date; and the covered has a start date <> end date, but not null either.

Any pointers are appreciated! thank you.

 

[lbass]

Why not eliminate the cancelled statuses by using a record selection formula like:

{table.start} <> {table.end}

Then I think you could create a formula for the group footer (Member) like:

totext(minimum({table.start},{table.member}),"MM/dd/yyyy}) + " to " +
(
if maximum({table.end},{table.member}) <
maximum({table.start},{table.member}) then "Active" else
totext(maximum({table.end,{table.member}),"MM/dd/yyyy")
)

-LB

 

[smsherman]

I need to keep the canceled status for further calculations and to show record history.

I run a record selection in 4 month increments (rolling) and a record can have a cancelled status in one month, but two months later be active again. In this instace, i would want to ignore the cancel. However, I need to monitor every instance of the member history during the four month rolling window.

 

[lbass]

What would you want to see in this case:

1/1 to 1/31
2/1 to 2/1
3/1 to 3/31
4/1 to ___

-LB

 

[smsherman]

I would want to see all record lines; however, I would want a return of 3/1 to _ as the output.

1/1 to 1/31
2/1 to 2/28
3/1 to 3/31
4/1 to _ = 1/1 to _

1/1 to 1/31
2/1 to 2/28
3/1 to 3/1 = 1/1 to 3/1

1/1 to 1/1
2/1 to 2/1
3/1 to 3/31 = 3/1 to 3/31

I've managed to cover most of the sitautions, but the example you provided is one case where it simply does not compute. i was hoping to find a different solution.

Thank you for your help and consideration with this!

 

[lbass]

The following works as far as I could test it. This assumes that you want to show the most recent range of active plans whether before or after one or more cancellations. Create three formulas:

//{@reset} to be placed in the Member group header:
whileprintingrecords;
datevar mindate := date(0,0,0);
datevar maxdate := date(0,0,0);
datevar altmindate := date(0,0,0);
datevar maxshipdate := date(0,0,0);
numbervar cnt := 0;
stringvar x := "";

//{@accum} to be placed in the detail section and suppressed:
whileprintingrecords;
datevar mindate;
datevar altmindate;
datevar maxdate;
datevar maxshipdate;
numbervar cnt;
stringvar x;

if next({table.startdate}) = next({table.enddate}) and
{table.startdate} <> {table.enddate} then
altmindate := {table.startdate};

if {table.startdate} = {table.enddate} then
(mindate := mindate;
cnt := 0);
if isnull({table.enddate}) or
{table.startdate} < {table.enddate} then
(cnt := cnt + 1;
if cnt = 1 or
{table.startdate} < mindate then
mindate := {table.startdate});

if {table.startdate} > maxdate and
(
isnull({table.enddate}) or
{table.startdate} <> {table.enddate}
) then
maxdate := {table.startdate};

if {table.startdate} = maxdate then
if isnull({table.enddate}) then
maxshipdate := date(0,0,0) else
maxshipdate := {table.enddate};

//{@display} to be placed in the Member group footer:
whileprintingrecords;
stringvar x;
datevar mindate;
datevar maxdate;
datevar maxshipdate;
datevar altmindate;

if mindate = maxshipdate then
x := "No Active Plans" else
if mindate = date(0,0,0) then
x := totext(altmindate,"MM/dd/yyyy") + " to "+
(
if maxshipdate = date(0,0,0) then "____" else
totext(maxshipdate,"MM/dd/yyyy")
) else
x := totext(mindate,"MM/dd/yyyy") + " to "+
(
if maxshipdate = date(0,0,0) then "____" else
totext(maxshipdate,"MM/dd/yyyy")
);
x;

-LB

 

[smsherman]

This worked like a charm! Thank you so much!

I modified the display formula as I want to export the data for start and end date as two distinct fields, so I used the minimum start date information for one formula, and the end date for the other formula.

Would it be possible to group records based on the @display "start date"?; and since I just need the date information, what would be the best way to convert the output of the display to a date string instead of a text string?

Thank you again!

 

[smsherman]

it looks like I missed a scenerio...

Any suggestions on how to modify the above formula(s) to handle the following scenario:

1/1-1/31
3/1 - "active"
Should return 3/1 to "active"

1/1-1/31
2/1-2/28
4/1-4/30
Should return 4/1 - 4/30

The display formula appears to pull in the first date 1/1 instead of the 3/1. I've tried some different methods to pull in the 3/1, but it ends up invalidating other situations that are working correctly.

Thank you.

 

[lbass]

Try changing {@accum} to:

//{@accum} to be placed in the detail section and suppressed:
whileprintingrecords;
datevar mindate;
datevar altmindate;
datevar maxdate;
datevar maxshipdate;
numbervar cnt;
stringvar x;

if next({table.startdate}) = next({table.enddate}) and
{table.startdate} <> {table.enddate} then
altmindate := {table.startdate};

if {table.startdate} = {table.enddate} or
datediff("m",previous({table.startdate}), {table.startdate}) > 1 then
(mindate := mindate;
cnt := 0);
if isnull({table.enddate}) or
{table.startdate} < {table.enddate} then
(cnt := cnt + 1;
if cnt = 1 or
{table.startdate} < mindate then
mindate := {table.startdate});

if {table.startdate} > maxdate and
(
isnull({table.enddate}) or
{table.startdate} <> {table.enddate}
) then
maxdate := {table.startdate};

if {table.startdate} = maxdate then
if isnull({table.enddate}) then
maxshipdate := date(0,0,0) else
maxshipdate := {table.enddate};

Change the display formula to:

//{@display} to be placed in the Member group footer:
whileprintingrecords;
stringvar x;
datevar mindate;
datevar maxdate;
datevar maxshipdate;
datevar altmindate;

if mindate = maxshipdate then
x := "No Active Plans" else
if mindate = date(0,0,0) then
x := totext(altmindate,"MM/dd/yyyy") + " to "+
(
if maxshipdate = date(0,0,0) then "Active" else
totext(maxshipdate,"MM/dd/yyyy")
) else
x := totext(mindate,"MM/dd/yyyy") + " to "+
(
if maxshipdate = date(0,0,0) then "Active" else
totext(maxshipdate,"MM/dd/yyyy")
);
x;

To display the start date, create another formula:

evaluateafter ({@display});
whileprintingrecords;
stringvar x;
cdate(left(x, instr(x," ")-1))

No, you will not be able to group on this formula or the others, as they are evaluated "whileprintingrecords".

-LB