Overview
For each release, IHTSDO requests statistics regarding specialist performance. This is a breakdown of the request and the queries involved to answer the question.
Preparation
ICD10 project has id 7.
Determine the date range for the release
select distinct id,refsetid,editingCycleBeginDate,
unix_timestamp(editingCycleBeginDate), name from map_projects_AUD
where id in (7);
+----+-----------+-----------------------+---------------------------------------+------------------+
| id | refsetid | editingCycleBeginDate | unix_timestamp(editingCycleBeginDate) | name |
+----+-----------+-----------------------+---------------------------------------+------------------+
| 7 | 447562003 | 2015-01-07 00:00:00 | 1420588800 | SNOMED to ICD10 |
| 7 | 447562003 | 2015-06-24 17:19:28 | 1435166368 | SNOMED to ICD10 |
| 7 | 447562003 | 2015-11-28 17:17:50 | 1448731070 | SNOMED to ICD10 |
+----+-----------+-----------------------+---------------------------------------+------------------+
2016-01-31 Editing Cycle was:
- start = 2015-06-24 (1435166368000)
- end = 2015-11-28 (1448731070000)
Overall group discrepancy rate
NOTE: the project id and dates are interpolated from the "preparation" data computed above.
a. Concepts edited through normal workflow
drop table tbac;
create table tbac as
select distinct mr.conceptId
from map_records_AUD mr
where mr.mapProjectId = 7
and mr.lastModified >= 1435166368000
and mr.lastModified < 1448731070000
and workflowStatus in ('NEW')
and mr.revType != 2;
b. Concepts edited through QA workflow
drop table tbac2;
create table tbac2 as
select distinct mr.conceptId
from map_records_AUD mr
where mr.mapProjectId = 7
and mr.lastModified >= 1435166368000
and mr.lastModified < 1448731070000
and workflowStatus = 'QA_NEW'
and mr.revType != 2;
c. Concepts edited through "fix error" workflow
drop table tbac3;
create table tbac3 as
select distinct mr.conceptId
from map_records_AUD mr
where mr.mapProjectId = 7
and mr.lastModified >= 1435166368000
and mr.lastModified < 1448731070000
and workflowStatus = 'REVIEW_NEW'
and mr.revType != 2;
d. CONFLICT concepts
drop table tbac4;
create table tbac4 as
select distinct mr.conceptId
from map_records_AUD mr
where mr.mapProjectId = 7
and mr.lastModified >= 1435166368000
and mr.lastModified < 1448731070000
and workflowStatus like '%CONFLICT%'
and mr.revType != 2;
e. Feedback "error" concepts
drop table tbac5;
create table tbac5 as
select distinct mr.conceptId
from map_records_AUD mr, feedback_conversations fc, feedbacks f
where mr.mapProjectId = 7
and fc.mapProjectId = 7
and isError = 1
and f.feedbackConversation_id = fc.id
and fc.mapRecordId = mr.id
and mr.lastModified >= 1435166368000
and f.timestamp >= from_unixtime(1435166368000/1000)
and mr.lastModified < 1448731070000
and f.timestamp < from_unixtime(1448731070000/1000)
and mr.revtype != 2;
Now, there are various ways to look at overall "discrepancy" rate because there are 3 workflows.
"Normal editing" with a conflict
select b.ct, a.ct, b.ct*100/a.ct
from
(select count(*) ct from tbac) a,
(select count(*) ct from tbac a, tbac4 b where a.conceptId = b.conceptId) b;
"Normal editing" with error feedback
select b.ct, a.ct, b.ct*100/a.ct
from
(select count(*) ct from tbac) a,
(select count(*) ct from tbac a, tbac5 b where a.conceptId = b.conceptId) b;
"QA editing" with feeback
select b.ct, a.ct, b.ct*100/a.ct
from
(select count(*) ct from tbac2) a,
(select count(*) ct from tbac2 a, tbac5 b where a.conceptId = b.conceptId) b;
ICD10: | 69 | 4102 | 1.6821 |
ICD9CM: | 16 | 216 | 7.4074 |
"Fix error editing" with feedback
select b.ct, a.ct, b.ct*100/a.ct
from
(select count(*) ct from tbac3) a,
(select count(*) ct from tbac3 a, tbac5 b where a.conceptId = b.conceptId) b;
Concordance rate between two mapping specialists.
For example K and L both mapped 200 of the same concepts. 150 had no discrepancies.
Resulting in a 75% concordance rate. Need this done for each Map Specialist pair,
e.g., Sarah and Nicki, Genevieve and Nicki, etc. for only those maps in the
editing cycle ending 31 December 2015.
ONLY applies to ICD10 - project 7
Concepts edited through normal workflow by specialist
drop table tbac;
create table tbac as
select distinct mr.conceptId, userName
from map_records_AUD mr, map_users b
where owner_id = b.id
AND mr.mapProjectId = 7
and mr.lastModified >= 1435166368000
and mr.lastModified < 1448731070000
and workflowStatus in ('NEW')
and mr.revType != 2;
CONFLICT concepts
drop table tbac2;
create table tbac2 as
select distinct mr.conceptId
from map_records_AUD mr
where mr.mapProjectId = 7
and mr.lastModified >= 1435166368000
and mr.lastModified < 1448731070000
and workflowStatus like '%CONFLICT%'
and mr.revType != 2;
Concordance rate (assumes all pairs have at least ONE in common)
select a.u1, a.u2, b.ct, a.ct, b.ct*100/a.ct
from
(select count(*) ct, a.userName u1, b.userName u2
from tbac a, tbac b
where a.conceptId = b.conceptId
and a.userName < b.userName
group by a.userName, b.userName) a,
(select count(*) ct, a.userName u1, b.userName u2
from tbac a, tbac b
where a.conceptId = b.conceptId
and a.userName < b.userName
and a.conceptId not in (select conceptId from tbac2)
group by a.userName, b.userName) b
where concat(a.u1,a.u2) = concat(b.u1,b.u2);
Group discrepancy rate for "Target code selection for a map record is in error."
NOTE: this is now also an officially supported map report for the ICD10 project in the tool.
For example, out of the 475 concepts with discrepancies 190 were resolved and
noted by the map lead as a target code selection error for any specialist.
This would calculate to 40% of the the concepts which were tagged as discrepancies
were resolved as an error in target code selection.
ONLY applies to ICD10
drop table tbac;
create table tbac as
select distinct mr.conceptId
from map_records_AUD mr
where mr.mapProjectId = 7
and mr.lastModified >= 1435166368000
and mr.lastModified < 1448731070000
and workflowStatus like '%CONFLICT%'
and mr.revType != 2;
drop table tbac2;
create table tbac2 as
select distinct mr.conceptId
from map_records_AUD mr, feedback_conversations fc, feedbacks f
where f.mapError = 'Target code selection for a map record is in error'
and mr.mapProjectId = 7
and fc.mapProjectId = 7
and isError = 1
and f.feedbackConversation_id = fc.id
and fc.mapRecordId = mr.id
and mr.lastModified >= 1435166368000
and f.timestamp >= from_unixtime(1435166368000/1000)
and mr.lastModified < 1448731070000
and f.timestamp < from_unixtime(1448731070000/1000)
and mr.revtype != 2;
Group discrepancy rate for "Target code selection for a map record is in error."
select b.ct, a.ct, b.ct*100/a.ct
from
(select count(*) ct from tbac) a,
(select count(*) ct from tbac a, tbac2 b where a.conceptId = b.conceptId) b;
.