# 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;