# get list of names with unit_no = UNKNOWN & freq > 1:
drop table if exists temp;
create temporary table temp( first_name varchar(250), last_name varchar(250) );
insert into temp (last_name, first_name)
select p.last_name,p.first_name
from patients p join patient_case pc on pc.patient_id = p.id
where unit_number = 'unknown'
group by p.last_name, p.first_name
having count(*) > 1;
# get patient.id's of names from above query:
drop table if exists temp2;
create temporary table temp2( id int );
insert into temp2
select p.id from patients p join temp t on ( p.last_name = t.last_name
and p.first_name = t.first_name )
join patient_case pc on pc.patient_id = p.id
group by p.id
having count(*) = 1;
# convert patient.id's to names from above query:
select last_name, first_name from patients p join temp2 t on t.id = p.id
group by last_name, first_name
order by last_name, first_name;
################################################################################
# hilis3 nhs number duplicates:
drop table if exists temp;
create temporary table temp( NHSNo char(10) );
insert into temp (NHSNo)
select distinct(NHSNo)
from hilis3.PID where NHSNo is not null
group by NHSNo
having count(NHSNo) > 1;
#select * from temp
# save nhs numbers of unique patient entries from above nhs numbers:
drop table if exists temp2;
create temporary table temp2( NHSNo char(10) );
insert into temp2 (NHSNo)
select p.NHSNo
from hilis3.PID p, temp t
where t.NHSNo = p.NHSNo
group by LName,FName,DoB,Sex;
#select * from temp2 group by NHSNo having count(*) > 1
# get duplicate nhs numbers from above list:
drop table if exists temp3;
create temporary table temp3( NHSNo char(10) );
insert into temp3 (NHSNo)
select t.* from temp2 t left join test.ResolvedNHSNo r on t.NHSNo = r.NHSNo
where r.NHSNo is null group by NHSNo having count(*) > 1;
# select * from temp3
# run this:
select LName,FName,DoB,Sex,t.NHSNo
from hilis3.PID p, temp3 t
where t.NHSNo = p.NHSNo
group by LName,FName,DoB,Sex,NHSNo
order by t.NHSNo;
# remove unresolved duplicates from above list & save as setup/hilis3_convert/new_resolved_nhs_numbers.xls,
# run setup/hilis3_convert/add_resolved_nhs_numbers.pl