text: use pedi;
  
  INSERT IGNORE INTO query_objects(instance,scope,module,section,target,short_desc,standard,primary_link,secondary_link) values('lava','crms','query','pedi','scq','SCQ',1,1,1);
  
  -- -----------------------------------------------------
  -- view lq_view_scq
  -- -----------------------------------------------------
  DROP VIEW IF EXISTS `lq_view_scq`;
  CREATE OR REPLACE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `lq_view_scq` AS select 
  `instr_id`,
  `scq_date`,
  `scq_name`,
  `scq1`,
  `scq2`,
  `scq3`,
  `scq4`,
  `scq5`,
  `scq6`,
  `scq7`,
  `scq8`,
  `scq9`,
  `scq10`,
  `scq11`,
  `scq12`,
  `scq13`,
  `scq14`,
  `scq15`,
  `scq16`,
  `scq17`,
  `scq18`,
  `scq19`,
  `scq20`,
  `scq21`,
  `scq22`,
  `scq23`,
  `scq24`,
  `scq25`,
  `scq26`,
  `scq27`,
  `scq28`,
  `scq29`,
  `scq30`,
  `scq31`,
  `scq32`,
  `scq33`,
  `scq34`,
  `scq35`,
  `scq36`,
  `scq37`,
  `scq38`,
  `scq39`,
  `scq40`,
  `scq_social`,
  `scq_comm`,
  `scq_patterns`,
  `scq_total`
  FROM `instrumenttracking` `t1` inner join `scq` `t2` on (`t1`.`InstrID` = `t2`.`instr_id`)
  WHERE `t1`.`InstrType` = 'SCQ';
  
  
  -- -----------------------------------------------------
  -- procedure lq_get_pedi_scq
  -- -----------------------------------------------------
  
  DROP PROCEDURE IF EXISTS `lq_get_pedi_scq`;
  DELIMITER $$
  
  CREATE PROCEDURE `lq_get_pedi_scq`(user_name varchar(50), host_name varchar(50), query_type varchar(25), query_subtype VARCHAR(25), query_days INTEGER)
  BEGIN
  CALL lq_audit_event(user_name, host_name, 'app-pedi.pedi.scq', query_type);
  
  IF query_type = 'Simple' THEN
  	SELECT p.PIDN, it.InstrType, it.DCDate, v.VType, it.DCStatus, it.AgeAtDC, i.* FROM instrumenttracking it 
  	    INNER JOIN visit v ON (it.VID = v.VID) 
  		INNER JOIN scq i ON (it.InstrID = i.instr_id) 
  		INNER JOIN temp_pidn p ON (p.PIDN = it.PIDN) 
  	WHERE it.InstrType = 'SCQ' or it.InstrType is null 
  	ORDER BY p.pidn, it.DCDate;
        
  ELSEIF query_type = 'SimpleAllPatients' THEN
  	SELECT p.PIDN, it.InstrType, it.DCDate, v.VType, it.DCStatus, it.AgeAtDC, i.* FROM instrumenttracking it  
  	    INNER JOIN visit v ON (it.VID = v.VID) 
  		INNER JOIN scq i ON (it.InstrID = i.instr_id)  
  		RIGHT OUTER JOIN temp_pidn p ON (p.PIDN = it.PIDN)  
  	WHERE it.InstrType =  'SCQ' or it.InstrType is null 
  	ORDER BY P.pidn, it.DCDate;
  	
  ELSEIF query_type = 'VisitGrouping' THEN
      SELECT l.PIDN, l.link_date, l.link_id, v.VType, i.InstrType, i.DCDate, i.DCStatus, i.AgeAtDC, d.*
      FROM temp_linkdata l LEFT OUTER JOIN (visit v, instrumenttracking i, scq d)
        ON (l.link_id=v.vid AND v.vid=i.vid AND i.InstrID=d.instr_id AND
            NOT i.DCStatus = 'Scheduled' AND NOT i.DCStatus like 'Canceled%')
      ORDER BY l.PIDN, l.link_date, l.link_id;
  	
  ELSEIF query_type = 'PrimaryAll' THEN 
  	CREATE TEMPORARY TABLE temp_linkdata as 
  		SELECT p.PIDN,i.DCDate as link_date, i.InstrID as link_id, i.InstrType, i.DCDate, i.DCStatus, i.AgeAtDC, d.*  
  		FROM temp_pidn p INNER JOIN instrumenttracking i ON (p.PIDN=i.PIDN)  
  			INNER JOIN scq d ON (i.InstrID=d.instr_id) 
  		WHERE NOT i.DCStatus = 'Scheduled' AND NOT i.DCStatus like 'Canceled%' 
  		ORDER BY p.pidn, i.DCDate, i.InstrID ;
  	ALTER TABLE temp_linkdata ADD INDEX(pidn,link_date,link_id);	
  	SELECT * from temp_linkdata;
  	
  ELSEIF query_type = 'PrimaryLatest' THEN  
  	CREATE TEMPORARY TABLE temp_linkdata as 
  		SELECT p.PIDN, i.DCDate as link_date, i.InstrID as link_id, i.InstrType, i.DCDate, i.DCStatus, i.AgeAtDC, d.*  
  		FROM temp_pidn p INNER JOIN instrumenttracking i ON (p.PIDN=i.PIDN)  
  			INNER JOIN scq d ON (i.InstrID=d.instr_id) 
  		WHERE i.DCDate = (SELECT MAX(i2.DCDate) from instrumenttracking i2 WHERE i2.PIDN=p.PIDN AND  
  			i2.InstrType = 'SCQ' AND NOT i2.DCStatus = 'Scheduled' AND NOT i2.DCStatus like 'Canceled%') 
  		ORDER BY p.pidn, i.DCDate, i.InstrID;
  	ALTER TABLE temp_linkdata ADD INDEX(pidn,link_date,link_id);	
  	SELECT * from temp_linkdata;
  	
  ELSEIF query_type = 'PrimaryFirst' THEN
  	CREATE TEMPORARY TABLE temp_linkdata as 
  		SELECT p.PIDN,i.DCDate as link_date, i.InstrID as link_id, i.InstrType, i.DCDate, i.DCStatus, i.AgeAtDC, d.* 
  		FROM temp_pidn p INNER JOIN instrumenttracking i ON (p.PIDN=i.PIDN) 
  			INNER JOIN scq d ON (i.InstrID=d.instr_id)
  		WHERE i.DCDate = (SELECT MIN(i2.DCDate) from instrumenttracking i2 WHERE i2.PIDN=p.PIDN AND 
  			i2.InstrType = 'SCQ' AND NOT i2.DCStatus = 'Scheduled' AND NOT i2.DCStatus like 'Canceled%')
  		ORDER BY p.pidn, i.InstrID;
  	ALTER TABLE temp_linkdata ADD INDEX(pidn,link_date,link_id);	
  	SELECT * from temp_linkdata;
  	
  ELSEIF query_type IN ('SecondaryAll','SecondaryClosest') THEN
  	#Create candidate table with secondary instruments 
  	CREATE TEMPORARY TABLE temp_secondary_candidates AS
  		SELECT l.PIDN, l.link_date, l.link_id, i2.InstrType, i2.InstrID, DATEDIFF(l.link_date, i2.DCDate) AS Days 
  		FROM temp_linkdata l INNER JOIN instrumenttracking i2 ON (i2.PIDN=l.PIDN) 
  		WHERE i2.InstrType = 'SCQ';
  	ALTER TABLE temp_secondary_candidates ADD INDEX(pidn,link_date,link_id,Days);
  	
  	#get rid of earlier or later instruments as necessary
  	IF query_subtype = 'Earlier' THEN DELETE from temp_secondary_candidates WHERE Days >0;
  	ELSEIF query_subtype = 'MoreRecent' THEN DELETE from temp_secondary_candidates WHERE Days <0;
  	END IF;
  	
  	#limit records to specified day range      
  	DELETE FROM temp_secondary_candidates WHERE abs(Days) > query_days;
  
  	#only keep closest if appropriate
  	IF query_type = 'SecondaryClosest' THEN
  		CREATE TEMPORARY TABLE temp_secondary_closest AS
  			SELECT pidn,link_date,link_id,MIN(ABS(Days)) as min_days 
  			FROM temp_secondary_candidates
  			GROUP BY pidn,link_date,link_id;
  		ALTER TABLE temp_secondary_closest ADD INDEX (pidn,link_date,link_id);
  		DELETE FROM temp_secondary_candidates
  			WHERE ABS(days) <> 
  				(SELECT min_days 
  				FROM temp_secondary_closest s2 
  				WHERE (s2.pidn = temp_secondary_candidates.pidn and s2.link_date=temp_secondary_candidates.link_date and s2.link_id=temp_secondary_candidates.link_id));
  		DROP TABLE temp_secondary_closest;
  	END IF;
  
  	SELECT l.PIDN, l.link_date, l.link_id, v.VType, i.InstrType, i.DCDate, temp_secondary_candidates.days as DayDiff, i.DCStatus, i.AgeAtDC, d.* 
  	FROM temp_linkdata l
  		LEFT OUTER JOIN temp_secondary_candidates ON (l.pidn=temp_secondary_candidates.pidn and l.link_date = temp_secondary_candidates.link_date and l.link_id=temp_secondary_candidates.link_id) 
  		LEFT JOIN instrumenttracking i ON (temp_secondary_candidates.InstrID=i.InstrID)
  		LEFT JOIN visit v ON (i.VID = v.VID)
  		LEFT JOIN scq d ON (i.InstrID=d.instr_id) ORDER BY l.pidn, l.link_date, l.link_id;
  
  	DROP TABLE temp_secondary_candidates;
  
  END IF;
  
  END$$
  
  $$
  DELIMITER ;
  
  
  