From c9b6b9a5ca082fe7c1b6f58d7713f785a9eb6a5c Mon Sep 17 00:00:00 2001
From: Martial Simon <msimon_fr@hotmail.com>
Date: Mon, 15 Sep 2025 01:08:27 +0200
Subject: add: graphs et rushs

---
 graphs/sql/outsourcing/req04.sql | 47 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 47 insertions(+)
 create mode 100644 graphs/sql/outsourcing/req04.sql

(limited to 'graphs/sql/outsourcing/req04.sql')

diff --git a/graphs/sql/outsourcing/req04.sql b/graphs/sql/outsourcing/req04.sql
new file mode 100644
index 0000000..badca1a
--- /dev/null
+++ b/graphs/sql/outsourcing/req04.sql
@@ -0,0 +1,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
-- 
cgit v1.2.3

