How to Estimate the diagnosis of health conditions in England

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.

gp_code	Indicator_group	register	patient_list_type	list_size
AGP001	AF	100	TOTAL	1000
AGP001	AST	300	TOTAL	1000
AGP001	CKD	125	18OV	800

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 (
gp_code char(6),
indicator_group varchar(20),
register int(11),
total int(11),
propwithind double,
percind double)
as
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:

gp_code	gp_name	lsoa	patients
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 (
gp_code char(6),
lsoa varchar(12),
patients int(11),
indicator_group varchar(20),
gp_lsoa_ind double)
as
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
FROM gp_lsoa_prev
group by lsoa, indicator_group;