Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
So add it as a distinct field and as part of the key in the date bridge?
Cory
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;