Help with record selection 1

[zenzabar]

I'm using CR XI. I need to report only patient records that contain services dates prior to 11/2006 but are also current patients being seen in 6/2007.

I'm grouping by patient number -

Patient # Date of Service
146097 3/2006
7/2006
4/2007
6/2007

186221 2/2007
6/2007

223733 8/2006
12/2006
4/2007
6/2007

I've tried different formulas but I cannot get the needed result. With this sample data, what I should have is just the listing of 146097 and 223733. What am I not seeing?

 

[IanWaterman]

I would do this using global boolean vars

in detail line add formula

whileprintingrecords;
global booleanvar nov;
global booleanvar jun;
if month({DATE}) = 11 and year ({DATE}) = 2006 then nov:= true;
if month({DATE}) = 6 and year ({DATE}) = 2006 then jun:= true;

In patient group header

whileprintingrecords;
global booleanvar nov:=false;
global booleanvar jun:=false;

this resets vars for each patient

In group selection

whileprintingrecords;
global booleanvar nov = false and
global booleanvar jun = false

Suppresses patients where these booleans are false

Ian

 

[zenzabar]

Thanks for your help. The formula will only show true in the detail line formula for the last entry.

so the records retuned look like this:

False 26590 10/15/2006
False 26590 10/22/2006
False 26590 11/05/2006
False 26590 11/18/2006
True 26590 06/11/2007
True 26590 06/18/2007

in detail line add formula

whileprintingrecords;
global booleanvar nov;
global booleanvar jun;
if month({DATE}) = 11 and year ({DATE}) = 2006 then nov:= true;
if month({DATE}) = 6 and year ({DATE}) = 2007 then jun:= true;

If I change the the 6 to the first line, then all of the June dates are false and November dates become true.

I have not gone further with placing a formula into the group header.

 

[lbass]

A different approach would be to insert a group on patientID and then go to report->selection formula->GROUP and enter:

minimum({table.date},{table.patientID}) < date(2006,11,1) and
maximum({table.date},{table.patientID}) >= date(2007,6,1) and
maximum({table.date},{table.patientID}) < date(2007,7,1)

If you just want the most recent date to be anytime after 6/1, then remove the last clause.

-LB

 

[zenzabar]

Thanks, that works extremely well!