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
| select TO_CHAR(count(case When f.analysis_type in ('BI','SNES','BULL_TR') Then c.name End)) AS NbDemande,
f.analysis_type as Bulletin,
DECODE(c.x_l_type_client,'C','PRIVE','S','SOC','Q','SEV-MAGNERAUD','G','GEVES') as Client
from (select sum(d.X_N_DUREE) as duree,a.name as project
from project a,DEM05_PRESTATIONS b,DEM01_DETAIL_PREST c,TEST_LIST d
where a.name = b.Project
AND a.name = c.Project
AND b.D05_A_VRCI_NO = c.D01_A_VRCI_NO
AND c.D01_A_VRLISTPLAN_ANA = d.name
GROUP BY a.name) a
,
(select max(b.D05_D_RENDU_SNES) as dateSNES,a.name as project
from project a,DEM05_PRESTATIONS b,DEM01_DETAIL_PREST c
where a.name = b.Project
AND a.name = c.Project
AND b.D05_A_VRCI_NO = c.D01_A_VRCI_NO
GROUP BY b.D05_D_RENDU_SNES,a.name) b,
project c,
DEM01_DETAIL_PREST d,
TEST_LIST_ENTRY e,
ANALYSIS f
where a.project = b.project
AND a.project = c.name
AND b.project = c.name
AND c.date_created <= b.dateSNES-a.duree-7
AND c.name = d.project
AND d.D01_A_VRLISTPLAN_ANA = e.name
AND e.analysis = f.name
AND f.analysis_type in ('BI','SNES','BULL_TR')
AND (c.x_l_type_client is not null AND c.x_l_type_client != 'T')
group by f.analysis_type,c.x_l_type_client
order by f.analysis_type |
