Change Details

See {T228575}. Next round, basically. However, the initial SQL query must also exclude tasks which have seen assignee changes in the last two years (that's not the case in T228575), so that should rather be: `SELECT u.userName, ue.address, t.id FROM phabricator_maniphest.maniphest_task t JOIN phabricator_user.user u JOIN phabricator_user.user_email ue JOIN phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified < (UNIX_TIMESTAMP() - 63072000)) AND u.phid = SUBSTR(ta.newValue, INSTR(ta.newValue, 'PHID-USER-'), 30) AND ta.objectPHID = t.phid AND t.ownerPHID = u.phid AND (t.status = "open") AND ue.userPHID = u.phid AND ue.isPrimary = 1 AND t.phid NOT IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND e.dst = "PHID-PROJ-onnxucoedheq3jevknyr") AND t.phid NOT IN (SELECT ta.objectPHID FROM phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified > (UNIX_TIMESTAMP() - 63072000))) ORDER BY u.userName, ta.dateModified;` To make the output of that query more complete, it would also be great (though time-intense) **beforehand** to check those tasks which had their current assignee set more than two years ago and do have a #Patch-for-Review tag, and did not have a reassignment in the last two years again, because some tasks should be changed from #Patch-for-Review to #Patch-Needs-Improvement (e.g. when rebasing needed): `SELECT u.userName, ue.address, CONCAT("https://phabricator.wikimedia.org/T",t.id) FROM phabricator_maniphest.maniphest_task t JOIN phabricator_user.user u JOIN phabricator_user.user_email ue JOIN phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified < (UNIX_TIMESTAMP() - 63072000)) AND u.phid = SUBSTR(ta.newValue, INSTR(ta.newValue, 'PHID-USER-'), 30) AND ta.objectPHID = t.phid AND t.ownerPHID = u.phid AND (t.status = "open") AND ue.userPHID = u.phid AND ue.isPrimary = 1 AND t.phid IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND e.dst = "PHID-PROJ-onnxucoedheq3jevknyr") AND t.phid NOT IN (SELECT ta.objectPHID FROM phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified > (UNIX_TIMESTAMP() - 63072000))) ORDER BY u.userName, ta.dateModified;`

See {T228575}. Next round, basically. However, the initial SQL query must also exclude tasks which have seen assignee changes in the last two years (that's not the case in T228575), plus should exclude tasks with #periodic-update, so that should rather be: `SELECT u.userName, ue.address, t.id FROM phabricator_maniphest.maniphest_task t JOIN phabricator_user.user u JOIN phabricator_user.user_email ue JOIN phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified < (UNIX_TIMESTAMP() - 63072000)) AND u.phid = SUBSTR(ta.newValue, INSTR(ta.newValue, 'PHID-USER-'), 30) AND ta.objectPHID = t.phid AND t.ownerPHID = u.phid AND (t.status = "open") AND ue.userPHID = u.phid AND ue.isPrimary = 1 AND t.phid NOT IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND (e.dst = "PHID-PROJ-onnxucoedheq3jevknyr" OR e.dst = "PHID-PROJ-hwr4habt5dzuabb5w2xz")) AND t.phid NOT IN (SELECT ta.objectPHID FROM phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified > (UNIX_TIMESTAMP() - 63072000))) ORDER BY u.userName, ta.dateModified;` To make the output of that query more complete, it would also be great (though time-intense) **beforehand** to check those tasks which had their current assignee set more than two years ago and do have a #Patch-for-Review tag, and did not have a reassignment in the last two years again, because some tasks should be changed from #Patch-for-Review to #Patch-Needs-Improvement (e.g. when rebasing needed): `SELECT u.userName, ue.address, CONCAT("https://phabricator.wikimedia.org/T",t.id) FROM phabricator_maniphest.maniphest_task t JOIN phabricator_user.user u JOIN phabricator_user.user_email ue JOIN phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified < (UNIX_TIMESTAMP() - 63072000)) AND u.phid = SUBSTR(ta.newValue, INSTR(ta.newValue, 'PHID-USER-'), 30) AND ta.objectPHID = t.phid AND t.ownerPHID = u.phid AND (t.status = "open") AND ue.userPHID = u.phid AND ue.isPrimary = 1 AND t.phid IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND e.dst = "PHID-PROJ-onnxucoedheq3jevknyr") AND t.phid NOT IN (SELECT ta.objectPHID FROM phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified > (UNIX_TIMESTAMP() - 63072000))) ORDER BY u.userName, ta.dateModified;`

See {T228575}. Next round, basically. However, the initial SQL query must also exclude tasks which have seen assignee changes in the last two years (that's not the case in T228575), plus should exclude tasks with #periodic-update, so that should rather be: `SELECT u.userName, ue.address, t.id FROM phabricator_maniphest.maniphest_task t JOIN phabricator_user.user u JOIN phabricator_user.user_email ue JOIN phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified < (UNIX_TIMESTAMP() - 63072000)) AND u.phid = SUBSTR(ta.newValue, INSTR(ta.newValue, 'PHID-USER-'), 30) AND ta.objectPHID = t.phid AND t.ownerPHID = u.phid AND (t.status = "open") AND ue.userPHID = u.phid AND ue.isPrimary = 1 AND t.phid NOT IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND (e.dst = "PHID-PROJ-onnxucoedheq3jevknyr"r" OR e.dst = "PHID-PROJ-hwr4habt5dzuabb5w2xz")) AND t.phid NOT IN (SELECT ta.objectPHID FROM phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified > (UNIX_TIMESTAMP() - 63072000))) ORDER BY u.userName, ta.dateModified;` To make the output of that query more complete, it would also be great (though time-intense) **beforehand** to check those tasks which had their current assignee set more than two years ago and do have a #Patch-for-Review tag, and did not have a reassignment in the last two years again, because some tasks should be changed from #Patch-for-Review to #Patch-Needs-Improvement (e.g. when rebasing needed): `SELECT u.userName, ue.address, CONCAT("https://phabricator.wikimedia.org/T",t.id) FROM phabricator_maniphest.maniphest_task t JOIN phabricator_user.user u JOIN phabricator_user.user_email ue JOIN phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified < (UNIX_TIMESTAMP() - 63072000)) AND u.phid = SUBSTR(ta.newValue, INSTR(ta.newValue, 'PHID-USER-'), 30) AND ta.objectPHID = t.phid AND t.ownerPHID = u.phid AND (t.status = "open") AND ue.userPHID = u.phid AND ue.isPrimary = 1 AND t.phid IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND e.dst = "PHID-PROJ-onnxucoedheq3jevknyr") AND t.phid NOT IN (SELECT ta.objectPHID FROM phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified > (UNIX_TIMESTAMP() - 63072000))) ORDER BY u.userName, ta.dateModified;`