Help with Circular Reference

[Cory]

I've got a problem with a circular reference that seem like it should be easy to overcome, but I just can't seem to figure it out.  We have drugs that get dispensed to a program on a date, we also have surgeries that are done for a program on a date.  We need to be able to determine the quantity of a specific drug that was used on a date or in a date range and the program that used it.  We also need to be able to count the number of surgeries on a date or in a date range for a specific program.  Ultimately we need to see the average usage of a drug per surgery.  I'm having a hard time resolving the issue below:

Any help is appreciated.

Cory

[treysmithdev]

You need to move the Program Description field to your link(bridge) table. Then have your DrugUsage and Surgery tables link to the link table using concatenated key:

Eg.

DrugUsageKey&'|'&ProgramDescription as %DrugUsageProgramKey,

[Cory]

So add it as a distinct field and as part of the key in the date bridge?

Cory

[treysmithdev]

Yeah. Something like this:

DrugUsage:
Load
    DrugUsageKey &'|'& ProgramDescription as %DrugUsageProgramKey,
    DrugUsageKey,
    ProgramDescription d_ProgramDescription,
    DispenseDate,
    ....
From
    ...

Surgery:
Load
    SurgeryID &'|'& ProgramDescription as %SurgeryProgramKey,
    SurgeryID,
    ProgramDescription as s_ProgramDescription,
    ORDate,
    ....
From
    ....


DateBridge:
Load distinct
    %DrugUsageProgramKey,
    d_ProgramDescription as ProgramDescription,
    DispenseDate as Date,
    'Drug' as DateType
Resident
     DrugUsage;

Concatenate(DateBridge):
Load distinct
    %SurgeryProgramKey,
    s_ProgramDescription as ProgramDescription,
    ORDate as Date,
    'Surgery' as DateType
Resident
     Surgery;

Drop Fields d_ProgramDescription, s_ProgramDescription;