Skipping fields to perform record comparisons

My dilemma:

I have a SQL database of point-in-time "patient assessments".  (Relevant variables: PatientID, AssessmentDate, HasCancer)  Assessments are completed at any date in the year, but the only ones that are valid for my purposes are the last assessments in each quarter.  I need to compare each assessment to its "prior" valid assessment - ie. the last assessment performed in the previous quarter for the same patient.  As multiple assessments could be performed in a single quarter, I occasionally need to skip records and perform comparisons on records occurring 2 or 3 assessments back.

The only operators I am familiar with that can retrieve prior records for comparison are the Previous({fieldName}) and Next({fieldName}) operators.  For example,

if ( Previous({@quarter}) = {@quarter} - 1) then Valid = 1 else Valid = 0;

The Previous operator only looks at the previous record.  If the previous record is an invalid assessment (in same quarter), I am left paralyzed to performing comparisons with assessments before the previous.

As you can see, the "Valid" variable is a formula created within the database, at run time, so I cannot simply perform a Select to remove invalid fields.  Essentially, the validity criteria must be calculated only after my database is pulled into Crystal (from what I can tell).  Is there a workaround that can let me skip "Invalid" fields to perform comparisons?
Example

if( PriorValidAssessment.{HasCancer} = false and {HasCancer} = true ) then PatientDevelopedCancerLastQuarter = true;

Thanks for your help.
johnstonfAsked:
Who is Participating?
 
frodomanCommented:
I'm thinking that you want to use a variable to store the information and then compare based on that.

shared booleanVar LastQuarter;
shared booleanVar ThisQuarter;
booleanVar PatientDeveloped := false;
// If next quarter is different this is the last record of this quarter
if {@quarter} <> next({@quarter}) then
(
   // Copy current data to last quarter variable
   LastQuarter := ThisQuarter;
   // Populate current data
   ThisQuarter := {YourTable.HasCancer};
   // Compare current to prior
   if LastQuarter = False and ThisQuarter = True then  
      ( PatientDeveloped := true; )
);
PatientDeveloped;
0
 
OutinCommented:
@frodoman:

Nice solution!
I would probably have done the same thing, but in 2 or 3 formulas....this one is a beauty :-)

--
Outin
0
 
johnstonfAuthor Commented:
Agreed! Just working on implementing it....now for a follow up question.

Do you think it is possible to produce a list of patients who have developed the disease?  Ie. Select patients based on the result this runtime formula....if PatientDeveloped = True then print.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
OutinCommented:
You can conditionally suppress section(s) based on the formula, or (maybe) use it in your selection criteria....

--
Outin
0
 
frodomanCommented:
Yes, you probably can develop a list of patients, but that's an entirely different process and should probably be opened in a new thread.  

Simply put you'll need to create an array that gets redimed and appended every time a new record is found that you want to add to the list, then you'll later need to loop through the array to print the results.

frodoman
0
 
johnstonfAuthor Commented:
After a bit of research on my part, Outin's conditonal suppress suggestion worked beautifully on test data.  I will be implementing both solutions over this week.  Thank you both.
0
 
OutinCommented:
Glad it works...

--
Outin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.