Discussion :

[bib34690]

Bonjour,
J'ai :
- 1 Table Patients (NumDossier, Nom).
- 1 Table Interventions (NumDossier, Date, CodePresence)
Je voudrais avoir la liste des patients qui ont eu au moins X absences consécutives (par ordre chronologique), X étant un paramètre pouvant varier de 2 à 8.
Une absence est un CodePresence compris entre 1 et 50.
Je travaille avec SQL Server 2012.

Merci pour votre aide.
Bib

[aieeeuuuuu]

Bonjour,

Merci de fournir un jeu d'essai couvrant les différents cas possibles.

Qu'y a-t-il notamment dans la table Présence en cas de présence effective ? Y a-t-il forcément une et une seule ligne dans la table présence pour chaque jour ?

[escartefigue]

Question subsidiaire : faut il tenir compte d'un calendrier des jours ouvrés ?
Ex : patient absent le vendredi puis le lundi qui suit, selon que le samedi et le dimanche comptent ou pas, il y a ou pas deux jours consécutifs !
Evidemment, ça se corse avec les jours fériés mobiles

Selon la réponse, une table calendrier s'impose

[bib34690]

Merci pour l'intérêt porté à ma question qui était effectivement imprécise.
Les interventions sont irrégulières. Il peut y en avoir plusieurs le même jour et aucune pendant plusieurs jours.
Pas de vérification de date à faire, juste trouver les absences consécutives par ordre chrono.
Le code présence n'a pas d'importance. Pour l'exemple disons que Présent=1 et Absent = 2.
Voici un exemple de table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
create table I
(
    NumDossier int primary key identity,
    [Date] smallDateTime,
    CodeAbs tinyint
)
go
 
insert into I values (1, '01-01-2018', 1);
insert into I values (1, '05-01-2018', 2);
insert into I values (1, '06-01-2018', 2);
insert into I values (1, '06-01-2018', 2);
insert into I values (2, '01-01-2018', 1);
insert into I values (2, '03-01-2018', 1);

Je souhaite pouvoir demander quels sont les patients qui ont 3 absences consécutives.
Il devrait me répondre : dossier 1 depuis le 05-01-2018

[escartefigue]

Essayez ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 select * from (                                          
 select patient      PP                                   
      , date1        DD                                   
      , sum(case                                          
              when flag=2 then 1                          
                          else 0                          
              end)                                        
        over(partition by patient                         
             order by date1 asc                           
             rows between 2 preceding and current row) CC 
 from Ma_Table
 )                                                        
 where CC>2

[aieeeuuuuu]

ou bien, dans la même veine :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
WITH tmp AS(
    SELECT *, COUNT(CASE WHEN CodeAbs = 2 THEN 1 ELSE NULL END) OVER(PARTITION BY NumDossier ORDER BY [Date] ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS NbAbs
    FROM I
)
SELECT NumDossier, [Date]
FROM tmp
WHERE CodeAbs = 2 
AND NbAbs = 3

[bib34690]

Désolé escartefigue, quand je colle cette Procédure ds SQL 2012, j'ai 1 erreur de syntaxe sur le dernier Where :
Syntaxe incorrecte proche de Where. Attendu AS, ID ou Quoted_id
...autant pour moi, il manquait juste un alias en fin.

Désolé, ce n'est pas le résultat attendu.
Avec cette requête si 1 patient a 4 absences, il sort 2 fois dans la liste avec chaque fois 3 absences alors qu'il ne devrait sortir qu'1 fois avec 4 absences consécutives.
La question c'est "la liste des patients qui ont 3 (ou plus) abs.consécutives, désolé d'avoir été imprécis 1 fois de plus.

[escartefigue]

laquelle des deux requêtes avez vous essayée ?

[bib34690]

Merci aieeeuuuuu mais c'est le même resultat : si 1 patient a 10 absences consécutives, il sort 7 fois avec 1 Nb = 3 pour chacun au lieu de sortir 1 seule fois avec Nb=10

[aieeeuuuuu]

il suffit de vérifier que la ligne précédente n'est pas une absence :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
WITH tmp AS(
    SELECT *
        , COUNT(CASE WHEN CodeAbs = 2 THEN 1 ELSE NULL END) OVER(PARTITION BY NumDossier ORDER BY [Date] ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS NbAbs
        , LAG(CodeAbs) OVER(PARTITION BY NumDossier ORDER BY [Date]) AS Prec
    FROM I
)
SELECT NumDossier, [Date]
FROM tmp
WHERE CodeAbs = 2 
AND Prec = 1
AND NbAbs = 3

[bib34690]

Merci aieeeuuuuu, on se rapproche mais ce n'est pas encore tout à fait ça.

1) Quand il y a 10 abs. consécutives, il continue de me retourner 3 au lieu de 10
2) Je pense que ca ne marchera pas s'il est absent dès la 1ère intervention, il n'y aura pas de précédent.

