SELECT * FROM (SELECT DATE_FORMAT(pv.prevention_date,'%Y-%m-%d') AS "Date", IF((@a:=DATEDIFF (pv.prevention_date, DATE_FORMAT(CONCAT(dm.year_of_birth,'-',dm.month_of_birth,'-',dm.date_of_birth),'%Y-%m-%d') ) )> 30, IF(@a > 730, CONCAT(FLOOR(@a / 365.25),"y"), CONCAT(FLOOR(@a / 30.4375),"m")), CONCAT(@a,"d") ) AS "Age", pv.prevention_type as "Vaccine", IF( (@b1:=pv.prevention_type) LIKE '%DT%' OR @b1 LIKE '%TD%', "X", @b1:=' ') AS "D
I
P
H
T
H
E
R
I
A", IF( (@b2:=pv.prevention_type) LIKE '%DT%' OR @b2 LIKE '%TD%', "X", @b2:=' ') AS "T
E
T
A
N
A
S



", IF( (@b3:=pv.prevention_type) LIKE '%aP%', "X", @b3:=' ') AS "P
E
R
T
U
S
S
I
S

", IF( (@b4:=pv.prevention_type) LIKE '%IPV%', "X", @b4:=' ') AS "P
O
L
I
O





", IF( (@b5:=pv.prevention_type) LIKE '%HiB%', "X", @b5:=' ') AS "H
I
B







", IF( (@b6:=pv.prevention_type) LIKE '%Pneu%', "X", @b6:=' ') AS "P
N
E
U
M
O
C
C
O
C", IF( (@b7:=pv.prevention_type) LIKE '%Rot%', "X", @b7:=' ') AS "R
O
T
A
V
I
R
U
S

", IF( (@b8:=pv.prevention_type) LIKE '%MR%' OR @b8 LIKE 'M', "X", @b8:=' ') AS "M
E
A
S
L
E
S



", IF( (@b9:=pv.prevention_type) LIKE '%MMR%', "X", @b9:=' ') AS "M
U
M
P
S





", IF( (@b10:=pv.prevention_type) LIKE '%MR%', "X", @b10:=' ') AS "R
U
B
E
L
L
A



", IF( (@b11:=pv.prevention_type) LIKE 'VZ' OR @b11 LIKE '%RV%', "X", @b11:=' ') AS "V
A
R
I
C
E
L
L
A

", IF( (@b12:=pv.prevention_type) LIKE '%Men%', "X", @b12:=' ') AS "M
E
N
I
N
G
I
T
I
S", IF( (@b13:=pv.prevention_type) LIKE '%HepB%' OR @b13 LIKE '%HepAB%' OR @b13 LIKE '%-HB%', "X", @b13:=' ') AS "H
E
P

B





", IF( (@b14:=pv.prevention_type) LIKE '%HPV%', "X", @b14:=' ') AS "H
P
V







", IF( (@b15:=pv.prevention_type) LIKE '%FLU%', "X", @b15:=' ') AS "I
N
F
L
U
E
N
Z
A

", IF( (@b16:=pv.prevention_type) LIKE '%HepA%' OR @b16 LIKE '%HA-%', "X", @b16:=' ') AS "H
E
P

A





", IF( (@b17:=pv.prevention_type) LIKE '%ZV', "X", @b17:=' ') AS "S
H
I
N
G
L
E
S


", IF( (@b18:=pv.prevention_type) LIKE '%COVID%', "X", @b18:=' ') AS "C
O
V
I
D





", IF( LENGTH(TRIM(CONCAT(@b1,@b2,@b3,@b4,@b5,@b6,@b7,@b8,@b9,@b10,@b11,@b12,@b13,@b14,@b15,@b16,@b17,@b18)))<>0,' ',"X") AS "O
T
H
E
R
S




", pe.val AS "Name", p2.val AS "Lot", IF( pv.provider_no > 0, CONCAT(LEFT(pr.first_name,1),".", LEFT(pr.last_name,1),"."), pv.provider_name ) AS "Provider" FROM preventions pv INNER JOIN preventionsExt pe ON pv.id = pe.prevention_id INNER JOIN preventionsExt p2 ON pv.id = p2.prevention_id INNER JOIN demographic dm ON pv.demographic_no = dm.demographic_no LEFT JOIN provider pr ON pv.provider_no = pr.provider_no WHERE pv.demographic_no = '{demo_no}' AND pv.deleted = 0 AND pv.refused = 0 AND pe.keyval = "name" AND p2.keyval = "lot" ORDER BY Date) AS sub UNION ALL (SELECT " "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," ") UNION ALL (SELECT CONCAT(d2.last_name,",
",d2.first_name), IF((@a:=DATEDIFF (CURDATE(), DATE_FORMAT(CONCAT(d2.year_of_birth,'-',d2.month_of_birth,'-',d2.date_of_birth),'%Y-%m-%d') ) )> 30, IF(@a > 730, CONCAT(FLOOR(@a / 365.25),"y"), CONCAT(FLOOR(@a / 30.4375),"m")), CONCAT(@a,"d") ), CONCAT('DOB:
',d2.year_of_birth,'-',d2.month_of_birth,'-',d2.date_of_birth), " "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," " FROM demographic d2 WHERE d2.demographic_no = '{demo_no}') ;