Simon has explained what we wanted to do with QOF in the previous post.
Looking at the prevalence data from the QOF files, we see the GP Code, the Indicator Group (the health condition this relates to), the number of patients on that GP’s register that have that condition, the list type and list size.
These last two fields are important – not all conditions are measured across all ages: in the example below, Chronic Kidney Disease is only counted for people over 18.
As we are interested in the total number of patients, we need to create a summary table containing just the total number of patients within a practice:
Create table gp_practice_size as
select gp_code, max(list_size) as total
from prevalence group by gp_code;
Having done this, we can estimate the prevalence of each indicator group within a GP practice:
create table gp_prevalence (
select a.gp_code, a.indicator_group, a.register, b.total,
(a.register / b.total) as propwithind,
(a.register / b.total) * 100 as percind
from prevalence a left join gp_practice_size b on a.gp_code = b.gp_code;
As MySQL does not allow the casting of calculated fields to float or double, we need to define the structure of the table. If we did not do this, MySQL would store the new fields propwithind and percind as DECIMAL(14,4) which loses a lot of precision.
As we will be joining on this table later, it is a good idea to add an index:
ALTER TABLE `gp_prevalence` ADD INDEX(`gp_code`);
Our next task is to calculate the total number of registered patients within each LSAO using the following table:
|AGP001||THE AVERAGE SURGERY||LSOA001||100|
|AGP001||THE AVERAGE SURGERY||LSOA002||400|
|AGP001||THE AVERAGE SURGERY||LSOA003||500|
create table lsoa_patients as
select lsoa, sum(patients) as patients from gp_lsoa group by lsoa;
From the propwithind field we created above, we can now estimate for each LSOA with a GP practice the prevalence of each indicator group:
create table gp_lsoa_prev (
select a.gp_code, a.lsoa, a.patients,
b.indicator_group, (a.patients * b.propwithind) as gp_lsoa_ind
from gp_lsoa a left join gp_prevalence b on a.gp_code = b.gp_code;
Again, we have to define the structure of the table to ensure the precision of DOUBLE as opposed to DECIMAL(14,4).
Finally, we need to roll up the data to LSOA level:
create table lsoa_prevalence_total as
SELECT lsoa, indicator_group, sum(gp_lsoa_ind) as lsoa_ind
group by lsoa, indicator_group;