HELP please comparing dates in multiple rows, use array? 1

[SouthernRed]

Using Crystal 2008
Example of data

rt ID Lstnme Crse Tme StartDate ExpireDate Status

1 125 Smith S-12 12 08/08/2012 08/08/2013 "License has expired, P couse must be taken"
2 125 Smith P-11 12 08/04/2011 08/04/2012
3 125 Smith P-09 12 04/07/2010 04/07/2011

I know this must be simple but can't figure out if I need an array, shared variable or how to proceed.
[highlight #EF2929]Problem:[/highlight]Each course must be taken before the previous one expires (the tme field denotes number of months before expire date. Do I need to do a datediff between the Start Date from each row against Time(12mths)or compare startdate from row 1 to expiredate in row2? I have a running total using count in the detail section but I don't know how to reference it.
[highlight #3465A4]Needed Result:[/highlight]If any course has expired, the next course must be a P* course not an S* course. Status must determine
1. If Expired
2. If Expired and P course taken "License was expired but P course taken" OR
If Expired and S course taken "License has expired, P couse must be taken"
I have the running total so I can show only row 1 on the report, that is all they care about.
Any help with syntax would be greatly appreciated. I have looked in books and on-line but really stuck. This is such a great forum for those of us learning the hard way. Thanks again.

 

[SouthernRed]

Need to add that SQL can't be modified, must work with dataset as presented

 

[SouthernRed]

additional Info: Time is shown in months but it must be 1 year to date. In this example the 1st record needs to show expired because he missed taking the next course by 4 days.

 

[lbass]

This assumes you are grouping on ID and sorting descending on start date.

//{@Status} for detail section:
if {table.startdate} > previous({table.enddate}) and
{table.course}[1] = "S" then
"License has expired, P couse must be taken" else
if {table.startdate} > previous({table.enddate}) and
{table.course}[1] = "P" then
"License was expired but P course taken"

If you are trying to count people in this situation, use a variable like this:

//{@detail formula}:
whileprintingrecords;
numbervar es;
numbervar ep;
numbervar ne;
if onfirstrecord or
{table.ID} <> previous({table.ID} then
(
if {@Status}="License has expired, P couse must be taken"
es := es + 1 else
if {@Status} = "License was expired but P course taken" then
ep := ep + 1 else
ne := ne + 1
);

Report footer formulas (separate for each variable} like this:
whileprintingrecords;
numbervar es; //etc.

-LB

 

[SouthernRed]

LB Thanks so much for your help!!