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
| SELECT distinct
DEM_IDSTA as IDSTA,
sti_id as IDSTI,
sum(HRE_SESSION.nb_heures) as NBH_SES_INS_TOTAL
FROM DEMANDES
JOIN STAGES ON DEM_IDSTA=STA_ID AND DEM_ISUPP=0
JOIN AFFAIRES_PRODUITS on DEM_IDAPR = APR_ID
JOIN AFFAIRES on APR_IDAFF=AFF_ID
JOIN STAGES_INSCRIPTIONS on STI_IDSTA=DEM_IDSTA and STI_ISUPP=0 and STI_DATE_DES is null and sti_id > 0
JOIN (
select
STI_IDSTA,
sti_id as stiid,
coalesce (SES_PARTIEL.IDSTS,STS_ID) as IDSTS,
NB_SES_INSCRIT,
NB_SES_TOTAL
from
stages_inscriptions
left join (
select distinct
STI_IDSTA as IDSTA,
sti_id as IDSTI,
coalesce (NB_SES_INS, SES_PLAN.NB_SES) as NB_SES_INSCRIT,
SES_PLAN.NB_SES as NB_SES_TOTAL
from
stages_inscriptions
left join stages_sessions on STI_IDSTA = STS_IDSTA and STS_ISUPP=0
left join stages_inscriptions_sessions on STS_ID = SIS_IDSTS and SIS_IDSTI=STI_ID AND SIS_ISUPP=0
left join (
select sts_idsta as IDSTA, count(sts_id) as NB_SES
from stages_sessions
where sts_isupp = 0
group by sts_idsta
) as SES_PLAN on SES_PLAN.IDSTA=STI_IDSTA
left join (
select sti_idsta as IDSTA, STI_ID as IDSTI, count(sis_id) as NB_SES_INS
from stages_inscriptions
join stages_inscriptions_sessions on STI_ID=SIS_IDSTI
where sis_isupp = 0 and STI_MOTIF_DES_CVT71 IS NULL and STI_DATE_DES is null and sti_id > 0
group by sti_idsta, STI_ID
) as SES_INS on SES_INS.IDSTA=STI_IDSTA and SES_INS.IDSTI=sti_id
where
STI_DATE_DES is null
and sti_id > 0
and sti_isupp=0
) as INSC on INSC.IDSTA=STI_IDSTA AND sti_id=INSC.IDSTI
left join stages_sessions on STI_IDSTA = STS_IDSTA and STS_ISUPP=0 and NB_SES_INSCRIT = NB_SES_TOTAL
left join (
select
STI_IDSTA as IDSTA,
sti_id as IDSTI,
SIS_IDSTS as IDSTS
from
stages_inscriptions
join stages_sessions on STI_IDSTA = STS_IDSTA and STS_ISUPP=0
join stages_inscriptions_sessions on STS_ID = SIS_IDSTS and SIS_IDSTI=STI_ID AND SIS_ISUPP=0
) as SES_PARTIEL on SES_PARTIEL.IDSTA= STI_IDSTA and SES_PARTIEL.IDSTI=sti_id
where
STI_DATE_DES is null
and sti_id > 0
and sti_isupp=0
) as INSCRIT on INSCRIT.STI_IDSTA=DEM_IDSTA and INSCRIT.STIID=STI_ID
left join (
select
sta_id,
sts_id,
sum(stq_jours) as nb_jours,
sum(stq_heures) as nb_heures
from
stages
join stages_sessions on sta_id=sts_idsta and sts_isupp=0
join stages_sequences on sts_id=stq_idsts and stq_isupp=0 and stq_type_cvt26 in ('001','003')
where
STA_ISUPP=0
group by
sta_id, sts_id
) as HRE_SESSION on
HRE_SESSION.sta_id=dem_idsta and HRE_SESSION.sts_id=INSCRIT.IDSTS
WHERE
STA_ISUPP=0 AND STA_ETAT_STAGE_CVT42='2' and dem_etat_cvt33 IN ('DEM','ENP','PRO','VAL')
GROUP BY
AFF_ID,
APR_ID,
DEM_ID,
DEM_IDSTA,
sti_statut_cvt58,
sti_statut_detail_cvt88,
sti_id,
DEM_IDSTP |
Temps de réponse encore et toujours
Répondre avec citation
Envoyé par HadanMarv
Partager