Specific record needed in report

[technonurse]

Scenario: Person takes a certain test and gets a score. I need to find out if the person was re-tested on the same test within the next 6 months. Person may take test several times, so essentially I need to compare just 1st date and 2nd date, all being pulled out of the same field. How can I get CR to pull 2nd date only?

Using CR 8.5
links are equal in a one-to-many relationship.

Technonurse-Spokane

 

[chelseatech]

Group your data by Person ({person.GroupField})
Create a summary Maximum(TestDate) for each person
Use a GROUP selection formula of
{Person.TestDate} =
Maximum({Person.TestDate}, {person.GroupField})

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[rhinok]

What if the individual took tests as follows:[ol][li]Test A - Jan 1st[/li][li]Test A - Jul 1st[/li][li]Test A - Aug 1st[/li][/ol]In this scenario, the individual was retested within 6 months of a previous test, just not between the 1st and 2nd tests. Do you really only want to show retests between the 1st and 2nd occurrences?

Also, what do you really want the report to display? Only retested individuals or all individuals tested, with only the appropriate retest displayed?

Chelseatech, your formula doesn't address the key points:[ol][li]The request is to compare tests within a 6-months period. Your formula doesn't test for a date range. Instead, it simply returns values where the test date = max(test date).[/li][li]The request is to compare the 1st and 2nd test dates (although I think this is erroneous, based on the scenario above). Your formula looks for the Max(test date), which could be the 5th test date.[/li][/ol]Assuming that you only want to show individuals that retested within 6 months, you could do the following:[ol][li]Order by Person (Ascending)[/li][li]Order by Test (Ascending)[/li][li]Order by Test Date (Ascending)[/li][li]Create a Formula Field that calculates the number of months between the current Test Date and the Previous Test Date:

//@CompareTestDates
If
PreviousIsNull({table.testdate})
Then
7//conditionally suppress detail records where @CompareTestDates >6
Else If
{table.testerid} = Previous({table.testerid})
And testid = Previous(testid)
Then
DateDiff ("m", Previous({table.testdate}), {table.testdate})
Else
7//conditionally suppress detail records where @CompareTestDates >6

[/li][li]Conditionally suppress all detail records where the @CompareTestDates formula > 6[/li][/ol]A caveat to using this approach is that you're going to show all retests that occurred within 6 months. So, an individual that retested 5 times, each within 6 months of the previous test date, will show up 5 times. If you only want to show the first occurrence then you'll need to create a running total of occurrences and suppress records where the running total > 1.

Hope this helps...

 

[lyanch]

I would use a self join for this. With two joins:

table.person_id = tablealias.person_id and
table.test_id = tablealias.test_id

And a selection formula as follows:

tablealias.date > table.date and
tablealias.date <= dateadd(&quot;m&quot;,6,table.date)

Lisa

 

[technonurse]

Thank you to your great suggestions: I am trying out a couple of things per your overall suggestions and am close to a solution. Will pulbish as soon as I have a winner. Thanks again!

Technonurse-Spokane