Background: I'm trying to create a denominator for Patients that have a Disease Diagnosis Date on or before the time they started taking the Drug. We want to make sure to only capture with high confidence that the patients are taking the drugs we are interested in AFTER they were diagnosed with the disease we are interested in. The thought is if someone is taking a drug before their diagnosis date then they most likely were taking that drug for a different disease.
Table/Relationship Structure: Patient dimension table in the center of the schema, fact Drug table (many drugs to one patient), fact Disease table (many diseases to one patient). There is no direct relationship between Drug and Disease tables.
Silly Example - yes this isn't a disease: Drugs of Interest: Advil & Tylonol
Patient 109247 took Advil on 1/8/2020 Patient 109247 took Tylonol on 1/17/2020 Patient 109247 was diagnosed with Headache on 1/20/2020 This patient should not be counted in our denominator at all because they were diagnosed after taking the drugs.
SQL: SELECT Count (Distinct dr.PatientID) FROM DrugTable dr INNER JOIN DiseaseTable di on di.PatientID = dr.PatientID WHERE dr.DrugDate >= di.DiseaseDate