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
|
SELECT
person_id,
first_name,
last_name,
o.created_at,
serving_time,
o.created_at::timestamp
+ concat(serving_time, ' month')::interval AS release_date
INTO TEMPORARY TABLE a
FROM justice.trials AS t, justice.cases AS c, justice.defendants AS d,
public.people AS p, justice.outcomes AS o,
justice.sentence_reductions AS r
WHERE
t.case_id = c.id
AND t.id = d.trial_id
AND d.person_id = p.id
AND o.trial_id = t.id
AND r.outcome_id != o.id
AND o.outcome = 'GUILTY';
SELECT
person_id,
first_name,
last_name,
o.created_at,
serving_time,
o.created_at + concat(serving_time - r.amount, ' month')::interval AS release_date
INTO TEMPORARY TABLE b
FROM justice.trials AS t, justice.cases AS c, justice.defendants AS d,
public.people AS p, justice.outcomes AS o,
justice.sentence_reductions AS r
WHERE
t.case_id = c.id
AND t.id = d.trial_id
AND d.person_id = p.id
AND o.trial_id = t.id
AND r.outcome_id = o.id
AND o.outcome = 'GUILTY';
INSERT INTO b
SELECT DISTINCT * FROM a
WHERE NOT EXISTS (SELECT * FROM b WHERE a.person_id = b.person_id);
SELECT DISTINCT *
INTO public.release_dates
FROM b
ORDER BY b.release_date DESC, serving_time DESC
|