Le 1er problème vient du "CURRENT ROW AND 2 FOLLOWING" qui ne sait que vérifier 1 lignes et les 2 qui suivent.
Il faudrait utiliser ce compteur pour filtrer mais init. le Nb de consécutifs avec 1 autre requete qui compte les consécutifs à partir de cette date...

Pour le 2nd problème, on doit pouvoir corriger la formule...mais j'en suis incapable

[bib34690]

Ca semble compliqué.
Je suis prêt à réduire mes ambitions.
Est-il possible d'avoir juste la liste des NumDossier qui en ont 3 ou plus consécutifs, sans avoir ni la date ni le Nb ?

[escartefigue]

Il me semble qu'il suffit d'ajouter un distinct sur le patient et de supprimer la date dans la requete que je vous avais proposée et vous n'aurez plus de doublons

[JeitEmgie]

Ca semble compliqué.
Je suis prêt à réduire mes ambitions.
Est-il possible d'avoir juste la liste des NumDossier qui en ont 3 ou plus consécutifs, sans avoir ni la date ni le Nb ?

Si cela peut inspirer quelqu'un pour une solution avec SQL Server, en voici une pour Oracle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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  
;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
num_dossier nabsences start_date
1    4    2018-01-01 10:00:00
1    3    2018-01-06 10:00:00

Pour comprendre le résultat de la clause MODEL, le résultat de counting_inter est

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
num_dossier, rn, counter, group_dossier, code_presence
1	1	0	0	0
1	2	1	0	0
1	3	2	0	0
1	4	3	0	0
1	5	0	1	1
1	6	0	2	0
1	7	1	2	0
1	8	2	2	0
2	1	0	0	0
2	2	0	1	1
3	1	0	0	0

[JeitEmgie]

En remplaçant le counting_inter basé sur MODEL par une version récursive :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
counting_inter(num_dossier,rn, counter,group_dossier,code_presence) as (
  select num_dossier, rn,
    0 as counter,
    rn as group_dossier, code_presence
    from indexed_inter inter
    where rn = 1
    union all
    select rec.num_dossier, inter.rn, 
    case when rec.code_presence = inter.code_presence
         then rec.counter + 1 else 0 end as counter,
     case when rec.code_presence = inter.code_presence
      then rec.group_dossier else rec.group_dossier + 1 end as group_dossier, 
        inter.code_presence
   from counting_inter rec
   join indexed_inter inter on inter.num_dossier = rec.num_dossier and inter.rn = rec.rn + 1
)

le résultat de couting_inter est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
num_dossier,rn, counter,group_dossier,code_presence
1    1    0    1    0
1    2    1    1    0
1    3    2    1    0
1    4    3    1    0
1    5    0    2    1
1    6    0    3    0
1    7    1    3    0
1    8    2    3    0
2    1    0    1    0
2    2    0    2    1
3    1    0    1    0

donc le reste doit fonctionner et cette version doit être adaptable en SQL Server assez trivialement.

[bib34690]

Merci JeitEmgie pour ta réponse qui m'a aidée, mais je ne suis pas assez expert pour traduire en SqlServer et ça me semble un peu compliqué.

