# 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