Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Membre habitué
    Profil pro
    Développeur informatique
    Inscrit en
    juin 2002
    Messages
    240
    Détails du profil
    Informations personnelles :
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : juin 2002
    Messages : 240
    Points : 147
    Points
    147

    Par défaut Liste patients ayant X absences consécutives

    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

  2. #2
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    4 932
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 4 932
    Points : 10 132
    Points
    10 132

    Par défaut

    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 ?

  3. #3
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    3 875
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 3 875
    Points : 8 867
    Points
    8 867
    Billets dans le blog
    1

    Par défaut

    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

  4. #4
    Membre habitué
    Profil pro
    Développeur informatique
    Inscrit en
    juin 2002
    Messages
    240
    Détails du profil
    Informations personnelles :
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : juin 2002
    Messages : 240
    Points : 147
    Points
    147

    Par défaut

    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

  5. #5
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    3 875
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 3 875
    Points : 8 867
    Points
    8 867
    Billets dans le blog
    1

    Par défaut

    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

  6. #6
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    4 932
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 4 932
    Points : 10 132
    Points
    10 132

    Par défaut

    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

  7. #7
    Membre habitué
    Profil pro
    Développeur informatique
    Inscrit en
    juin 2002
    Messages
    240
    Détails du profil
    Informations personnelles :
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : juin 2002
    Messages : 240
    Points : 147
    Points
    147

    Par défaut

    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.

  8. #8
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    3 875
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 3 875
    Points : 8 867
    Points
    8 867
    Billets dans le blog
    1

    Par défaut

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

  9. #9
    Membre habitué
    Profil pro
    Développeur informatique
    Inscrit en
    juin 2002
    Messages
    240
    Détails du profil
    Informations personnelles :
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : juin 2002
    Messages : 240
    Points : 147
    Points
    147

    Par défaut

    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

  10. #10
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    4 932
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 4 932
    Points : 10 132
    Points
    10 132

    Par défaut

    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

  11. #11
    Membre habitué
    Profil pro
    Développeur informatique
    Inscrit en
    juin 2002
    Messages
    240
    Détails du profil
    Informations personnelles :
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : juin 2002
    Messages : 240
    Points : 147
    Points
    147

    Par défaut

    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

  12. #12
    Membre habitué
    Profil pro
    Développeur informatique
    Inscrit en
    juin 2002
    Messages
    240
    Détails du profil
    Informations personnelles :
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : juin 2002
    Messages : 240
    Points : 147
    Points
    147

    Par défaut

    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 ?

  13. #13
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    3 875
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 3 875
    Points : 8 867
    Points
    8 867
    Billets dans le blog
    1

    Par défaut

    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

  14. #14
    Membre expert
    Homme Profil pro
    Inscrit en
    septembre 2006
    Messages
    2 601
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : septembre 2006
    Messages : 2 601
    Points : 3 637
    Points
    3 637

    Par défaut

    Citation Envoyé par bib34690 Voir le message
    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

  15. #15
    Membre expert
    Homme Profil pro
    Inscrit en
    septembre 2006
    Messages
    2 601
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : septembre 2006
    Messages : 2 601
    Points : 3 637
    Points
    3 637

    Par défaut

    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.

  16. #16
    Membre habitué
    Profil pro
    Développeur informatique
    Inscrit en
    juin 2002
    Messages
    240
    Détails du profil
    Informations personnelles :
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : juin 2002
    Messages : 240
    Points : 147
    Points
    147

    Par défaut

    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

  17. #17
    Membre expert
    Homme Profil pro
    Inscrit en
    septembre 2006
    Messages
    2 601
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : septembre 2006
    Messages : 2 601
    Points : 3 637
    Points
    3 637

    Par défaut

    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

  18. #18
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    4 932
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 4 932
    Points : 10 132
    Points
    10 132

    Par défaut

    Citation Envoyé par bib34690 Voir le message
    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...

  19. #19
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    4 932
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 4 932
    Points : 10 132
    Points
    10 132

    Par défaut

    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

  20. #20
    Membre expert
    Homme Profil pro
    Inscrit en
    septembre 2006
    Messages
    2 601
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : septembre 2006
    Messages : 2 601
    Points : 3 637
    Points
    3 637

    Par défaut

    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

+ Répondre à la discussion
Cette discussion est résolue.
Page 1 sur 2 12 DernièreDernière

Discussions similaires

  1. [Modèle Relationnel] Schéma BDD d'une liste arborescente ayant plusieurs aspects.
    Par llaffont dans le forum Schéma
    Réponses: 2
    Dernier message: 06/01/2012, 12h47
  2. [AC-2007] Liste déroulante ayant pour critère une autre valeur de la requete
    Par yoyo1664 dans le forum Requêtes et SQL.
    Réponses: 2
    Dernier message: 24/03/2011, 08h41
  3. Réponses: 4
    Dernier message: 24/11/2010, 21h31
  4. Réponses: 1
    Dernier message: 19/01/2010, 11h52
  5. Réponses: 7
    Dernier message: 24/04/2006, 10h31

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo