Requêtes : Groupement de deux résultats

**QuestVba** · 28/05/2020, 12h12

Bonjour Forum

,

J'ai une question toute simple pour vous mais complexe pour moi.
En fait j'ai deux requêtes :

Code :

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

1
2
1. La première me donne le nombre, par agent, de dossiers distincts pour lesquels il a chargé un document automatiquement pour ses propres dossiers
2. La seconde me donne le nombre, par agent, de dossiers distincts pour lesquels un autre agent a chargé un document automatiquement

Voici les deux requêtes :

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
select 
    '04/2020' AS Month_Select
    , s2.FILE_MANAGER AS FILE_MANAGER
    , count(distinct s2.file_number) AS INJECTED_DOCUMENT_BY_HIMSELF
from
(
select *
from
(
select 
    ad.process_date
    , ad.file_number
    , ad.lot_number
    , ad.agent_number as ID_SCANNER
    , CASE
        WHEN ag2.first_name || ' ' || ag2.last_name ='LESLIE RITSERVELDT' then 'SCAN_OPERATOR'
        WHEN ag2.first_name || ' ' || ag2.last_name ='NADINE SUENENS' then 'SCAN_OPERATOR'
        WHEN ag2.first_name || ' ' || ag2.last_name ='ROLAND BRUYNBROECK' then 'SCAN_OPERATOR'
        WHEN ag2.first_name || ' ' || ag2.last_name ='MATTHIEU LEFRANCQ' then 'SCAN_OPERATOR'
        WHEN ag2.first_name || ' ' || ag2.last_name ='ANNIE VANDEN DAELE' then 'SCAN_OPERATOR'
        WHEN ag2.first_name || ' ' || ag2.last_name ='Sabine MERTENS' then 'SERV_JUR'
        WHEN ag2.first_name || ' ' || ag2.last_name ='PHILIPPE LERNOULD' then 'SERV_JUR'
        WHEN ad.agent_number is null then 'MYFAMIRIS'
        else ag2.first_name || ' ' || ag2.last_name
    end as SCANNER
    , ag.agent_number AS ID_FILEMANAGER
    , CASE
        WHEN ag.first_name || ' ' || ag.last_name ='LESLIE RITSERVELDT' then 'SCAN_OPERATOR'
        WHEN ag.first_name || ' ' || ag.last_name ='NADINE SUENENS' then 'SCAN_OPERATOR'
        WHEN ag.first_name || ' ' || ag.last_name ='ROLAND BRUYNBROECK' then 'SCAN_OPERATOR'
        WHEN ag.first_name || ' ' || ag.last_name ='MATTHIEU LEFRANCQ' then 'SCAN_OPERATOR'
        WHEN ag.first_name || ' ' || ag.last_name ='ANNIE VANDEN DAELE' then 'SCAN_OPERATOR'
        WHEN ad.agent_number is null then 'MYFAMIRIS'
        else ag.first_name || ' ' || ag.last_name
    end as FILE_MANAGER
    , CASE
        WHEN ad.agent_number = ag.agent_number THEN 1
        ELSE 0
    END AS BY_HIMSELF
 
 
 
from archived_documents ad
    inner join file_managements fm on fm.file_number = ad.file_number
    inner join agents ag on ag.agent_id = fm.file_manager_id and start_date < '01/05/2020' and end_date is null
 
 
    left join agents ag2 on ag2.agent_number = ad.agent_number
 
where ad.process_date>='01/04/2020' and ad.process_date<='30/04/2020'
order by ad.file_number
) s1
where s1.by_himself = 1
) s2
group by FILE_MANAGER
order by 2
;

La seconde :

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
select 
    '04/2020' AS Month_Select
    , s2.FILE_MANAGER AS FILE_MANAGER
    , count(distinct s2.file_number) AS INJECTED_DOCUMENT_BY_other
