[hmrn_rebuild_tables]
DROP TABLE IF EXISTS `staging_sites`;
CREATE TABLE `staging_sites` (
  `id` int(11) NOT NULL auto_increment,
  `description` varchar(255) NOT NULL default '',
  `location` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `patient_staging_site`;
CREATE TABLE `patient_staging_site` (
  `patient_id` int(11) NOT NULL default '0',
  `site_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`patient_id`,`site_id`),
  KEY `site_id` (`site_id`)
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `patient_chronologies`;
CREATE TABLE `patient_chronologies` (
  `patient_id` int(11) NOT NULL default '0',
  `diagnosed` date default NULL,
  PRIMARY KEY  (`patient_id`)
) ENGINE=InnoDB;

[hmrn_calculate_patient_age]
SELECT p.dob, c.diagnosed
FROM patients p
    JOIN patient_chronologies c on c.patient_id = p.id
WHERE patient_id = ?

[hmrn_patient_diagnostic_categories_map]
SELECT 
  DISTINCT(t7.description), 1
FROM
  requests t1 
  JOIN patient_case t2 ON (t1.patient_case_id = t2.id)
  JOIN patients t3 ON (t2.patient_id = t3.id)
  JOIN request_report t4 ON (t1.id = t4.request_id)
  JOIN diagnoses t5 ON (t4.diagnosis_id = t5.id)
  JOIN icdo_category t6 ON (t5.icdo3 = t6.icdo3)
  JOIN diagnostic_categories t7 ON (t6.diagnostic_category_id = t7.id)
WHERE
  t2.patient_id = ?
  
[hmrn_staging_sites]
SELECT description,location 
FROM staging_sites