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
|
CREATE TABLE t_interventions (
num_dossier number(19,0),
date_int DATE,
code_presence number(1,0)
);
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(1, to_date('01-01-2018 10:00','dd-mm-yyyy hh24:mi'), 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(2, to_date('01-01-2018 10:00','dd-mm-yyyy hh24:mi'), 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(3, to_date('01-01-2018 10:00','dd-mm-yyyy hh24:mi'), 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(1, to_date('02-01-2018 10:00','dd-mm-yyyy hh24:mi'), 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(2, to_date('02-01-2018 10:00','dd-mm-yyyy hh24:mi'), 1) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(1, to_date('03-01-2018 10:00','dd-mm-yyyy hh24:mi'), 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(1, to_date('04-01-2018 10:00','dd-mm-yyyy hh24:mi'), 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(1, to_date('05-01-2018 10:00','dd-mm-yyyy hh24:mi'), 1) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(1, to_date('06-01-2018 10:00','dd-mm-yyyy hh24:mi'), 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(1, to_date('07-01-2018 10:00','dd-mm-yyyy hh24:mi'), 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(1, to_date('07-01-2018 12:00','dd-mm-yyyy hh24:mi'), 0) ;
WITH indexed_inter AS (
SELECT row_number() over(PARTITION BY num_dossier ORDER BY date_int) AS rn, num_dossier, date_int, code_presence
FROM t_interventions
),
counting_inter AS (
SELECT num_dossier, rn, counter, group_dossier, code_presence
FROM indexed_inter
model
dimension by(num_dossier, rn)
measures(0 AS counter, code_presence, 0 AS group_dossier)
rules(
counter[ANY,ANY] = CASE WHEN code_presence[cv(),cv()] = code_presence[cv(),cv()-1] THEN counter[cv(), cv()-1] + 1 ELSE 0 END,
group_dossier[ANY,ANY] = presentnnv(code_presence[cv(),cv()-1] ,CASE WHEN code_presence[cv(),cv()] = code_presence[cv(),cv()-1] THEN group_dossier[cv(),cv()-1] ELSE group_dossier[cv(),cv()-1]+1 END, 0)
)
),
absences_per_dossier as (
SELECT num_dossier, group_dossier, max(counter) +1 AS nabsences FROM counting_inter
WHERE code_presence = 0
GROUP BY num_dossier, group_dossier
),
start_of_absences AS (
SELECT cint.num_dossier, cint.group_dossier, min(inter.date_int) AS start_date FROM counting_inter cint
JOIN indexed_inter inter ON inter.num_dossier = cint.num_dossier AND inter.rn = cint.rn
WHERE cint.code_presence = 0
GROUP BY cint.num_dossier, cint.group_dossier
)
SELECT sd.num_dossier, sd.nabsences, sabs.start_date FROM absences_per_dossier sd
JOIN counting_inter cint ON cint.num_dossier = sd.num_dossier AND cint.group_dossier = sd.group_dossier AND cint.counter+1 = sd.nabsences
JOIN start_of_absences sabs ON sabs.num_dossier = sd.num_dossier AND sabs.group_dossier = cint.group_dossier
WHERE sd.nabsences > 2
;
|
Partager