A table lists periods of administered medications and associated side effects:
drop table if exists mini_exposure_to_medication;
create table mini_exposure_to_medication (
medpatkey int(10),
medage int(4),
MYSTART varbinary(10),
MYSTOP varbinary(29),
MYMEDCODE int(9),
onset_date date,
has_ae varchar(5)
);
The column 'has_ae' ("has after-effects") is supposed to have four options:
- 'never': took the medication and never had a side effect
- 'N'; took medication and had a reaction after a period of time such that it cannot be attributed to the medication
- 'Y': took a medication and had a reaction during or immediately after taking the medication
- 'B': had the side effect before taking the medication
Here is sample data:
insert into mini_exposure_to_medication
(medpatkey, medage, MYSTART, MYSTOP, MYMEDCODE, onset_date, has_ae) Values
(56,64,'2024-08-05','2024-08-05',7,NULL,"never"),
(56,72,'2024-12-13','2024-12-03',6,NULL,"never"),
(56,72,'2024-12-06','2024-12-01',6,NULL,"never"),
(56,72,'2025-02-23','2025-02-18',6,NULL,"never"),
(40,68,'2024-06-27','2024-07-27',6,NULL,"never"),
(40,68,'2025-01-14','2025-02-13',6,NULL,"never"),
(53,80,'2024-11-10','2024-11-04',1,NULL,"never"),
(53,80,'2024-10-21','2024-10-16',1,NULL,"never"),
(53,80,'2024-05-26','2024-05-21',1,NULL,"never"),
(53,80,'2024-09-30','2024-09-25',1,NULL,"never"),
(53,80,'2025-02-21','2025-02-16',1,NULL,"never"),
(53,80,'2024-06-20','2024-06-15',1,NULL,"never"),
(94,78,'2024-05-09','2024-05-09',5,NULL,"never"),
(781,47,'2024-12-22','2024-12-22',1,NULL,"never"),
(781,47,'2024-08-19','2024-08-19',1,NULL,"never"),
(790,76,'2024-10-28','2024-11-27',6,NULL,"never"),
(790,76,'2024-12-08','2025-01-07',6,NULL,"never"),
(294,60,'2024-05-04','2024-04-29',6,NULL,"never"),
(294,60,'2024-10-28','2024-10-23',6,NULL,"never"),
(294,60,'2024-10-24','2024-10-19',6,NULL,"never"),
(294,60,'2024-05-19','2024-05-14',6,NULL,"never"),
(515,75,'2025-02-18','2025-02-18',7,NULL,"never"),
(538,80,'2025-01-08','2025-01-08',1,NULL,"never"),
(538,80,'2024-06-28','2024-06-28',1,NULL,"never"),
(538,80,'2024-11-29','2024-11-29',1,NULL,"never"),
(538,80,'2024-06-03','2024-06-03',1,NULL,"never"),
(538,80,'2024-11-04','2024-11-04',1,NULL,"never"),
(538,80,'2024-04-11','2024-04-11',1,NULL,"never"),
(538,80,'2024-04-12','2024-04-12',1,NULL,"never"),
(898,66,'2024-08-01','2024-11-29',2,'2024-03-29',"B"),
(898,67,'2024-08-31','2024-09-30',2,'2024-03-29',"B"),
(898,67,'2024-09-29','2025-02-26',5,'2024-03-29',"B"),
(327,66,'2024-06-06','2024-06-06',1,'2025-03-09',"N"),
(327,67,'2025-03-09','2025-03-09',1,'2025-03-09',"Y"),
(2322,80,'2024-12-08','2024-12-08',5,'2024-11-02',"B"),
(2322,80,'2024-10-27','2024-10-27',5,'2024-11-02',"B"),
(2089,68,'2024-08-30','2024-08-25',7,'2025-03-11',"B"),
(2089,68,'2024-08-30','2024-08-25',7,'2024-06-11',"B"),
(2089,68,'2024-07-12','2024-07-07',7,'2025-03-11',"B"),
(2089,68,'2024-07-12','2024-07-07',7,'2024-06-11',"B");
A basic rule of thumb for discovering what query design pattern to apply is to look for normalisation problems. The mini_exposure_to_medication (`mem`) table needs normalising to get to 3NF: remove the side effects data from `mem` to a child table. Or in relationspeak, project them.
Then we have a parent table with one row per medication period, and a child table with one row per side effect event per medication period:
DROP TABLE IF EXISTS med_exposures;
CREATE TABLE med_exposures (
ID INT PRIMARY KEY AUTO_INCREMENT,
medpatkey INT,
medage SMALLINT,
medstart DATE,
medstop DATE,
mymedcode INT
);
DROP TABLE IF EXISTS side_effects;
CREATE TABLE side_effects (
id INT PRIMARY KEY AUTO_INCREMENT,
memkey INT,
onset_date DATE NOT NULL
);
INSERT INTO med_exposures
SELECT DISTINCT NULL, medpatkey,medage,mystart,mystop,mymedcode
FROM mini_exposure_to_medication;
INSERT INTO side_effects
SELECT NULL,m.id,mem.onset_date
FROM med_exposures m
JOIN mini_exposure_to_medication mem ON m.medpatkey=mem.medpatkey AND m.medstart=mem.mystart
WHERE mem.onset_date IS NOT NULL;
Now side-effect queries are dead simple, eg ...
SELECT
m.id,m.medstart,m.medstart,
s.onset_date,
CASE
WHEN s.onset_date IS NULL THEN 'Never'
WHEN s.onset_date < m.medstart THEN 'Before'
WHEN DATEDIFF( s.onset_date, m.medstart ) > 30 THEN 'No'
ELSE 'Yes'
END AS 'Side effects'
FROM med_exposures m
LEFT JOIN side_effects s ON m.id = s.memkey
HAVING `side effects` <> 'Never';
|
|