-
Notifications
You must be signed in to change notification settings - Fork 0
/
HistoMonoMandatPartielAnnee.sql
120 lines (118 loc) · 5.48 KB
/
HistoMonoMandatPartielAnnee.sql
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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
CREATE OR REPLACE FUNCTION "BREF"."HistoMonoMandatPartielAnnee"(
)
RETURNS TABLE(idindividu character varying, typemandat character varying, dureemandat integer, date_debut_mandat date, date_fin_mandat date)
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
declare
rec record;
duree int;
BEGIN
for rec in
select "IdIndividu", "BREF"."TypeMandat"."IdTypeMandat", "BREF"."TypeMandat"."TypeMandat", "IdMandat", "DateDebutMandat", "DateFinMandat", "TypeMandat"
from
(
select "IdIndividu", count("IdMandat") from "BREF"."Mandat"
join "BREF"."Individu" on "BREF"."Individu"."IdIndividu" = "BREF"."Mandat"."Elu_IdIndividu"
group by "IdIndividu"
having count("IdMandat") = 1 --593170 individus ne font qu'un seul mandat (des types spécifiés)
) A
join "BREF"."Mandat" on A."IdIndividu" = "BREF"."Mandat"."Elu_IdIndividu"
join "BREF"."TypeMandat" on "BREF"."TypeMandat"."IdTypeMandat" = "BREF"."Mandat"."TypeDuMandat_IdTypeMandat"
where "IdTypeMandat" in (2, 4, 5, 6, 8, 9)
order by "DateDebutMandat" asc
loop
duree = date_part('year', rec."DateFinMandat") - date_part('year', rec."DateDebutMandat");
case rec."IdTypeMandat"
when 2 then --"CONSEILLER DEPARTEMENTAL"
if (((date_part('year', rec."DateDebutMandat")>= 1958 and date_part('year', rec."DateDebutMandat") < 1985)
or (date_part('year', rec."DateDebutMandat")>= 1992 and date_part('year', rec."DateDebutMandat") < 2001))
and duree < 6)
or (((date_part('year', rec."DateDebutMandat")>= 1985 and date_part('year', rec."DateDebutMandat") < 1992)
or (date_part('year', rec."DateDebutMandat")>= 2001 and date_part('year', rec."DateDebutMandat") < 2008)
or (date_part('year', rec."DateDebutMandat")>= 2004 and date_part('year', rec."DateDebutMandat") < 2011)
or (date_part('year', rec."DateDebutMandat")>= 2008))
and duree < 7)
then
idindividu := rec."IdIndividu";
typemandat := rec."TypeMandat";
dureemandat := duree;
date_debut_mandat := rec."DateDebutMandat";
date_fin_mandat := rec."DateFinMandat";
return next;
end if;
when 4 then --"CONSEILLER MUNICIPAL"
if duree < 6 then
idindividu := rec."IdIndividu";
typemandat := rec."TypeMandat";
dureemandat := duree;
date_debut_mandat := rec."DateDebutMandat";
date_fin_mandat := rec."DateFinMandat";
return next;
end if;
when 5 then --"CONSEILLER REGIONAL"
if (date_part('year', rec."DateDebutMandat")>= 1958 and duree < 6)
or (date_part('year', rec."DateDebutMandat")>= 2010 and date_part('year', rec."DateDebutMandat") < 2015 and duree < 5)
then
idindividu := rec."IdIndividu";
typemandat := rec."TypeMandat";
dureemandat := duree;
date_debut_mandat := rec."DateDebutMandat";
date_fin_mandat := rec."DateFinMandat";
return next;
end if;
when 6 then --"DEPUTE"
if (((date_part('year', rec."DateDebutMandat")>= 1962 and date_part('year', rec."DateDebutMandat") < 1967)
or (date_part('year', rec."DateDebutMandat")>= 1968 and date_part('year', rec."DateDebutMandat") < 1978)
or (date_part('year', rec."DateDebutMandat")>= 1981 and date_part('year', rec."DateDebutMandat") < 1986)
or (date_part('year', rec."DateDebutMandat")>= 1988 and date_part('year', rec."DateDebutMandat") < 1993)
or (date_part('year', rec."DateDebutMandat")>= 1997))
and duree < 5)
or (date_part('year', rec."DateDebutMandat")>= 1958 and date_part('year', rec."DateDebutMandat") < 1962 and duree < 4)
or (date_part('year', rec."DateDebutMandat")>= 1967 and date_part('year', rec."DateDebutMandat") < 1968 and duree < 1)
or (date_part('year', rec."DateDebutMandat")>= 1978 and date_part('year', rec."DateDebutMandat") < 1981 and duree < 3)
or (date_part('year', rec."DateDebutMandat")>= 1986 and date_part('year', rec."DateDebutMandat") < 1988 and duree < 2)
or (date_part('year', rec."DateDebutMandat")>= 1993 and date_part('year', rec."DateDebutMandat") < 1997 and duree < 4)
then
idindividu := rec."IdIndividu";
typemandat := rec."TypeMandat";
dureemandat := duree;
date_debut_mandat := rec."DateDebutMandat";
date_fin_mandat := rec."DateFinMandat";
return next;
end if;
when 8 then --"REPRESENTANT AU PARLEMENT EUROPEEN"
if duree < 5 then
idindividu := rec."IdIndividu";
typemandat := rec."TypeMandat";
dureemandat := duree;
date_debut_mandat := rec."DateDebutMandat";
date_fin_mandat := rec."DateFinMandat";
return next;
end if;
when 9 then --"SENATEUR"
if (((date_part('year', rec."DateDebutMandat")>= 1958 and date_part('year', rec."DateDebutMandat") < 1998)
or (date_part('year', rec."DateDebutMandat")>= 2008 and date_part('year', rec."DateDebutMandat") < 2017))
and duree < 9)
or (((date_part('year', rec."DateDebutMandat")>= 2011 and date_part('year', rec."DateDebutMandat") < 2017)
or (date_part('year', rec."DateDebutMandat")>= 2014))
and duree < 6)
or (((date_part('year', rec."DateDebutMandat")>= 1998 and date_part('year', rec."DateDebutMandat") < 2008)
or (date_part('year', rec."DateDebutMandat")>= 2004 and date_part('year', rec."DateDebutMandat") < 2014)
or (date_part('year', rec."DateDebutMandat")>= 2001 and date_part('year', rec."DateDebutMandat") < 2011))
and duree < 10)
then
idindividu := rec."IdIndividu";
typemandat := rec."TypeMandat";
dureemandat := duree;
date_debut_mandat := rec."DateDebutMandat";
date_fin_mandat := rec."DateFinMandat";
return next;
end if;
else
end case;
end loop;
END;
$BODY$;