Trying to query a list of vaccinations
Posted on 2013-01-20
I am a pediatrician trying to use MySQL to analyze immunization records (although I may need to move my work to SQL Server). I am still a novice at SQL.
A bit of background may help. In order to achieve immunity to a given disease, a patient generally needs to receive a series of doses of a vaccine against that disease.
Vaccine doses must be given at the correct ages, and the time intervals between doses must meet prescribed minimums in order for immunity to be achieved. Recently the US Centers for Disease Control and Prevention published a formal schema to guide developers in writing software for analyzing immunization records. The goal of the schema is to help developers write software that helps clinicians know where a patient stands in their immunization process and that guides clinicians in deciding which additional vaccine doses will be needed going forward and when those additional doses will need to be given. The schema defines a series of what it calls "target doses" for each vaccine. Each "target dose" in the series has specific criteria such the patient's age when it was given and the minimum time since the previous dose was given. The vaccine doses actually given to a patient can be compared to the target doses to see whether they meet the criteria for the target doses. Doses given are analyzed in the order they were given. The earliest dose given that meets criteria for Target Dose 1 is said to have satisfied Target Dose 1. Subsequent doses given are analyzed to see which is the next earliest dose given that satisfies Target Dose 2, and so on, until either all target doses in the series have been satisfied or there are no more doses given left to analyze. Each dose given can satisfy at most 1 target dose. Once all of a patient's target doses have been satisfied, then that immunization series is considered to be complete.
So, my first task has been to take a list of vaccine doses that have been given to a patient and see how they match up with the target doses. Each dose given may happen to meet the criteria for more than one target dose, but an individual dose given can only count towards satisfying one of the target doses in the series.
Now, suppose I have this hypothetical list of 3 target doses
where <criteria_n> is actually several fields that collectively give the criteria for satisfying the nth Target Dose.
And this hypothetical list of 4 doses given
Where DoseGiven1 < DoseGiven2 < DoseGiven3 < DoseGiven4
The doses given could be in the form of date fields or integers.
I can use the criteria to generate a cross product that lists every target dose along with every dose given that potentially could satisfy that target dose.
Suppose that using the criteria, I get this result set:
I need to narrow that result set to this one:
or, equivalently, to this one:
TargetDose1, DoseGiven1, Yes
TargetDose1, DoseGiven2, No
TargetDose1, DoseGiven3, No
TargetDose1, DoseGiven4, No
TargetDose2, DoseGiven3, Yes
TargetDose2, DoseGiven4, No
TargetDose3, DoseGiven3, No
TargetDose3, DoseGiven4, Yes
Note that in this example, DoseGiven2 does not satisfy any of the target doses. It meets criteria for satisfying Target Dose 1, but DoseGiven1 is assigned to Target Dose 1. Target Dose 2 is satisfied by DoseGiven 3 but not by DoseGiven2.
I have spent many hours trying to figure this out. I would appreciate it if anyone could help.