SQL Query Cheat Sheet for Final Data Processing
# Use the following queries where the supplied cases are in columns ordered by year
INSERT INTO `puglia_asl_lecce_diagnostics`(`serial`, `ammap_region`, `icd9`, `cases`) SELECT `serial`, `ammap_region`,`icd9`,`2018` FROM `lombardia_asst_nord_milano_all_years`;
UPDATE `puglia_asl_lecce_diagnostics` SET `year`='2018' WHERE `year`='0';
INSERT INTO `puglia_asl_lecce_diagnostics`(`serial`, `ammap_region`, `icd9`, `cases`) SELECT `serial`, `ammap_region`,`icd9`,`2019` FROM `lombardia_asst_nord_milano_all_years`;
UPDATE `puglia_asl_lecce_diagnostics` SET `year`='2019' WHERE `year`='0';
INSERT INTO `puglia_asl_lecce_diagnostics`(`serial`, `ammap_region`, `icd9`, `cases`) SELECT `serial`, `ammap_region`,`icd9`,`2020` FROM `lombardia_asst_nord_milano_all_years`;
UPDATE `puglia_asl_lecce_diagnostics` SET `year`='2020' WHERE `year`='0';
INSERT INTO `puglia_asl_lecce_diagnostics`(`serial`, `ammap_region`, `icd9`, `cases`) SELECT `serial`, `ammap_region`,`icd9`,`2021` FROM `lombardia_asst_nord_milano_all_years`;
UPDATE `puglia_asl_lecce_diagnostics` SET `year`='2021' WHERE `year`='0';
INSERT INTO `puglia_asl_lecce_diagnostics`(`serial`, `ammap_region`, `icd9`, `cases`) SELECT `serial`, `ammap_region`,`icd9`,`2022` FROM `lombardia_asst_nord_milano_all_years`;
UPDATE `puglia_asl_lecce_diagnostics` SET `year`='2022' WHERE `year`='0';
INSERT INTO `puglia_asl_lecce_diagnostics`(`serial`, `ammap_region`, `icd9`, `cases`) SELECT `serial`, `ammap_region`,`icd9`,`2023` FROM `lombardia_asst_nord_milano_all_years`;
UPDATE `puglia_asl_lecce_diagnostics` SET `year`='2023' WHERE `year`='0';
# Remove zero case rows
DELETE FROM `puglia_asl_lecce_diagnostics` WHERE `cases`='0';
# Delete ICD9 codes where the first character is NOT equal to a `0` to `9`
DELETE FROM `puglia_asl_lecce_diagnostics` WHERE SUBSTRING(`icd9`, 1, 1) NOT REGEXP '^[0-9]';
# Remove decimal point in ICD9 code
UPDATE `puglia_asl_lecce_diagnostics` SET `icd9`=replace(`icd9`, '.', '');
# Remove a "," or "." in cases
UPDATE `puglia_asl_lecce_diagnostics` SET `cases`=replace(`cases`, '.', '');
UPDATE `puglia_asl_lecce_diagnostics` SET `cases`=replace(`cases`, ',', '');
# Remove space either side of cell
UPDATE `puglia_asl_lecce_diagnostics` SET `year` = TRIM(`year`);
UPDATE `puglia_asl_lecce_diagnostics` SET `serial` = TRIM(`serial`);
UPDATE `puglia_asl_lecce_diagnostics` SET `icd9` = TRIM(`icd9`);
UPDATE `puglia_asl_lecce_diagnostics` SET `ammap_region` = TRIM(`ammap_region`);
# Delete rows containing 9999 as it's for other and unspecified complications of medical care
DELETE FROM `puglia_asl_lecce_diagnostics` WHERE `icd9`='9999';
# List any duplicates
SELECT `year`, `serial`, `ammap_region`, `icd9`, COUNT(*)
FROM `puglia_asl_lecce_diagnostics`
GROUP BY `year`, `serial`, `ammap_region`, `icd9`
HAVING COUNT(*) > 1;
# List rows where ICD9s are not between 3 and 5 characters long
SELECT `serial`, `ammap_region`, `icd9`
FROM `puglia_asl_lecce_diagnostics`
WHERE CHAR_LENGTH(`icd9`) < 3 OR CHAR_LENGTH(`icd9`) > 5;