Vos réponses m'ont fait penser à 1 compromis qui répondrait à ma question et permettrait d'aller plus loin mais je butte sur 1 point sur lequel j'aimerais votre aide. L'idée serait d'utiliser Row_Number au lieu de Count. Il suffirait qu'il reparte à 1 lorsqu'on change de Num_dossier ou de code_presence. J'épérais avoir trouvé la solution avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Row_Number() OVER(PARTITION BY Num_Dossier, Code_presence ORDER BY Date_int) AS RowNum

...mais il ne tient pas compte du order by pour numéroter et je ne trouve pas de solution.

Voici le code pour créer la base (merci Emgie):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(1, '01-01-2018 10:00', 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(1, '02-01-2018 10:00', 0) ;
 
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(2, '03-01-2018 10:00', 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(2, '04-01-2018 10:00', 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(2, '05-01-2018 10:00', 1) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(2, '06-01-2018 10:00', 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(2, '07-01-2018 10:00', 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(2, '07-01-2018 12:00', 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(2, '09-01-2018 10:00', 1) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(2, '12-01-2018 10:00', 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(2, '13-01-2018 12:00', 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(2, '14-01-2018 10:00', 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(2, '15-01-2018 12:00', 0) ;
 
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(3, '02-01-2018 10:00', 1) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(4, '01-01-2018 10:00', 0) ;
INSERT INTO t_interventions(num_dossier, date_int, code_presence) values(4, '01-01-2018 10:00', 0) ;

...et la requête qui ne donne pas le bon résultat pour l'instant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
WITH tmp AS(
    SELECT *, Row_Number() OVER(PARTITION BY Num_Dossier, Code_presence ORDER BY Date_int) AS RowNum
    FROM t_interventions
)
SELECT *
FROM tmp
order by Num_dossier, date_int

Résultat obtenu :
num_dossier date_int code_presence RowNum
1 2018-01-01 0 1
1 2018-01-02 0 2
2 2018-01-03 0 1
2 2018-01-04 0 2
2 2018-01-05 1 1
2 2018-01-06 0 3 <<< à partir d'ici ca ne marche plus, il a numéroté en classant par Code_presence et non par date
2 2018-01-07 0 4
2 2018-01-07 0 5
2 2018-01-09 1 2
2 2018-01-12 0 6
2 2018-01-13 0 7
2 2018-01-14 0 8
2 2018-01-15 0 9
3 2018-01-02 1 1
4 2018-01-01 0 1
4 2018-01-01 0 2

Ca me permettrait de demander les dossier dont RowNum > 3 ou 4 ou 5... et d'avoir pour chaque limite la date...si vous me trouvez la solution...
Merci pour votre aide

[JeitEmgie]

Si vous utilisez la version récursive du counting_inter cela doit fonctionner car c'est lui qui résout le problème posé par le over(partition num_dossier, code_presence)
c'est lui qui numérote correctement les plages de code_presence identique par dossier et en plus il compte le nombre de lignes dans chaque plage,
et c'est cette numérotation qui permet ensuite de partitionner correctement.

Notez que si vous utilisez le RN comme numéro de groupe au lieu d'un numéro qui s'incrémente, vous devriez aussi retrouver la start_date de chaque plage par un simple "JOIN On num_dossier AND rn"
au lieu d'avoir besoin de start_of_absences et cela ne doit pas être trop compliqué d'utiliser le même principe pour avoir la end_date de chaque plage,
ainsi le résultat final pourrait être des tuples (num_dossier, n_absences, start_date, end_date).

Dans un premier temps (sans la end_date des plages)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
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(num_dossier,rn, counter,group_dossier,code_presence) as (
  select num_dossier, rn,
    0 as counter,
    rn as group_dossier, code_presence
    from indexed_inter inter
    where rn = 1
    union all
    select rec.num_dossier, inter.rn, 
    case when rec.code_presence = inter.code_presence
         then rec.counter + 1 else 0 end as counter,
     case when rec.code_presence = inter.code_presence
      then rec.group_dossier else inter.rn end as group_dossier, 
        inter.code_presence
   from counting_inter rec
   join indexed_inter inter on inter.num_dossier = rec.num_dossier and inter.rn = rec.rn + 1
),
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
)
SELECT sd.num_dossier, sd.nabsences, sabs.date_int 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 indexed_inter sabs ON sabs.num_dossier = sd.num_dossier AND sabs.rn = cint.group_dossier
    WHERE sd.nabsences > 2  
;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
num_dossier nabsences start_date
1    4    01-JAN-18
1    3    06-JAN-18

[aieeeuuuuu]

1) Quand il y a 10 abs. consécutives, il continue de me retourner 3 au lieu de 10
2) Je pense que ca ne marchera pas s'il est absent dès la 1ère intervention, il n'y aura pas de précédent.

Pour le deuxième point, il n'y a qu'a modifier la condition : AND Prec = 1 OR Prec IS NULLPour le deuxième point, je n'avais pa compris que vous vouliez connaitre le nombre d'absence consécutives.
Il faudra modifier la requete, voire l'approche, mais avant d'aller plus loin, il faut clarifier tous les cas possible.

Notamment, comme vous pouvez avoir plusieurs lignes par jour, comment cela se passe-t-il si vous avez, le même jour, une absence et une présence ?
2018-01-01 : absent
2018-01-02 : absent
2018-01-03 : present
2018-01-03 : absent


Il faudrait une colonne supplémentaire pour ordonner les deux dernières lignes...

[aieeeuuuuu]

Si l'on suppose que vous avez une composante horaire, et que vous ne pouvez avoir deux lignes à la même date pour un même numéro de dossier, ceci devrait convenir (dans le cas contraire, le résultat sera non déterministe):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
WITH R AS (
    SELECT  *, CASE WHEN LAG(code_presence) OVER(PARTITION BY num_dossier ORDER BY date_int) = code_presence THEN 0 ELSE 1 END AS rupt
    FROM	   t_interventions
),
Groupe AS (
    SELECT *, SUM(rupt) OVER(PARTITION BY num_dossier ORDER BY date_int) AS grp
    FROM R
)
SELECT num_dossier, MIN(date_int) AS debut_absence, COUNT(*) AS NbAbsence
FROM Groupe
WHERE code_presence = 0
GROUP BY num_dossier, grp
HAVING COUNT(*) >= 3

[JeitEmgie]

Et avec start/end date de chaque plage :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
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(num_dossier,rn, counter,group_dossier,code_presence,prev_group,prev_end_date, cur_date) as (
  select num_dossier, rn,
    1 as counter,
    rn as group_dossier, code_presence,
    0 as prev_group, cast(null as date) as prev_end_date,
    date_int as cur_date
    from indexed_inter inter
    where rn = 1
    union all
    select rec.num_dossier, inter.rn, 
    case when rec.code_presence = inter.code_presence
         then rec.counter + 1 else 1 end as counter,
     case when rec.code_presence = inter.code_presence
      then rec.group_dossier else inter.rn end as group_dossier, 
        inter.code_presence,
     case when rec.code_presence = inter.code_presence 
        then rec.prev_group else rec.group_dossier end,
     case when rec.code_presence = inter.code_presence 
        then rec.prev_end_date else rec.cur_date end,
    inter.date_int 
   from counting_inter rec
   join indexed_inter inter on inter.num_dossier = rec.num_dossier and inter.rn = rec.rn + 1
)
,
absences_per_dossier as (
    SELECT num_dossier, group_dossier, max(counter) AS nabsences FROM counting_inter
    WHERE code_presence = 0
    GROUP BY num_dossier, group_dossier
)
SELECT sd.num_dossier, sd.nabsences, sabs.date_int as from_date , nvl(edat.prev_end_date, ledat.cur_date) as to_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= sd.nabsences
    JOIN indexed_inter sabs ON sabs.num_dossier = sd.num_dossier AND sabs.rn = cint.group_dossier
    LEFT JOIN counting_inter edat ON edat.num_dossier = sd.num_dossier AND edat.prev_group = sd.group_dossier
    LEFT JOIN counting_inter ledat ON ledat.num_dossier = sd.num_dossier AND ledat.group_dossier = sd.group_dossier AND ledat.counter = sd.nabsences
    WHERE sd.nabsences > 2  
    order by sd.num_dossier, sabs.date_int
;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
num_dossier nabsences from_date to_date
1    4    01-JAN-18    04-JAN-18
1    3    06-JAN-18    07-JAN-18