DIsplaying services for client that were active during the date range.

[QueTech]

I am using Crystal 10 on Win Xp machine. I use OBDC connection to Intersystems Cashe driver to Cashe database.

I need to display services provided to clients that were active during the date range provided.

Tables utilized tx_history_all , Movement_history.

I create a parameter (?date) using the fied tx_history_all.date_of_service.

In the detail section I list (tx_history_all.PATID)=client ID,tx_history_all.date_of_service, (tx_history_all.service_code)=type of service.

I display all dates and services provided during that date range. However, I am unable filter only the services that were provided to the client when he was active.

The Movement_history table provides the following fields.
Movement_history.admit_date=last admission date
Movement_history.discharge_date=last discharge date
Movement_history.movement_date=all movement date
Movement_history.program_code=status of client

Example date for PATID=100

Movement_history.admit_date
12/20/2004
1/1/2005
6/1/2005
12/1/2005
Movement_history.discharge_date
12/31/2004
3/1/2005
8/1/2005
Movement_history.movement_date
12/20/2004
12/31/2004
1/1/2005
3/1/2005
6/1/2005
8/1/2005
12/1/2005
Movement_history.program_code
Pre-admint
Discharge
Admit
Discharge
Admit
Discharge
Admit



I need to code the recrod selection to allow only those service that fit my criteria.

Any help is appreciated.

 

[synapsevampire]

I'd think there'd be another field which uniqely identifies each admission and discharge period, an event sort of ID which allows you to join the 2 tables and demonstrate which admission ties to which discharge.

See anything like that?

Otherwise it's much more difficult and resource consuming to determine which transactions are associated.

If not, then you'll need to bring in ALL rows and figure out the ranges on the fly, which even a mediocre dataase designer should understand.

Have a look for another key.

-k