SQL_expungment/criminal_district_SQL_JRA at master · MVLSLAW/SQL_expungment · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
UPDATE test_exp
INNER JOIN (
SELECT id,
(CASE
WHEN end_date < date_sub(Now(), INTERVAL wait_years YEAR)
THEN "JRA-Maybe"
ELSE "JRA-Not Yet" END) as expungability,
date_add(end_date, INTERVAL wait_years YEAR) as estimate_date,
wait_years
FROM(

SELECT inside.id,
    CaseNumber,
	ChargeNo,
	GREATEST(
        (CASE WHEN (JailTermDays is not NULL or JailTermMos is not NULL or JailTermYrs is not NULL)
        THEN (date_add(date_add(date_add(DispositionDate, INTERVAL JailTermDays DAY), INTERVAL JailTermMos MONTH), INTERVAL JailTermYrs YEAR))
        ELSE 0 END),(CASE WHEN PBJEndDate IS NOT NULL THEN PBJEndDate ELSE 0 END), (CASE WHEN ProbationEndDate IS NOT NULL THEN ProbationEndDate ELSE 0 END),DispositionDate) as end_date,
    (CASE WHEN Statute LIKE 'CR.3.203%' THEN 15 ELSE 10 END) as wait_years
	FROM (
		SELECT test_exp.ID,
        test_exp.CaseNumber,
        test_exp.ChargeNo,
        LEFT(CJISCode,1) as CJIS_init,
        MID(CJISCode,2,1) as CJIS_inchoate,
        RIGHT(CJISCode,4) as CJIS2,
        DispositionDate,
        JailTermDays,
        JailTermMos,
        JailTermYrs,
        PBJEndDate,
        ProbationEndDate,
        Statute,
        Description
		FROM charge_and_disposition_information
		INNER JOIN test_exp ON 							test_exp.Casenumber=charge_and_disposition_information.CaseNumber AND test_exp.ChargeNo=charge_and_disposition_information.ChargeNo
		WHERE Disposition='GUILTY' AND test_exp.Expungability='No') as inside
	WHERE
    (CJIS_init=1 AND inside.CJIS2 in ("0257","0025","0043","0114","0215","0217","0220","0247",
                                        "0248","0249","0250","0251","0252","0255","0256","0259",
                                        "0260","0261","0262","0291","0330","0331","0332","0336",
                                        "0419","0566","0567","0568","0569","0570","0571","0573",
                                        "0640","0641","0643","0644","0645","0646","0651","0691",
                                        "0972","1093","1415","1416","1474","1475","1635","2405",
                                        "2474","2602","2608","3901","3550","3804","3903","3908",
                                        "5099","5100","5599","6503"))
    OR (CJIS_init=2 AND inside.CJIS2 in ("0050","0055","0060","0065","0254","0354","2003","0105",
                                        "2210","2411","2800","2820","3080","3090","3908","3921",
                                        "3925","0810","0254","0354"))
    OR (CJIS_init=3 AND inside.CJIS2 in ("0120","0125","0126","0130","0135","0140","0150","0155",
                                        "0160","0180","0185","1314","0165","2602","2607","3921",
                                        "3942","3946","3947"))
    OR (CJIS_init=4 AND inside.CJIS2=3550)
    OR (CJIS_init=5 AND inside.CJIS2 in ("3550","3555"))
    OR (CJIS_init=6 AND inside.CJIS2 in("2600","3555"))
    OR (CJIS_init=7 AND inside.CJIS2 in("2607","3550","5501"))
    OR (CJIS_init=9 AND inside.CJIS2 in ("0082"))
    OR Description LIKE "%public intox%"
    OR Description LIKE "%deceptive%"
    OR Statute LIKE "PS.5.307%"
    OR Statute LIKE "PS.5.308%"
    OR Statute LIKE "PS.6.602%"
    OR Statute LIKE "PS.7.402%"
    OR Statute LIKE "PS.14.114%"
    OR Statute LIKE "CR.8.103%"
    OR Statute LIKE "CR.8.206%"
    OR Statute LIKE "CR.7.104%")
    as inside2)
    as inside3
    ON test_exp.id=inside3.id

SET test_exp.Expungability=inside3.Expungability

WHERE test_exp.Expungability='No'