Closed cmgiven closed 7 years ago
Haven't yet looked at accuracy, but this is my starting point.
select
t1.*,
EPTEST_ATTORNEY,
EPTEST_MANILA,
EPTEST_FOREIGN,
EPTEST_CLCW,
EPTEST_RADIATION,
EPTEST_MUSTGAS,
EPTEST_INCARCERATION
from
(
select
BFKEY,
-- ** ISSUES ** --
-- Education --
case when count(case when
ISSPROG = '03'
then 1 end) > 0 then 1 else 0 end as EPTEST_EDUCATION,
-- Insurance --
case when count(case when
ISSPROG = '04'
then 1 end) > 0 then 1 else 0 end as EPTEST_INSURANCE,
-- Home Loan Guaranty --
case when count(case when
ISSPROG = '05'
then 1 end) > 0 then 1 else 0 end as EPTEST_GUARANTY,
-- VAMC/Medical --
case when count(case when
ISSPROG = '06'
then 1 end) > 0 then 1 else 0 end as EPTEST_MEDICAL,
-- Pension --
case when count(case when
ISSPROG = '07'
then 1 end) > 0 then 1 else 0 end as EPTEST_PENSION,
-- VR&E --
case when count(case when
ISSPROG = '08'
then 1 end) > 0 then 1 else 0 end as EPTEST_VRE,
-- National Cemetery Administration --
case when count(case when
ISSPROG = '11'
then 1 end) > 0 then 1 else 0 end as EPTEST_NCA,
-- Competency of Payee --
case when count(case when
ISSPROG = '02' and ISSCODE = '06'
then 1 end) > 0 then 1 else 0 end as EPTEST_COMPETENCY,
-- DIC/Death --
case when count(case when
ISSPROG = '02' and ISSCODE = '08'
then 1 end) > 0 then 1 else 0 end as EPTEST_DIC,
-- Attorney Fees --
case when count(case when
ISSPROG = '09' and ISSCODE = '01'
then 1 end) > 0 then 1 else 0 end as EPTEST_ATTORNEYFEES,
-- Spina Bifida --
case when count(case when
ISSPROG = '09' and ISSCODE = '03'
then 1 end) > 0 then 1 else 0 end as EPTEST_SPINABIFIDA,
-- Overpayment --
case when count(case when
(ISSPROG = '02' and ISSCODE = '13' and ISSLEV1 = '02') or
(ISSPROG = '03' and ISSCODE = '05' and ISSLEV1 = '02') or
(ISSPROG = '07' and ISSCODE = '10' and ISSLEV1 = '02')
then 1 end) > 0 then 1 else 0 end as EPTEST_OVERPAYMENT,
-- Accrued --
case when count(case when
(ISSPROG = '02' and ISSCODE = '01' and ISSLEV1 = '01') or
(ISSPROG = '02' and ISSCODE = '07' and ISSLEV1 = '01') or
(ISSPROG = '02' and ISSCODE = '09' and ISSLEV1 = '01') or
(ISSPROG = '02' and ISSCODE = '11' and ISSLEV1 = '01') or
(ISSPROG = '02' and ISSCODE = '12' and ISSLEV1 = '02') or
(ISSPROG = '02' and ISSCODE = '14' and ISSLEV1 = '01') or
(ISSPROG = '02' and ISSCODE = '15' and ISSLEV1 = '01') or
(ISSPROG = '02' and ISSCODE = '17' and ISSLEV1 = '01') or
(ISSPROG = '02' and ISSCODE = '18' and ISSLEV1 = '01' and ISSLEV2 = '01') or
(ISSPROG = '02' and ISSCODE = '20' and ISSLEV1 = '01') or
(ISSPROG = '03' and ISSCODE = '01') or
(ISSPROG = '07' and ISSCODE = '01')
then 1 end) > 0 then 1 else 0 end as EPTEST_ACCRUED,
-- ** REMANDS ** --
-- Due Process: BVA Travel Board/Video Hearing --
case when count(case when
RMDVAL = 'EA'
then 1 end) > 0 then 1 else 0 end as EPTEST_HEARING,
-- Manlincon Compliance --
case when count(case when
RMDVAL = 'ED'
then 1 end) > 0 then 1 else 0 end as EPTEST_MANLINCON,
-- Rice Compliance --
case when count(case when
ISSPROG = '02' and ISSCODE = '17' and RMDVAL = 'AA'
then 1 end) > 0 then 1 else 0 end as EPTEST_RICE
from
BRIEFF
left join ISSUES on BRIEFF.BFKEY = ISSUES.ISSKEY
left join RMDREA on ISSUES.ISSKEY = RMDREA.RMDKEY and ISSUES.ISSSEQ = RMDREA.RMDISSSEQ
group by BFKEY
) t1
join
(
select
BFKEY,
BFDDEC,
-- ** DOCKET ** --
-- Private Attorney --
case when
BFSO in ('T', 'U', 'Z')
then 1 else 0 end as EPTEST_ATTORNEY,
-- Manila --
case when
BFREGOFF = 'RO58'
then 1 else 0 end as EPTEST_MANILA,
-- Foreign Claims --
case when
BFREGOFF = 'RO71'
then 1 else 0 end as EPTEST_FOREIGN,
-- ** SPECIAL INTERESTS ** --
-- Camp Legune Contaminated Water --
case when
TICLCW = 'Y'
then 1 else 0 end as EPTEST_CLCW,
-- Radiation --
case when
TIRADB = 'Y' or TIRADN = 'Y'
then 1 else 0 end as EPTEST_RADIATION,
-- Mustard Gas --
case when
TIMGAS = 'Y'
then 1 else 0 end as EPTEST_MUSTGAS,
-- Incarceration --
case when
SINCAR = 'Y'
then 1 else 0 end as EPTEST_INCARCERATION
from
BRIEFF
left join CORRES on BRIEFF.BFCORKEY = CORRES.STAFKEY
left join FOLDER on BRIEFF.BFKEY = FOLDER.TICKNUM
) t2
on t1.BFKEY = t2.BFKEY
where BFDDEC >= date '2016-08-01'
My accuracy with the above:
- | Predicted: RO | Predicted: AMC |
---|---|---|
Actual: RO | 581 correct (16.5%) | 362 incorrect (10.3%) |
Actual: AMC | 111 incorrect (3.2%) | 2,466 correct (70.1%) |
Updating with two changes:
Test set SQL:
select
BFKEY,
LOCSTTO as LOC
from BRIEFF
join
(
select
LOCKEY as MLOCKEY,
max(LOCDOUT) MLOCDOUT,
count(case when LOCSTTO = '97' then 1 end) MAMCCNT
from PRIORLOC
where LOCSTTO in ('50', '97', '98')
group by LOCKEY
) on MLOCKEY = BFKEY
join PRIORLOC
on LOCKEY = BFKEY and LOCDOUT = MLOCDOUT
where MAMCCNT > 0
and LOCSTTO <> '97'
and BFDDEC >= date '2015-10-01' and BFDDEC < date '2016-10-01'
Accuracy:
- | Predicted: RO | Predicted: AMC |
---|---|---|
Actual: RO | 5,486 correct (20.6%) | 3,326 incorrect (12.5%) |
Actual: AMC | 674 incorrect (2.5%) | 17,145 correct (64.6%) |
Type I errors by test:
EPTEST_EDUCATION EPTEST_INSURANCE EPTEST_GUARANTY EPTEST_MEDICAL EPTEST_PENSION
2 0 0 9 52
EPTEST_VRE EPTEST_NCA EPTEST_COMPETENCY EPTEST_DIC EPTEST_ATTORNEYFEES
1 0 5 9 0
EPTEST_SPINABIFIDA EPTEST_OVERPAYMENT EPTEST_ACCRUED EPTEST_HEARING EPTEST_MANLINCON
1 1 216 63 125
EPTEST_RICE EPTEST_ATTORNEY EPTEST_MANILA EPTEST_FOREIGN EPTEST_CLCW
38 69 8 14 0
EPTEST_RADIATION EPTEST_MUSTGAS EPTEST_INCARCERATION
65 24 0
Type I error rate by test (0 is good, 1 means it's wrong every time):
EPTEST_EDUCATION EPTEST_INSURANCE EPTEST_GUARANTY EPTEST_MEDICAL EPTEST_PENSION
0.014184397 0.000000000 0.000000000 0.642857143 0.118721461
EPTEST_VRE EPTEST_NCA EPTEST_COMPETENCY EPTEST_DIC EPTEST_ATTORNEYFEES
0.027027027 0.000000000 0.208333333 0.016513761 0.000000000
EPTEST_SPINABIFIDA EPTEST_OVERPAYMENT EPTEST_ACCRUED EPTEST_HEARING EPTEST_MANLINCON
0.500000000 0.007751938 0.471615721 0.037837838 0.058740602
EPTEST_RICE EPTEST_ATTORNEY EPTEST_MANILA EPTEST_FOREIGN EPTEST_CLCW
0.093827160 0.098995696 0.049689441 0.215384615 0.000000000
EPTEST_RADIATION EPTEST_MUSTGAS EPTEST_INCARCERATION
0.281385281 0.500000000 0.000000000
Predict whether a case has a LOC 97–98 handoff (EP creation handled by AMC) or a LOC 97–50 handoff (EP creation handled by RO). Use discovery with Nicholas from 10/24 to create a set of rules.
Although it will be possible to report the type I error rate at the individual rule level, the type II error rate is only possible in aggregate, pending follow up into individual cases.