Complicated Problem...Need Your Expertise

[Soleil525]

My report contains three tables: Claimant, Immunity and SurvTest. Each patient may have multiple immunity Surveillance shots (each shot represents a record). So in the SurvTest table, patient A might have the following record:

Protocol Type Vaccine Actual_Date Next F/U Date
Immune Vacc TB 01/01/2001 02/01/2001
2nd Shot Vacc TB 02/01/2001 03/ 01/2001
3rd Shot Vacc TB

At runtime, the user will enter a date range base on the Next F/U Date field to select the record. Let say that the user enters the Next F/U Date range of 02/28/2001 to 03/31/2001, I want to show the latest record, which is Third Shot Vacc TB base on the following conditions:
If the Actual_Date is empty(this means that the patient has not come in for the Third shot) and the Next F/U Date is fall within the date range then display:
Protocol Type Vaccine Next F/U Date
Third Shot Vacc TB 03/01/2001(how do I show this date since it is stored in the Second Shot record).
So basically, I want Crystal to check the Actual_Date field of the latest record (Third Shot), if the Actual_Date field is null, then look at the latest Next F/U Date, which is 03/01/2001(store in the Second Shot) and if this date is within the date range then display the latest record with this date 03/01/2001. This report was originally created in Visual Fox Pro and my user want to re-create this in Crystal. I’m using CR 8.5 and Oracle backend. Your help is greatly appreciated.

Bryan

 

[Soleil525]

Here is the SQL statement that was written in FoxPro application:

SELECT ;
select
MAX(claimant.employer_name) as employer_name,
MAX(claimant.employer_id) as employer_id,
MAX(claimant.dept_name) as dept_name,
MAX(immunity.disease) as disease,
MAX(claimant.lastname) as lastname,
MAX(claimant.firstname) as firstname,
MAX(immunity.claimant_id) as claimant_id,
MAX(DECODE(survtest.next_date, NULL, TO_DATE('01010100','mmddyyyy'), survtest.next_date)) as next_date,
MAX(claimant.mi) as mi,
MAX(claimant.dept_id) as dept_id,
MAX(claimant.termdate) as termdate,
immunity.immunity_id as immunity_id,
survtest.init_test_id,
MAX(survtest.protocol) as protocol,
MAX(DECODE(survtest.actual_date, NULL, survtest.test_type, RPAD(' ',7,' '))) as test_type,
MAX(DECODE(survtest.actual_date, NULL, survtest.test_name, RPAD(' ',30,' '))) as test_name
from
Claimant, Immunity, SurvTest
where
Claimant.Claimant_ID = Immunity.Claimant_ID
AND Immunity.Immunity_id = SurvTest.Immunity_id
AND (Claimant.termdate IS NULL
OR (Claimant.termdate IS NOT NULL
AND Claimant.termdate > DECODE(?pdDateTo, NULL, SYSDATE, ?pdDateTo) ))
AND survtest.init_test_id in
(select init_test_id
from survtest where immunity_id IS NOT NULL
and actual_date IS NULL and next_date IS NULL )
**lcEmpIDFilter** **lcDeptFilter** **lcRecIDFilter**
group by immunity.immunity_id, survtest.init_test_id
order by 1,2,3,4,5,6,7,8
;
FROM ;
;

I modified the code and try it out in SQL Designer, the result was pretty close. So I think my DBA can create a view with function and procedure to mimic this code. However, my client insists to use Crystal alone to create the report so that they can modify it later if necessary. What a pain!!! Do you guys think this can be done through Crystal alone?

Ido,
I'm at the client this whole week. I'll have to follow up with you when I come back. Mean while, if you have any other suggestion, please let me know.

Thank you all,

Bryan

 

[IdoMillet]

As I've indicated in my first message the
join condition of
SurvTest.Next_F/U_Date = SurvTest2.Actual_Date
is in addition to the other conditions that ensure
it's the same claimant etc...

This this join would be an outer join, records
in SurvTest that can't find another record (in the copy of SurvTest) where the future date hasn't materialized
will survive (with all NUll values in SurvTest2.

Now, adding a condition of:
IsNull({SurvTest2.ActualDate}) would complete
the solution by selecting only those records where
indeed the future date didn't materialize as an actual date.

Cheers,
- Ido ixm7@psu.edu