from
(
select *
from
(
select 
    ad.process_date
    , ad.file_number
    , ad.lot_number
    , ad.agent_number as ID_SCANNER
    , CASE
        WHEN ag2.first_name || ' ' || ag2.last_name ='LESLIE RITSERVELDT' then 'SCAN_OPERATOR'
        WHEN ag2.first_name || ' ' || ag2.last_name ='NADINE SUENENS' then 'SCAN_OPERATOR'
        WHEN ag2.first_name || ' ' || ag2.last_name ='ROLAND BRUYNBROECK' then 'SCAN_OPERATOR'
        WHEN ag2.first_name || ' ' || ag2.last_name ='MATTHIEU LEFRANCQ' then 'SCAN_OPERATOR'
        WHEN ag2.first_name || ' ' || ag2.last_name ='ANNIE VANDEN DAELE' then 'SCAN_OPERATOR'
        WHEN ag2.first_name || ' ' || ag2.last_name ='Sabine MERTENS' then 'SERV_JUR'
        WHEN ag2.first_name || ' ' || ag2.last_name ='PHILIPPE LERNOULD' then 'SERV_JUR'
        WHEN ad.agent_number is null then 'MYFAMIRIS'
        else ag2.first_name || ' ' || ag2.last_name
    end as SCANNER
    , ag.agent_number AS ID_FILEMANAGER
    , CASE
        WHEN ag.first_name || ' ' || ag.last_name ='LESLIE RITSERVELDT' then 'SCAN_OPERATOR'
        WHEN ag.first_name || ' ' || ag.last_name ='NADINE SUENENS' then 'SCAN_OPERATOR'
        WHEN ag.first_name || ' ' || ag.last_name ='ROLAND BRUYNBROECK' then 'SCAN_OPERATOR'
        WHEN ag.first_name || ' ' || ag.last_name ='MATTHIEU LEFRANCQ' then 'SCAN_OPERATOR'
        WHEN ag.first_name || ' ' || ag.last_name ='ANNIE VANDEN DAELE' then 'SCAN_OPERATOR'
        WHEN ad.agent_number is null then 'MYFAMIRIS'
        else ag.first_name || ' ' || ag.last_name
    end as FILE_MANAGER
    , CASE
        WHEN (ad.agent_number <> ag.agent_number AND 
            (ad.agent_number <> 00100218 and ad.agent_number <> 00102082 and ad.agent_number <> 00000614
            and ad.agent_number <> 00101269 and ad.agent_number <> 00102148 and ad.agent_number <> 00060721)) THEN 1
        ELSE 0
    END AS BY_OTHER
 
 
from archived_documents ad
    inner join file_managements fm on fm.file_number = ad.file_number
    inner join agents ag on ag.agent_id = fm.file_manager_id and start_date < '01/05/2020' and end_date is null
 
 
    left join agents ag2 on ag2.agent_number = ad.agent_number
 
where ad.process_date>='01/04/2020' and ad.process_date<='30/04/2020'
order by ad.file_number
) s1
where s1.by_other = 1
) s2
group by FILE_MANAGER
order by 2
;

Voici les deux résultats côte à côte.
Nom : Image 2.png
Affichages : 74
Taille : 39,0 Ko

Est-ce qu'il serait possible de joindre les deux requêtes pour obtenir le résultat dans un seul tableau ? Évidemment, il se peut que certains agents aient des résultats dans les deux colonnes
(car ils ont travaillé pour eux-mêmes et que d'autres ont travaillé pour eux) ou pas de résultat dans une des deux colonnes (par ex, l'agent qui a été absent tout le mois et qui a été aidé par un autre : On voit Angélique qui a été absente mais on a travaillé pour elle) ?

J'espère avoir été clair. Si vous avez des questions, n'hésitez pas. Merci

**vanagreg** · 28/05/2020, 13h51

Bonjour,

Oui tu peux regrouper les requêtes en une seule:

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
with uploaded_docs 
as (select ad.process_date
           , ad.file_number
           , ad.lot_number
           , ad.agent_number as ID_SCANNER
           , CASE
                WHEN ag2.first_name || ' ' || ag2.last_name IN ('LESLIE RITSERVELDT', 'NADINE SUENENS', 'ROLAND BRUYNBROECK', 'MATTHIEU LEFRANCQ', 'ANNIE VANDEN DAELE') THEN 'SCAN_OPERATOR'
                WHEN ag2.first_name || ' ' || ag2.last_name IN ('Sabine MERTENS', 'PHILIPPE LERNOULD') THEN 'SERV_JUR'
                WHEN ad.agent_number is null then 'MYFAMIRIS'
                ELSE ag2.first_name || ' ' || ag2.last_name
            END as SCANNER
            , ag.agent_number AS ID_FILEMANAGER
            , CASE
                WHEN ag2.first_name || ' ' || ag2.last_name IN ('LESLIE RITSERVELDT', 'NADINE SUENENS', 'ROLAND BRUYNBROECK', 'MATTHIEU LEFRANCQ', 'ANNIE VANDEN DAELE') THEN 'SCAN_OPERATOR'                
                WHEN ad.agent_number is null then 'MYFAMIRIS'
                ELSE ag.first_name || ' ' || ag.last_name
            end as FILE_MANAGER
            , CASE
                WHEN ad.agent_number = ag.agent_number THEN 1
                ELSE 0
              END AS BY_HIMSELF
            , CASE
                WHEN (ad.agent_number <> ag.agent_number AND 
                    (ad.agent_number <> 00100218 and ad.agent_number <> 00102082 and ad.agent_number <> 00000614
                    and ad.agent_number <> 00101269 and ad.agent_number <> 00102148 and ad.agent_number <> 00060721)) THEN 1
                ELSE 0
             END AS BY_OTHER
    from archived_documents ad
    inner join file_managements fm on fm.file_number = ad.file_number
    inner join agents ag on ag.agent_id = fm.file_manager_id and start_date < to_date('01/05/2020', 'dd/mm/yyyy') and end_date is null 
    left join agents ag2 on ag2.agent_number = ad.agent_number 
    where ad.process_date>= to_date('01/04/2020', 'dd/mm/yyyy') and ad.process_date<=to_date('30/04/2020', 'dd/mm/yyyy')
   )
select  '04/2020' AS Month_Select
      , FILE_MANAGER AS FILE_MANAGER
      , count(distinct case when BY_HIMSELF = 1 then file_number end) AS INJECTED_DOCUMENT_BY_HIMSELF
      , count(distinct case when BY_OTHER = 1 then file_number end) AS INJECTED_DOCUMENT_BY_other
from uploaded_docs
group by FILE_MANAGER
order by 2
;

Attention à bien préciser TO_DATE avec le format quand tu passes une date en dur.