Note!

1.Tricky diagnostic codes that the NHS have modified from the standard are:

A35X may need to leave if there is a match is reference table called 1ICD10_4 (supplied by Bob)

This also goes for the following codes:

M45X9

C19X9

C20X9

M01.50A

N93.9 D

2.The procedure (OPCS) codes are all to 4 charcters in length and start with a letter, for example: A123. However, the NHS have been found to modify these too, for example:

M123+Elbow nedds to be M123

3.Before running the script you will need to convert the NHS Trust's small number policy to actual cases. This varies because the small policy is not standardised across Trusts:

SQL query definition:

FLOOR(min_value + RAND() * (max_value - min_value +1));

Query to update table when cases are defined as <10 - Between 1 and 9 for <10:

UPDATE `table_name` SET `cases`= FLOOR(1 + RAND() * (9 - 1 +1)) WHERE `cases`= '<10';



Run script Non numeric codes and produces a csv file to download v0.1.php to identify any unwanted characters in the codes within the raw data provided by the NHS. Note! This script is no longer used.


Execute script Step 1 - Check serial match region and code character count v0.3.php


Execute script Step 2 - Process NHS Trust diagnostics & procedure data v0.7.php. The script carries out the following processes:

1.Clean-up operations following a script crash - Check if the temporary table exists & if so delete the table. 

2.Create a temp table fuw allocating unmatched code codes and cases.

3.Validate table name contains the word "diagnostics" or "procedures".

4.Add additional columns `country`, `region` and `code_3` to the diagnostics table and only `country` and `region` to the procedures table.

5.Adds a Primary Key column called `id` for the purpose of tracking and displaying modifications to the data.

6.Trim leading and trailing spaces from columns with a column type VARCHAR skipping INT and  non-string columns. Display the modifications.

7.Remove rows where `cases` are blank, 0, or NULL.

8.Remove diagnostics R to Z codes. Do NOT do this for Procedures:

DELETE FROM $table_name  WHERE LEFT(`code`, 1) BETWEEN 'R' AND 'Z';

9.List codes that do not start with a letter followed by a number and remove for both diagnostics and procedures i.e. 1234, CC12, etc.

10.List codes that are  < 3 (2 or less) and remove for both diagnostics and procedures.

11.Removes unwanted characters from VARCHAR columns and skip INT and YEAR columns (meaning non-string types) as defined in array $unwanted_chars and display any modifcations. For example:

[spaces]

,

.

*

!

Display the modifications.

Note! Takes into consideration if the first character is alphabetic match (i.e. the start of a valid code) and then processes the rest of the string. Don't want codes removed!

12.Remove any defined alphabetic character appended to the code only if it is followed by a numeric character as defined in array $alpha_chars  and display the modifications. For example:

A

D

X

13.Removes the Primary Key column `id` as no longer required as will upset the code to deal with duplications.

14.For the diagnostics table only modify the `code` column structure type VARCHAR length to 4 and process the duplicates leaving just one occurrence of the code with the cases summed.

15.Process any duplicates for the procedure table so leaving only one occurrence of code with the cases summed.

16.For diagnostics only  - Compare $table_name code column with code column in reference table 1icd10_4 (supplied by Bob) and if there is a match step onto the next. If not strip the appended alpha character from the code and try for a match. Also, will try to match upon a keyword. As a last resort will allocate a code from the fix table and is processed based on per case base. Also, re-add / remove a Primary Key column called `id` for the purpose of tracking and displaying modifications to the data.

17.Process diagnostic duplicates leaving just one occurrence of the code with the cases summed.

18.Process procedure duplicates leaving just one occurrence of the code with the cases summed.

19.Modifies the `cases` column structure type to INT from VARCHAR.

20.Populates the columns from the provided comprehensive hospitals reference table:

`country`

`region`

`code_3`

21.Inspect the table for any empty cells within the VARCHAR, YEAR, and INT columns. If any blank cells are found, display a confirmation message detailing the specific column(s) containing the blank cells and flag them accordingly.

22.Script completion clean up operations


Execute the script Aggregation to final tables v0.2.php. This script performs the following tasks: It merges all tables with names ending in _diagnostics and _procedures into unified tables, such as uk_diagnostics and uk_procedures.


The final stage consists of preparing the data for presentation to our Technical Director, who will oversee its upload to the PurleXtra production database. Additionally, if the data spans multiple years, it should be organized into separate yearly tables using a MySQL query.

For diagnostics:

INSERT INTO `uk_diagnostics_19_03_25_additions_2023`(`year`, `serial`, `country`, `region`, `code`, `code_3`, `cases`) SELECT `year`, `serial`, `country`, `region`, `code`, `code_3`, `cases` FROM `0uk_diagnostics` WHERE `year` = '2023';

For procedures:

INSERT INTO `uk_procedures_19_03_25_additions_2023`(`year`, `serial`, `country`, `region`, `code`, `cases`) SELECT `year`, `serial`, `country`, `region`, `code`, `cases` FROM `0uk_procedures` WHERE `year` = '2023';


The End