Migration script for the patients table

PatientsTableMigrations.sql

-- Script to migrate patients data from old-blis to new blis
-- 0 warnings
-- Some records have no calculatable date of birth, I have put the DOB as the timestamp


insert into iblis.patients 
	(patient_number, name, dob, gender, external_patient_number, created_at)

select  patient_id, name, 
	(case WHEN dob = '0000-00-00'
	THEN CONVERT( 
		date_sub(DATE_FORMAT(ts,  '%Y-%m-%d'), INTERVAL age year)
		USING latin1 )
WHEN dob IS NULL 
	THEN  partial_dob 
ELSE dob 
END) as dob,

if(sex = 'M', 0, 1) as sex, surr_id, ts  from blis_301.patient