DROP TABLE IF EXISTS lab_analyte_range_reference;
CREATE TABLE lab_analyte_range_reference  (
  id int NOT NULL AUTO_INCREMENT,
  lab_analyte_id INT NOT NULL,
  gender_code varchar(100)  NOT NULL,
  age_from varchar(100)  NOT NULL,
  age_from_type varchar(100)  NOT NULL,
  age_from_days varchar(100)  NOT NULL,
  age_to varchar(100)  NOT NULL,
  age_to_type varchar(100)  NOT NULL,
  age_to_days varchar(100)  NOT NULL,
  range_from varchar(100)  NOT NULL,
  range_to varchar(200)  NOT NULL,
  display_rr text NOT NULL,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


Insert Into lab_analyte_range_reference
(lab_analyte_id, gender_code, 
age_from, age_from_type, age_from_days, 
age_to, age_to_type, age_to_days,
created_at, created_by)
Select b.id, gender_code,
			age_from, age_from_type, age_from_days,
			age_to, age_to_type, age_to_days,
			CURRENT_DATE, ''
			from lab_range_reference a, lab_analyte_master b
			Where a.component_id = b.analytecode
			And (a.component_id != '---' And a.component_id != '' And a.component_id is not null);

Insert Into lab_analyte_range_reference
(lab_analyte_id, gender_code, 
age_from, age_from_type, age_from_days, 
age_to, age_to_type, age_to_days,
created_at, created_by)
Select b.id, gender_code,
			age_from, age_from_type, age_from_days,
			age_to, age_to_type, age_to_days,
			CURRENT_DATE, ''
			from lab_range_reference a, lab_analyte_master b
			Where a.analyte_id = b.analyteid or a.analyte_id = b.analytecode
			And (a.component_id = '--' OR a.component_id = '' OR a.component_id is null);


Select  gender_code,
			age_from, age_from_type, age_from_days,
			age_to, age_to_type, age_to_days,
			CURRENT_DATE, ''
			from lab_range_reference a
			Where (a.component_id = '--' OR a.component_id = '' OR a.component_id is null);

Insert Into lab_analyte_range_reference
(lab_analyte_id, gender_code, 
age_from, age_from_type, age_from_days, 
age_to, age_to_type, age_to_days,
created_at, created_by,
range_from, range_to, display_rr)
Select b.id, gender_code,
			age_from, age_from_type, age_from_days,
			age_to, age_to_type, age_to_days,
			CURRENT_DATE, '', 
			a.range_from, a.range_to, a.display_rr
			from lab_range_reference a, lab_analyte_master b
			Where a.analyte_id = b.analyteid or a.analyte_id = b.analytecode or a.component_id = b.analyteid  or a.component_id = b.analytecode
			And (a.component_id = '--' OR a.component_id = '' OR a.component_id is null)