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
| -- BY_HIMSELF / BY_OTHER / FOR_OTHER
with uploaded_docs
as (select to_char(ad.process_date,'mm/yyyy') as Month_Select
, ad.file_number
, ad.lot_number
, ad.agent_number as ID_SCANNER
, ag2.first_name || ' ' || ag2.last_name as SCANNER
, ag.agent_number AS ID_FILEMANAGER
, ag.first_name || ' ' || ag.last_name 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 THEN 1
ELSE 0
END AS BY_OTHER
, CASE
WHEN ad.agent_number <> ag.agent_number THEN 1
ELSE 0
END AS FOR_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 to_char(start_date,'yyyymm') < '202012' and end_date is null
and fm.filemanagement_type = 'PERMANENT'
left join agents ag2 on ag2.agent_number = ad.agent_number
where to_char(ad.process_date,'yyyymm') = '202005'
and ad.archived_type='DIRECT'
), S_BY_HIMSELF_AND_OTHER as (
select 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 Month_Select, FILE_MANAGER
), S_FOR_OTHER as (
select Month_Select
, SCANNER AS SCANNER
, count(distinct case when FOR_OTHER = 1 then file_number end) AS INJECTED_DOCUMENT_FOR_other
from uploaded_docs
group by Month_Select, SCANNER
)
select
nvl(B.Month_Select, FR.Month_Select) as Month_Select
, nvl(B.FILE_MANAGER, FR.SCANNER) as FILE_MANAGER
, B.INJECTED_DOCUMENT_BY_HIMSELF
, B.INJECTED_DOCUMENT_BY_OTHER
, FR.INJECTED_DOCUMENT_FOR_other
from
S_BY_HIMSELF_AND_OTHER B
full outer join S_FOR_OTHER FR on B.FILE_MANAGER = FR.SCANNER
order by 2
; |
