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
    Expert éminent sénior
    Avatar de Paul TOTH
    Homme Profil pro
    Freelance
    Inscrit en
    novembre 2002
    Messages
    7 347
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Freelance
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : novembre 2002
    Messages : 7 347
    Points : 24 054
    Points
    24 054

    Par défaut Question requête agrégation deux fois sur la même table

    Bonjour,

    je pose la question ici car la base est FB mais je pense que c'est une question SQL générale

    soit 3 tables

    Praticiens
    Utilisateurs
    Appels

    un appel est fait pour un praticien et c'est un utilisateur qui décroche (ou pas => 0)

    un appel a une date d'appel, date de décrochage (éventuelle) et une date de fin d'appel

    je voudrais avoir un tableau avec

    Praticien | Utilisateur | Nombre d'appels | Temps de communication

    ça c'est facile, mais je dois distinguer les appels de plus et moins 10 secondes DATEDIFF(SECOND FROM Decrochage TO FinAppel)il me faut alors

    Praticien | Utilisateur | Nombre d'appels de +10s | Temps de comm | Nombre d'appels de -10s | Temps de comm

    j'ai une requête qui marche presque, mais elle ne me retourne pas les lignes quand aucun appel pour un couple praticien, utilisateur ne dépasse 10s

    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
     
    SELECT 
     p.Nom AS Praticien, 
     u.Nom AS Utilisateur, 
     d1.Appels AS Appels1, 
     d1.Duree AS Duree1, 
     d2.Appels AS Appels2, 
     d2.Duree AS Duree2 
     
    FROM Praticiens p 
     
    JOIN (
     SELECT Praticien, Utilisateur, COUNT(*) AS Appels, SUM(DATEDIFF(SECOND FROM Decrochage TO FinAppel)) AS Duree 
     FROM Appels 
     WHERE DATEDIFF(SECOND FROM Decrochage TO FinAppel) >= 10 
     GROUP BY Praticien, Utilisateur 
    ) d1 ON d1.Praticien = p.id 
     
    JOIN Utilisateurs u ON u.ID = d1.Utilisateur 
     
    LEFT JOIN (
      SELECT Praticien, Utilisateur, COUNT(*) AS Appels, SUM(DATEDIFF(SECOND FROM Decrochage TO FinAppel)) AS Duree 
      FROM Appels 
      WHERE DATEDIFF(SECOND FROM Decrochage TO FinAppel) < 10 
      GROUP BY Praticien, Utilisateur 
    ) d2 ON d2.Praticien = p.id AND d2.Utilisateur = u.ID
    je vois bien le pourquoi de cette limite mais je ne vois pas comment faire le lien entre p, d1, d2 et u autrement.

    Merci
    Developpez.com: Mes articles, forum FlashPascal
    Entreprise: Execute SARL
    Le Store Excute Store

  2. #2
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique
    Inscrit en
    janvier 2007
    Messages
    10 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : janvier 2007
    Messages : 10 736
    Points : 27 223
    Points
    27 223
    Billets dans le blog
    23

    Par défaut

    Bonjour,

    Quand je tombe sur des requêtes comme celle-ci je commence par utiliser des CTE

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
    WITH d1 AS (SELECT Praticien, Utilisateur, COUNT(*) AS Appels, SUM(DATEDIFF(SECOND FROM Decrochage TO FinAppel)) AS Duree 
                      FROM Appels 
                     WHERE DATEDIFF(SECOND FROM Decrochage TO FinAppel) >= 10 
                     GROUP BY Praticien, Utilisateur),
           d2 AS (SELECT Praticien, Utilisateur, COUNT(*) AS Appels, SUM(DATEDIFF(SECOND FROM Decrochage TO FinAppel)) AS Duree 
                      FROM Appels 
                     WHERE DATEDIFF(SECOND FROM Decrochage TO FinAppel) < 10 
                     GROUP BY Praticien, Utilisateur) 
    // à suivre
    Histoire de simplifier la suite
    ensuite, il me semble qu'il en faut encore une (CTE) qui va obtenir les couples Praticien,Utilisateur (il serait certainement possible de faire sans mais "diviser pour mieux régner" (C pour Couples)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    , C AS (SELECT Praticien,Utilisateur,count(*) AS TotalAppels  FROM Appels GROUP BY Praticien,Utilisateur)
     
    // à suivre
    Maintenant que les 'tables temporaires' sont faites il suffit de les utiliser

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    SELECT C.Praticien,C.Utilisateur,C.TotalAppels,COALESCE(d1.Appels,0) Appels1 ,COALESCE(d1.Duree,0) Duree1,COALESCE(d2.Appels,0) Appels2 ,COALESCE(d2.Duree,0) Duree2
    FROM C LEFT JOIN d1 ON d1.Praticien=c.Praticien AND d1.Utilisateur=c.Utilisateur
               LEFT JOIN d2 ON d2.Praticien=c.Praticien AND d2.Utilisateur=c.Utilisateur
    Voilà, il n'y a qu'à mettre tous les morceaux ensemble et je pense que la table résultat devrait te satisfaire (si j'ai bien saisi le problème)
    Il y a certainement mieux avec FB3 et les fonctions de fenêtrage mais 1) tu n'indiques pas quelle version de Firebird 2) je ne maitrise absolument pas ces fonctions
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein

    Delphi installés : D3,D7,D2010,XE4,XE7,D10 (Tokyo, Rio) et peut être quelques autres
    SGBD : Firebird 2.5, 3, SQLite
    générateurs Etats : FastReport, Rave, QuickReport
    OS : Window Vista, Windows 10, Ubuntu, Androïd

  3. #3
    Expert éminent sénior
    Avatar de Paul TOTH
    Homme Profil pro
    Freelance
    Inscrit en
    novembre 2002
    Messages
    7 347
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Freelance
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : novembre 2002
    Messages : 7 347
    Points : 24 054
    Points
    24 054

    Par défaut

    génial !

    je ne connaissais pas cette syntaxe

    Merci !
    Developpez.com: Mes articles, forum FlashPascal
    Entreprise: Execute SARL
    Le Store Excute Store

  4. #4
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    19 005
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 005
    Points : 44 666
    Points
    44 666

    Par défaut

    Et pourquoi pas plus simple en une seule requête ?

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
     SELECT Praticien, Utilisateur, COUNT(*) AS Appels, 
            SUM(DATEDIFF(SECOND FROM Decrochage TO FinAppel)) AS Duree,
    		CASE WHEN DATEDIFF(SECOND FROM Decrochage TO FinAppel) >= 10 THEN 'RETARD'
    		     ELSE 'OK' END AS LIMITE
     FROM Appels 
     WHERE  
     GROUP BY Praticien, Utilisateur,
           CASE WHEN DATEDIFF(SECOND FROM Decrochage TO FinAppel) >= 10 THEN 'RETARD'
    		     ELSE 'OK' END
    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  5. #5
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique
    Inscrit en
    janvier 2007
    Messages
    10 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : janvier 2007
    Messages : 10 736
    Points : 27 223
    Points
    27 223
    Billets dans le blog
    23

    Par défaut

    Bonjour,

    il est vrai que ce serait plus simple.
    Il y a plusieurs raisons qui m'ont fait proposer les CTE
    1 - je voulais reprendre ce que Paul avait déjà écrit
    2 - je n'étais pas sûr qu'il ne veuille pas ajouter les noms et autres informations des praticiens et utilisateurs
    3 - je me laissais la possibilité de proposer une CTE récursive pour obtenir tous les couples Praticien/utilisateur
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein

    Delphi installés : D3,D7,D2010,XE4,XE7,D10 (Tokyo, Rio) et peut être quelques autres
    SGBD : Firebird 2.5, 3, SQLite
    générateurs Etats : FastReport, Rave, QuickReport
    OS : Window Vista, Windows 10, Ubuntu, Androïd

  6. #6
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    4 034
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 79
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 4 034
    Points : 12 117
    Points
    12 117

    Par défaut

    Salut à tous.

    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
    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
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
    202
    203
    204
    205
    206
    207
    208
    209
    210
    211
    212
    213
    214
    215
    216
    217
    218
    219
    220
    221
    222
    223
    224
    225
    226
    CREATE DATABASE '..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET WIN1252;
     
    -- ================================
    -- Création de la table 'praticien'
    -- ================================
     
    create table praticien (
      id      smallint generated by default as identity not null,
      nom     varchar(20)                               not null,
      prenom  varchar(25)                               not null,
      primary key (id)
    );
     
    -- ==========================
    -- Insertion dans 'praticien'
    -- ==========================
     
    insert into praticien (nom,prenom) values ('Nom praticien 1','Prénom praticien 1');
    insert into praticien (nom,prenom) values ('Nom praticien 2','Prénom praticien 2');
     
    -- =====================
    -- Vidage de 'praticien'
    -- =====================
     
    select  *  from  praticien;
     
         ID NOM                  PRENOM
    ======= ==================== =========================
          1 Nom praticien 1      Prénom praticien 1
          2 Nom praticien 2      Prénom praticien 2
     
     
    -- ==================================
    -- Création de la table 'utilisateur'
    -- ==================================
     
    create table utilisateur (
      id      smallint generated by default as identity not null,
      nom     varchar(20)                               not null,
      prenom  varchar(25)                               not null,
      primary key (id)
    );
     
    -- ============================
    -- Insertion dans 'utilisateur'
    -- ============================
     
    insert into utilisateur (nom,prenom) values ('Nom utilisateur 1','Prénom utilisateur 1');
    insert into utilisateur (nom,prenom) values ('Nom utilisateur 2','Prénom utilisateur 2');
    insert into utilisateur (nom,prenom) values ('Nom utilisateur 3','Prénom utilisateur 3');
     
    -- =======================
    -- Vidage de 'utilisateur'
    -- =======================
     
    select  *  from  utilisateur;
     
         ID NOM                  PRENOM
    ======= ==================== =========================
          1 Nom utilisateur 1    Prénom utilisateur 1
          2 Nom utilisateur 2    Prénom utilisateur 2
          3 Nom utilisateur 3    Prénom utilisateur 3
     
     
    commit;
     
    -- ============================
    -- Création de la table 'appel'
    -- ============================
     
    create table appel (
      id           smallint generated by default as identity not null,
      praticien    smallint                                  not null   REFERENCES praticien   (id) ON DELETE CASCADE ON UPDATE CASCADE,
      utilisateur  smallint                                  not null   REFERENCES utilisateur (id) ON DELETE CASCADE ON UPDATE CASCADE,
      decrochage   timestamp                                 not null,
      finappel     timestamp                                 not null,
      primary key (id)
    );
     
    CREATE INDEX IDX_PRATICIEN   ON appel (praticien);
    CREATE INDEX IDX_UTILISATEUR ON appel (utilisateur);
     
    -- ======================
    -- Insertion dans 'appel'
    -- ======================
     
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 1, '2019-06-01 11:37:45', '2019-06-01 11:37:48');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 2, '2019-06-01 11:39:45', '2019-06-01 11:42:28');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 3, '2019-06-01 11:43:17', '2019-06-01 11:43:22');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 1, '2019-06-01 11:45:29', '2019-06-01 11:50:01');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 2, '2019-06-01 11:47:33', '2019-06-01 11:47:38');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 3, '2019-06-01 11:49:12', '2019-06-01 11:51:37');
     
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 1, '2019-06-01 11:50:24', '2019-06-01 11:50:25');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 2, '2019-06-01 11:53:18', '2019-06-01 11:58:24');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 3, '2019-06-01 11:55:47', '2019-06-01 12:05:12');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 1, '2019-06-01 12:15:33', '2019-06-01 12:15:38');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 2, '2019-06-01 12:17:27', '2019-06-01 12:20:57');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 3, '2019-06-01 12:23:23', '2019-06-01 12:23:45');
     
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 1, '2019-06-01 14:37:45', '2019-06-01 14:37:48');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 2, '2019-06-01 14:39:45', '2019-06-01 14:42:28');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 3, '2019-06-01 14:43:17', '2019-06-01 14:43:22');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 1, '2019-06-01 14:45:29', '2019-06-01 14:50:01');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 2, '2019-06-01 14:47:33', '2019-06-01 14:47:38');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 3, '2019-06-01 14:49:12', '2019-06-01 14:51:37');
     
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 1, '2019-06-01 14:50:24', '2019-06-01 14:50:25');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 2, '2019-06-01 14:53:18', '2019-06-01 14:58:24');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 3, '2019-06-01 14:55:47', '2019-06-01 15:05:12');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 1, '2019-06-01 15:15:33', '2019-06-01 15:15:38');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 2, '2019-06-01 15:17:27', '2019-06-01 15:20:57');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 3, '2019-06-01 15:23:23', '2019-06-01 15:23:45');
     
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 1, '2019-06-01 16:37:45', '2019-06-01 16:37:48');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 2, '2019-06-01 16:39:45', '2019-06-01 16:42:28');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 3, '2019-06-01 16:43:17', '2019-06-01 16:43:22');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 1, '2019-06-01 16:45:29', '2019-06-01 16:50:01');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 2, '2019-06-01 16:47:33', '2019-06-01 16:47:38');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 3, '2019-06-01 16:49:12', '2019-06-01 16:51:37');
     
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 1, '2019-06-01 16:50:24', '2019-06-01 16:50:25');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 2, '2019-06-01 16:53:18', '2019-06-01 16:58:24');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 3, '2019-06-01 16:55:47', '2019-06-01 17:05:12');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 1, '2019-06-01 17:15:33', '2019-06-01 17:15:38');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (1, 2, '2019-06-01 17:17:27', '2019-06-01 17:20:57');
    insert into appel (praticien,utilisateur,decrochage,finappel) values (2, 3, '2019-06-01 17:23:23', '2019-06-01 17:23:45');
     
    -- =================
    -- Vidage de 'appel'
    -- =================
     
    select  *  from  appel;
     
         ID PRATICIEN UTILISATEUR                DECROCHAGE                  FINAPPEL
    ======= ========= =========== ========================= =========================
          1         1           1 2019-06-01 11:37:45.0000  2019-06-01 11:37:48.0000
          2         2           2 2019-06-01 11:39:45.0000  2019-06-01 11:42:28.0000
          3         1           3 2019-06-01 11:43:17.0000  2019-06-01 11:43:22.0000
          4         2           1 2019-06-01 11:45:29.0000  2019-06-01 11:50:01.0000
          5         1           2 2019-06-01 11:47:33.0000  2019-06-01 11:47:38.0000
          6         2           3 2019-06-01 11:49:12.0000  2019-06-01 11:51:37.0000
          7         1           1 2019-06-01 11:50:24.0000  2019-06-01 11:50:25.0000
          8         2           2 2019-06-01 11:53:18.0000  2019-06-01 11:58:24.0000
          9         1           3 2019-06-01 11:55:47.0000  2019-06-01 12:05:12.0000
         10         2           1 2019-06-01 12:15:33.0000  2019-06-01 12:15:38.0000
         11         1           2 2019-06-01 12:17:27.0000  2019-06-01 12:20:57.0000
         12         2           3 2019-06-01 12:23:23.0000  2019-06-01 12:23:45.0000
         13         1           1 2019-06-01 14:37:45.0000  2019-06-01 14:37:48.0000
         14         2           2 2019-06-01 14:39:45.0000  2019-06-01 14:42:28.0000
         15         1           3 2019-06-01 14:43:17.0000  2019-06-01 14:43:22.0000
         16         2           1 2019-06-01 14:45:29.0000  2019-06-01 14:50:01.0000
         17         1           2 2019-06-01 14:47:33.0000  2019-06-01 14:47:38.0000
         18         2           3 2019-06-01 14:49:12.0000  2019-06-01 14:51:37.0000
         19         1           1 2019-06-01 14:50:24.0000  2019-06-01 14:50:25.0000
         20         2           2 2019-06-01 14:53:18.0000  2019-06-01 14:58:24.0000
     
         ID PRATICIEN UTILISATEUR                DECROCHAGE                  FINAPPEL
    ======= ========= =========== ========================= =========================
         21         1           3 2019-06-01 14:55:47.0000  2019-06-01 15:05:12.0000
         22         2           1 2019-06-01 15:15:33.0000  2019-06-01 15:15:38.0000
         23         1           2 2019-06-01 15:17:27.0000  2019-06-01 15:20:57.0000
         24         2           3 2019-06-01 15:23:23.0000  2019-06-01 15:23:45.0000
         25         1           1 2019-06-01 16:37:45.0000  2019-06-01 16:37:48.0000
         26         2           2 2019-06-01 16:39:45.0000  2019-06-01 16:42:28.0000
         27         1           3 2019-06-01 16:43:17.0000  2019-06-01 16:43:22.0000
         28         2           1 2019-06-01 16:45:29.0000  2019-06-01 16:50:01.0000
         29         1           2 2019-06-01 16:47:33.0000  2019-06-01 16:47:38.0000
         30         2           3 2019-06-01 16:49:12.0000  2019-06-01 16:51:37.0000
         31         1           1 2019-06-01 16:50:24.0000  2019-06-01 16:50:25.0000
         32         2           2 2019-06-01 16:53:18.0000  2019-06-01 16:58:24.0000
         33         1           3 2019-06-01 16:55:47.0000  2019-06-01 17:05:12.0000
         34         2           1 2019-06-01 17:15:33.0000  2019-06-01 17:15:38.0000
         35         1           2 2019-06-01 17:17:27.0000  2019-06-01 17:20:57.0000
         36         2           3 2019-06-01 17:23:23.0000  2019-06-01 17:23:45.0000
     
     
    commit;
     
    -- ===========
    -- Requête N°1
    -- ===========
     
    with CTE_1 as (    SELECT  p.nom                                            as Praticien,
                               u.nom                                            as Utilisateur,
                               datediff(SECOND FROM a.decrochage TO a.finAppel) as duree
     
                         FROM  appel        as a
     
                   INNER JOIN  praticien    as p
                           ON  p.id = a.praticien
     
                   INNER JOIN  utilisateur  as u
                           ON  u.id = a.utilisateur
                  ),
         CTE_2 as (    SELECT  Praticien,
                               Utilisateur,
                               case when duree <=10 then duree else null end as moins,
                               case when duree > 10 then duree else null end as plus
                         FROM  CTE_1
                  ),
         CTE_3 as (    SELECT  Praticien,
                               utilisateur,
                               cast(  sum(moins) as smallint) as moins_de_10,
                               cast(  sum(plus)  as smallint) as plus_de_10,
                               cast(count(moins) as smallint) as nbre_moins_de_10,
                               cast(count(plus)  as smallint) as nbre_plus_de_10
     
                         FROM  CTE_2
                     GROUP BY  Praticien, Utilisateur
                  )
     
    select * from CTE_3;
     
    PRATICIEN            UTILISATEUR          MOINS_DE_10 PLUS_DE_10 NBRE_MOINS_DE_10 NBRE_PLUS_DE_10
    ==================== ==================== =========== ========== ================ ===============
    Nom praticien 1      Nom utilisateur 1             12     <null>                6               0
    Nom praticien 1      Nom utilisateur 2             15        630                3               3
    Nom praticien 1      Nom utilisateur 3             15       1695                3               3
    Nom praticien 2      Nom utilisateur 1             15        816                3               3
    Nom praticien 2      Nom utilisateur 2         <null>       1407                0               6
    Nom praticien 2      Nom utilisateur 3         <null>        501                0               6
     
    exit;
     
    Appuyez sur une touche pour continuer...
    J'ai décomposé le CTE en trois phases afin de bien montrer comment le calcul se fait.
    J'ai utilisé le case ... end pour justement décomposer le calcul en moins de 10 secondes et en plus de 10 secondes.
    Pour le count(), l'astuce est d'utiliser le NULL car il n'est pas comptabilisé.
    Pour chaque couple (praticien, utilisateur), il y a toujours six lignes dans mon exemple.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  7. #7
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    4 034
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 79
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 4 034
    Points : 12 117
    Points
    12 117

    Par défaut

    Re-Salut à tous.

    La solution proposée par SQLPRO fonctionne parfaitement.
    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
    SELECT  Praticien,
            Utilisateur,
            COUNT(*) AS Appels,
            SUM(DATEDIFF(SECOND FROM Decrochage TO FinAppel)) AS Duree,
            CASE WHEN DATEDIFF(SECOND FROM Decrochage TO FinAppel) >= 10 THEN 'RETARD' ELSE 'OK' END AS LIMITE
     FROM Appel
     GROUP  BY Praticien, Utilisateur,
           CASE WHEN DATEDIFF(SECOND FROM Decrochage TO FinAppel) >= 10 THEN 'RETARD' ELSE 'OK' END
    ;
     
    PRATICIEN UTILISATEUR                APPELS                 DUREE LIMITE
    ========= =========== ===================== ===================== ======
            1           1                     6                    12 OK
            1           2                     3                    15 OK
            1           2                     3                   630 RETARD
            1           3                     3                    15 OK
            1           3                     3                  1695 RETARD
            2           1                     3                    15 OK
            2           1                     3                   816 RETARD
            2           2                     6                  1407 RETARD
            2           3                     6                   501 RETARD
     
    exit;
    J'avais cru comprendre que le résultat recherché devait être sur 1 et 1 seule ligne.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 8
    Dernier message: 07/01/2015, 13h48
  2. une requête avec 2 count sur la même table
    Par epeichette dans le forum Requêtes
    Réponses: 11
    Dernier message: 30/06/2009, 09h19
  3. Réponses: 1
    Dernier message: 14/02/2008, 08h02
  4. [AJAX] Gestion de deux listes sur une même table
    Par kabkab dans le forum Général JavaScript
    Réponses: 2
    Dernier message: 19/01/2008, 13h48
  5. Fusion de deux requetes sur une même table
    Par jojosbiz dans le forum Langage SQL
    Réponses: 6
    Dernier message: 25/06/2007, 09h18

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