P3X GitList

/* from UNIVERSITY HOSPITAL BRISTOL to UNIVERSITY HOSPITALS BRISTOL NHS FOUNDATION TRUST */
/* Beasley M & Shelley-Fraser G */
DELETE FROM referrer_department WHERE id IN (32589,33032); /* not used in requests */
/* Hughes CW */
UPDATE referrer_department rd SET rd.parent_organisation_id = 4 WHERE rd.id = 33560;

UPDATE referral_sources rs SET rs.parent_organisation_id = 4, rs.organisation_code = 'RA709'
	WHERE rs.display_name = 'Bristol Dental Hospital';
UPDATE referral_sources rs SET rs.parent_organisation_id = 4, rs.organisation_code = 'RA799' /* ficticious code */
	WHERE rs.display_name = 'Bristol Oncology Centre';
UPDATE referral_sources rs SET rs.parent_organisation_id = 4, rs.organisation_code = 'RA701'
	WHERE rs.display_name = 'Bristol Royal Infirmary';
UPDATE referral_sources rs SET rs.parent_organisation_id = 4, rs.organisation_code = 'RA723'
	WHERE rs.display_name = 'Bristol Childrens Hospital';
/* get rid of duplicates from referrer_department */
UPDATE requests r SET r.referrer_department_id = 24098 WHERE r.referrer_department_id = 32570; /* Marks DI */
UPDATE requests r SET r.referrer_department_id = 32559 WHERE r.referrer_department_id = 32577; /* Moldovan C */
UPDATE requests r SET r.referrer_department_id = 32569 WHERE r.referrer_department_id = 32568; /* Protheroe RE */
UPDATE requests r SET r.referrer_department_id = 18653 WHERE r.referrer_department_id = 32573; /* Ramani P */
DELETE FROM referrer_department WHERE id IN (32570,32577,32568,32573);
UPDATE referrer_department SET parent_organisation_id = 4 WHERE parent_organisation_id in (426, 490, 499);
/* unnecessary parent orgs */
DELETE FROM parent_organisations WHERE id IN (426, 490, 499);

/* run this pre & post - checks OK */
select r.request_number, r.year, rs.display_name, rs.organisation_code, po.parent_code, ref.name
from requests r 
	join patient_case pc on r.patient_case_id = pc.id
	join referral_sources rs on pc.referral_source_id = rs.id
	join referrer_department rd on r.referrer_department_id = rd.id
	join referrers ref on rd.referrer_id = ref.id
	join parent_organisations po on rd.parent_organisation_id = po.id
where rs.display_name rlike 'bristol' and rs.referral_type_id = 4 # and rs.id is null
order by rs.display_name, ref.name, r.id;