Worked on this query for a few hours and thought I should share this. Outputs all messages not in department "Staff" and, assuming Department carries grade numbers, compares the differences between the grades.
DROP FUNCTION IF EXISTS STRIP_NON_DIGIT;
DELIMITER $$
CREATE FUNCTION STRIP_NON_DIGIT(input VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
DECLARE output VARCHAR(255) DEFAULT '';
DECLARE iterator INT DEFAULT 1;
WHILE iterator < (LENGTH(input) + 1) DO
IF SUBSTRING(input, iterator, 1) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN
SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
END IF;
SET iterator = iterator + 1;
END WHILE;
RETURN output;
END
$$
SELECT IF(STRIP_NON_DIGIT(f.department) != STRIP_NON_DIGIT(t.department),STRIP_NON_DIGIT(f.department) - STRIP_NON_DIGIT(t.department),'') SndrAgeDiff, m.timecreated TimeSent,IF(timeuserfromdeleted NOT LIKE '0','Deleted','') SenderDeleted, m.timeread TimeRead, IF(timeusertodeleted NOT LIKE '0','Deleted','') RecipientDeleted, CONCAT(f.firstname,' ', f.lastname,' (', f.department, ') > ', t.firstname,' ', t.lastname,' (', t.department,')') AS FromTo, left(m.fullmessage,length(m.fullmessage)-206) Message, f.firstname FromFirstName, f.lastname FromLastName, STRIP_NON_DIGIT(f.department) FromGrade, t.firstname ToFirstName, t.lastname ToLastName,
STRIP_NON_DIGIT(t.department) ToGrade
FROM (SELECT from_unixtime(timecreated) timecreated, useridfrom, useridto, smallmessage, fullmessage, timeusertodeleted, timeuserfromdeleted, 'Unread' AS timeread from mdl_message UNION SELECT from_unixtime(timecreated) timecreated, useridfrom, useridto, smallmessage, fullmessage, timeusertodeleted, timeuserfromdeleted, from_unixtime(timeread) AS timeread from mdl_message_read) m
JOIN mdl_user f ON f.id=m.useridfrom
JOIN mdl_user t ON t.id=m.useridto
WHERE f.department NOT LIKE 'Staff' AND t.department NOT LIKE 'Staff' AND f.department NOT LIKE '' AND t.department NOT LIKE '' AND m.fullmessage NOT LIKE '%This is a copy of a message posted%'
ORDER BY m.timecreated
;
