|
I'm experiencing issues simpler to this because of the precedence change and parentheses optimization, in my example, parentheses are being stripped.
Focus issue is with the outer parenteses being stripped b/c of optimization:
((
|
((a.left2 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 2000)) - (a_left.left2 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_left.test_datetime,p.birth_date)/365)), 2000)))
|
+ ((a.left3 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 3000)) - (a_left.left3 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_left.test_datetime,p.birth_date)/365)), 3000)))
|
+ ((a.left4 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 4000)) - (a_left.left4 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_left.test_datetime,p.birth_date)/365)), 4000)))
|
) / 3) AS 'left_age_corrected_shift',
|
Example SQL View:
SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';
|
DROP VIEW IF EXISTS audio_shifts_v;
|
CREATE ALGORITHM = MERGE
|
DEFINER='root'@'localhost'
|
SQL SECURITY INVOKER
|
VIEW audio_shifts_v AS
|
SELECT
|
`a`.`audio_id`,
|
`a`.`revision_num`,
|
`a`.`doc_id`,
|
`a`.`left05`,
|
`a`.`left1`,
|
`a`.`left2`,
|
`a`.`left3`,
|
`a`.`left4`,
|
`a`.`left6`,
|
`a`.`left8`,
|
`a`.`right05`,
|
`a`.`right1`,
|
`a`.`right2`,
|
`a`.`right3`,
|
`a`.`right4`,
|
`a`.`right6`,
|
`a`.`right8`,
|
`a`.`manual`,
|
`a`.`hcp`,
|
`a`.`left_sts`,
|
`a`.`right_sts`,
|
`a`.`left_osha_recordable`,
|
`a`.`right_osha_recordable`,
|
`a`.`left_baseline`,
|
`a`.`right_baseline`,
|
`a`.`invalid`,
|
`a`.`test_reason`,
|
`a`.`comment`,
|
`a`.`outside_provider`,
|
`a`.`outside_location`,
|
`a`.`audio_model`,
|
`a`.`audio_serial`,
|
`a`.`calibration_date`,
|
/* The left and right shift are the average shift from the baseline at the 2k,3k,and 4k ranges */
|
((
|
(a.left2 - a_left.left2)
|
+ (a.left3 - a_left.left3)
|
+ (a.left4 - a_left.left4)
|
) / 3) AS 'left_shift',
|
((
|
(a.right2 - a_right.right2)
|
+ (a.right3 - a_right.right3)
|
+ (a.right4 - a_right.right4)
|
) / 3) AS 'right_shift',
|
/* The age corrected shift is the average shift from the baseline at the 2k, 3k, and 4k ranges after the test
|
and baseline have been adjusted for the patient's age, adjustments are found in wc_audio_age_adjustment_value */
|
((
|
((a.left2 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 2000)) - (a_left.left2 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_left.test_datetime,p.birth_date)/365)), 2000)))
|
+ ((a.left3 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 3000)) - (a_left.left3 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_left.test_datetime,p.birth_date)/365)), 3000)))
|
+ ((a.left4 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 4000)) - (a_left.left4 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_left.test_datetime,p.birth_date)/365)), 4000)))
|
) / 3) AS 'left_age_corrected_shift',
|
((
|
((a.right2 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 2000)) - (a_right.right2 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_right.test_datetime,p.birth_date)/365)), 2000)))
|
+ ((a.right3 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 3000)) - (a_right.right3 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_right.test_datetime,p.birth_date)/365)), 3000)))
|
+ ((a.right4 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a.test_datetime,p.birth_date)/365)), 4000)) - (a_right.right4 - wc_audio_age_adjustment_value(p.sex, FLOOR((DATEDIFF(a_right.test_datetime,p.birth_date)/365)), 4000)))
|
) / 3) AS 'right_age_corrected_shift'
|
FROM audio a
|
INNER JOIN documents d ON a.doc_id=d.doc_id
|
INNER JOIN patients p ON d.pat_id=p.pat_id
|
|
/* Using sub selects here instead of the routine wc_audio_get_baseline so that indexes can be used */
|
LEFT JOIN audio a_left ON a_left.audio_id=(SELECT a2.audio_id
|
FROM audio a2
|
INNER JOIN documents d2 ON a2.doc_id=d2.doc_id
|
WHERE d.pat_id=d2.pat_id AND a2.test_datetime < a.test_datetime AND a2.left_baseline='Yes'
|
ORDER BY a2.test_datetime DESC
|
LIMIT 1)
|
LEFT JOIN audio a_right ON a_right.audio_id=(SELECT a3.audio_id
|
FROM audio a3
|
INNER JOIN documents d3 ON a3.doc_id=d3.doc_id
|
WHERE d.pat_id=d3.pat_id AND a3.test_datetime < a.test_datetime AND a3.right_baseline='Yes'
|
ORDER BY a3.test_datetime DESC
|
LIMIT 1)
|
|