Formula to Show Date when it falls within a Group Date Range

[dazum]

I am using CR11 and have a report that is grouped by each Office Visit Date. I have a Follow up Call dates in the report also. The data is showing up as shown below:

3-31-09 (Grouped Office Visit Date)
6-17-10 (Follow up Call dates)
6-30-10
8-30-10
1-3-11
5-31-11
11-16-10 (Grouped Office Visit Date)
6-17-10 (Follow up Call dates)
6-30-10
8-30-10
1-3-11
5-31-11
I would like to the Follow up Call date to show up if it is within Grouped Office Visit Date and the Next Grouped Office Visit Date (see below)
3-31-09 (Grouped Office Visit Date)
6-17-10 (Follow up Call dates)
6-30-10
8-30-10

11-16-10 (Grouped Office Visit Date)
1-3-11 (Follow up Call dates)
5-31-11
Is there a formula that will allow the Follow up Call Dates to show this way?

 

[lbass]

It sounds like these two dates are in separate tables, and since you are identifying one of them as "follow-up", are you sure there is not another field that would identify each followup call as belonging to a specific visit?

-LB

 

[dazum]

lbass
The dates are in diffent tables. I've tried different linking in the tables but the "follow up dates" keep coming out the same way in the reports, that's why I'm trying to use a formula to get the "follow up date" to show up the way that I want. I have experimented using the "previous" record and "next" record command but can't get the data exactly the way I want.

 

[lbass]

Create a SQL expression {%mindt} like this (assuming you are evaluating these dates per patient:

(
select min(`OfficeVisitDate`)
from VisitTable A
where A.`OfficeVisitDate` < VisitTable.`OfficeVisitDate` and
A.`PatientID` = VisitTable.`PatientID`
)

This will return the next visit date, so you can then use formulas like this to display the desired followup call dates--or you can add a second group on this formula:

if {Followup.CallDate} >= {VisitTable.OfficeVisitDate} and
{Followup.CallDate} < {%mindt} then
{Followup.CallDate}

-LB

 

[dazum]

lbass
I tried your SQL expression
(select min(`OfficeVisitDate`)from VisitTable A where A.`OfficeVisitDate` < VisitTable.`OfficeVisitDate` and A.`PatientID` = VisitTable.`PatientID`) and it returned the following data:

Office Visit SQL Expression(to get next office visit date)
6-3-03
11-5-03 6-3-03
9-21-05 6-3-03
8-4-06 6-3-03

I tied modifying the SQL to read
(select max(`OfficeVisitDate`)from VisitTable A where A.`OfficeVisitDate` > VisitTable.`OfficeVisitDate` and A.`PatientID` = VisitTable.`PatientID`) and it returned the following data:

Office Visit SQL Expression (to get next office visit date)
6-3-03 8-4-06
11-5-03 8-4-06
9-21-05 8-4-06
8-4-06
Is there any way I can modify the SQL expression so the data reads as follows?:

Office Visit SQL Expression (to get next office visit date)
6-3-03 11-5-03
11-5-03 9-21-05
9-21-05 8-4-06
8-4-06

 

[lbass]

(
select min(`OfficeVisitDate`)
from VisitTable A
where A.`OfficeVisitDate` > VisitTable.`OfficeVisitDate` and
A.`PatientID` = VisitTable.`PatientID`
)

It should have been ">", but using "min". But this assumes that OfficeVisitDate is a date, not a datetime.

-LB

 

[dazum]

thanks lbass!
I modified the SQL expression and the the data came out the way I needed it to.