-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathDatos_Desa.sql
More file actions
474 lines (468 loc) · 13.6 KB
/
Datos_Desa.sql
File metadata and controls
474 lines (468 loc) · 13.6 KB
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
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
---
--- Inscritos 2021-2022
---
select
COORDINACION as DIRECCION_GENERAL
, case when substr(vista.cct,3,1) = 'P'
then 'PRIVADO'
else 'PUBLICO'
end as SOSTENIMIENTO
, trim(sos.DES_SOSTENIMIENTO) as SOSTENIMIENTO_AEFCM
, vista.niveleducativo as NIVEL_EDUCATIVO
, decode(substr(vista.cct,4,2)
,'CR', 'INICIAL'
,'DI', 'INICIAL'
,'JN', 'PREESCOLAR'
,'EP', 'PREESCOLAR COMUNITARIOS'
,'PR', 'PRIMARIA GENERAL'
,'BN', 'PRIMARIA PARA TRABAJADORES'
,'IX', 'PRIMARIA PARTICIPACIÓN SOCIAL'
,'ZQ', 'PREESCOLAR COMUNITARIOS'
,'ML', 'ESPECIAL'
,'CO', 'ESPECIAL'
,'DM', 'ESPECIAL'
,'SN', 'SECUNDARIA PARA TRABAJADORES'
,'ES', 'SECUNDARIA GENERAL'
,'ST', 'SECUNDARIA TÉCNICA'
,'TV', 'TELESECUNDARIA'
, 'SIN CLASIFICAR'
) as SERVICIO_EDUCATIVO
, vista.alcaldia_cct as ALCADIA
, vista.CCT
, vista.nombreescuela as NOMBRE_PLANTEL
, vista.idturno as TURNO_ID
, vista.TURNO
, vista.GRADO
, vista.GRUPO
, null as SISTEMA_ID
, null as SISTEMA
, null as ALUMNO_ID
, vista.CURP
, vista.primerapellido as PRIMER_APELLIDO
, vista.segundoapellido as SEGUNDO_APELLIDO
, vista.NOMBRES
from mview_alumnos_20212022 vista
left join cct bdu on vista.cct = bdu.cct
left join sostenimiento sos on sos.cod_sostenimiento = bdu.cod_sostenimiento
where curp in ('ZUNC790612P50','LARC841129UF4','MUGP850402HB9','COPC870131FS4','PAVV771011BHA')
;
---
--- Boletas 2020-2021
---
select
decode(trim(bdu.cod_dep_normat)
,'SF','DGOSE'
,'SG','DGOSE'
,'TV','DGOSE'
,'PR','DGOSE'
,'EP','DGOSE'
,'EX','DGOSE'
,'EE','DGOSE'
,'IZ','DGSEI'
,'EN','DGENAM'
,'TE','DGEST'
,'DP','DGPPEE'
,'DX','DGIFA'
,'AP','DGA'
,'DF','AEFCM'
,'SC','OSEP'
,'ND','CONADE'
) as DIRECCION_GENERAL
, case when substr(BOLETAS.cct,3,1) = 'P'
then 'PRIVADO'
else 'PUBLICO'
end as SOSTENIMIENTO
, trim(sos.DES_SOSTENIMIENTO) as SOSTENIMIENTO_AEFCM
, BOLETAS.NIVEL_EDUCATIVO
, decode(substr(BOLETAS.cct,4,2)
,'CR', 'INICIAL'
,'DI', 'INICIAL'
,'JN', 'PREESCOLAR'
,'EP', 'PREESCOLAR COMUNITARIOS'
,'PR', 'PRIMARIA GENERAL'
,'BN', 'PRIMARIA PARA TRABAJADORES'
,'IX', 'PRIMARIA PARTICIPACIÓN SOCIAL'
,'ZQ', 'PREESCOLAR COMUNITARIOS'
,'ML', 'ESPECIAL'
,'CO', 'ESPECIAL'
,'DM', 'ESPECIAL'
,'SN', 'SECUNDARIA PARA TRABAJADORES'
,'ES', 'SECUNDARIA GENERAL'
,'ST', 'SECUNDARIA TÉCNICA'
,'TV', 'TELESECUNDARIA'
, 'SIN CLASIFICAR'
) as SERVICIO_EDUCATIVO
, (Select trim(alc.nombre) alcaldia from
cct ct, cit alc
Where ct.cct = BOLETAS.cct
And ct.cod_entidad = Alc.cod_entidad
And ct.cod_municipio = alc.cod_municipio
And '0000' = alc.cod_localidad) as ALCADIA
, BOLETAS.CCT
, bdu.nombre as NOMBRE_PLANTEL
, BOLETAS.TURNO_ID
, BOLETAS.TURNO
, BOLETAS.GRADO
, BOLETAS.GRUPO
, BOLETAS.SISTEMA_ID
, BOLETAS.SISTEMA
, BOLETAS.ID_EN_SISTEMA_ORIGEN as ALUMNO_ID
, BOLETAS.CURP
, BOLETAS.PRIMER_APELLIDO
, BOLETAS.SEGUNDO_APELLIDO
, BOLETAS.NOMBRES
from w_boletas_evaluacion_2020_2021 boletas
left join cct bdu on boletas.cct = bdu.cct
left join sostenimiento sos on sos.cod_sostenimiento = bdu.cod_sostenimiento
;
---
--- Comparación matrícula boletas vs insctipciones
---
select cct
, trim(nombre_plantel) as NOMBRE_PLANTEL
, decode(trim(cod_dep_normat)
,'SF','DGOSE'
,'SG','DGOSE'
,'TV','DGOSE'
,'PR','DGOSE'
,'EP','DGOSE'
,'EX','DGOSE'
,'EE','DGOSE'
,'IZ','DGSEI'
,'EN','DGENAM'
,'TE','DGEST'
,'DP','DGPPEE'
,'DX','DGIFA'
,'AP','DGA'
,'DF','AEFCM'
,'SC','OSEP'
,'ND','CONADE'
) as DIRECCION_GENERAL
, (Select trim(alc.nombre) alcaldia from
cct ct, cit alc
Where ct.cct = BOLETAS.cct
And ct.cod_entidad = Alc.cod_entidad
And ct.cod_municipio = alc.cod_municipio
And '0000' = alc.cod_localidad) as ALCADIA
, decode(cod_turno
,100,'MATUTINO'
,200,'VESPERTINO'
,300,'NOCTURNO'
,400,'DISCONTINUO'
,120,'MATUTINO Y VESPERTINO'
,123,'MATUTINO, VESPERTINO Y NOCTURNO'
,500,'TIEMPO COMPLETO'
,700,'JORNADA AMPLIADA'
,'DESCONOCIDO') as TURNO
, (select count(b.curp) from w_boletas_evaluacion_2020_2021 b
where BOLETAS.cct = b.cct) as MATRICULA_2020_2021_BOLETAS
, (select count(v.curp) from mview_alumnos_20212022 v
where BOLETAS.cct = v.cct) as MATRICULA_2021_2022_INSCRIP
from (select UNIQUE v.cct, bdu.nombre as NOMBRE_PLANTEL, bdu.cod_dep_normat,
bdu.cod_turno from w_boletas_evaluacion_2020_2021 v
left join cct bdu on v.cct = bdu.cct) BOLETAS
-- where CCT not in (select UNIQUE v.cct from w_boletas_evaluacion_2020_2021 v)
where substr(cct,3,1) = 'P';
---
--- Asginacion_preescolar
---
select
decode(trim(bdu.cod_dep_normat)
,'SF','DGOSE'
,'SG','DGOSE'
,'TV','DGOSE'
,'PR','DGOSE'
,'EP','DGOSE'
,'EX','DGOSE'
,'EE','DGOSE'
,'IZ','DGSEI'
,'EN','DGENAM'
,'TE','DGEST'
,'DP','DGPPEE'
,'DX','DGIFA'
,'AP','DGA'
,'DF','AEFCM'
,'SC','OSEP'
,'ND','CONADE'
) as DIRECCION_GENERAL
, case when substr(ASIGNACION_CCT,3,1) = 'P'
then 'PRIVADO'
else 'PUBLICO'
end as SOSTENIMIENTO
, trim(sos.DES_SOSTENIMIENTO) as SOSTENIMIENTO_AEFCM
, 'PREESCOLAR' as NIVEL_EDUCATIVO
, decode(substr(ASIGNACION_CCT,4,2)
,'CR', 'INICIAL'
,'DI', 'INICIAL'
,'JN', 'PREESCOLAR'
,'EP', 'PREESCOLAR COMUNITARIOS'
,'PR', 'PRIMARIA GENERAL'
,'BN', 'PRIMARIA PARA TRABAJADORES'
,'IX', 'PRIMARIA PARTICIPACIÓN SOCIAL'
,'ZQ', 'PREESCOLAR COMUNITARIOS'
,'ML', 'ESPECIAL'
,'CO', 'ESPECIAL'
,'DM', 'ESPECIAL'
,'SN', 'SECUNDARIA PARA TRABAJADORES'
,'ES', 'SECUNDARIA GENERAL'
,'ST', 'SECUNDARIA TÉCNICA'
,'TV', 'TELESECUNDARIA'
, 'SIN CLASIFICAR'
) as SERVICIO_EDUCATIVO
, (Select trim(alc.nombre) alcaldia from
cct ct, cit alc
Where ct.cct = ASIGNACION_CCT
And ct.cod_entidad = Alc.cod_entidad
And ct.cod_municipio = alc.cod_municipio
And '0000' = alc.cod_localidad) as ALCADIA
, ASIGNACION_CCT as CCT
, trim(bdu.nombre) as NOMBRE_PLANTEL
, asignacion_turno_id as TURNO_ID
, asignacion_turno as TURNO
, CURP
, PRIMER_APELLIDO
, SEGUNDO_APELLIDO
, NOMBRES
, asignacion_grado as GRADO
from
(
select
cmb.curp
, cmb.nompila as NOMBRES
, cmb.paterno as PRIMER_APELLIDO
, cmb.materno as SEGUNDO_APELLIDO
, pl.cct asignacion_cct
, (select ct.cod_turno from cct ct where ct.cct = pl.cct) asignacion_turno_id
, (select trim(tno.des_turno) from cct ct, turno tno where ct.cct = pl.cct and ct.cod_turno = tno.cod_turno) asignacion_turno
, cmb.grado asignacion_grado
, CMB.CANCELA
from p2122_prcmb1819_alumnos_pre cmb
, p2122_plantel pl
where cmb.llaasi = pl.ordenproceso(+)
union
select
asg.curp
, asg.nompila as NOMBRES
, asg.paterno as PRIMER_APELLIDO
, asg.materno as SEGUNDO_APELLIDO
, pl.cct asignacion_cct
, (select ct.cod_turno from cct ct where ct.cct = pl.cct) asignacion_turno_id
, (select trim(tno.des_turno) from cct ct, turno tno where ct.cct = pl.cct and ct.cod_turno = tno.cod_turno) asignacion_turno
, asg.grado asignacion_grado
, ASG.CANCELA
from p2122_pasg1819_alumnos_pre asg -- 61 549 total menos 83 canceladas = 61 466
, p2122_plantel pl
where
asg.llaasi = pl.ordenproceso(+)
and not exists
(select * from p2122_prcmb1819_alumnos_pre cmb
where asg.curp = cmb.curp
)
)
left join cct bdu on ASIGNACION_CCT = bdu.cct
left join sostenimiento sos on sos.cod_sostenimiento = bdu.cod_sostenimiento
where cancela is null -- 61,466
;
---
--- Asignacion primaria
---
select
decode(trim(bdu.cod_dep_normat)
,'SF','DGOSE'
,'SG','DGOSE'
,'TV','DGOSE'
,'PR','DGOSE'
,'EP','DGOSE'
,'EX','DGOSE'
,'EE','DGOSE'
,'IZ','DGSEI'
,'EN','DGENAM'
,'TE','DGEST'
,'DP','DGPPEE'
,'DX','DGIFA'
,'AP','DGA'
,'DF','AEFCM'
,'SC','OSEP'
,'ND','CONADE'
) as DIRECCION_GENERAL
, case when substr(ASIGNACION_CCT,3,1) = 'P'
then 'PRIVADO'
else 'PUBLICO'
end as SOSTENIMIENTO
, trim(sos.DES_SOSTENIMIENTO) as SOSTENIMIENTO_AEFCM
, 'PRIMARIA' as NIVEL_EDUCATIVO
, decode(substr(ASIGNACION_CCT,4,2)
,'CR', 'INICIAL'
,'DI', 'INICIAL'
,'JN', 'PREESCOLAR'
,'EP', 'PREESCOLAR COMUNITARIOS'
,'PR', 'PRIMARIA GENERAL'
,'BN', 'PRIMARIA PARA TRABAJADORES'
,'IX', 'PRIMARIA PARTICIPACIÓN SOCIAL'
,'ZQ', 'PREESCOLAR COMUNITARIOS'
,'ML', 'ESPECIAL'
,'CO', 'ESPECIAL'
,'DM', 'ESPECIAL'
,'SN', 'SECUNDARIA PARA TRABAJADORES'
,'ES', 'SECUNDARIA GENERAL'
,'ST', 'SECUNDARIA TÉCNICA'
,'TV', 'TELESECUNDARIA'
, 'SIN CLASIFICAR'
) as SERVICIO_EDUCATIVO
, (Select trim(alc.nombre) alcaldia from
cct ct, cit alc
Where ct.cct = ASIGNACION_CCT
And ct.cod_entidad = Alc.cod_entidad
And ct.cod_municipio = alc.cod_municipio
And '0000' = alc.cod_localidad) as ALCADIA
, ASIGNACION_CCT as CCT
, trim(bdu.nombre) as NOMBRE_PLANTEL
, asignacion_turno_id as TURNO_ID
, asignacion_turno as TURNO
, CURP
, PRIMER_APELLIDO
, SEGUNDO_APELLIDO
, NOMBRES
, asignacion_grado as GRADO
from
(
select
cmb.curp
, cmb.nompila as NOMBRES
, cmb.paterno as PRIMER_APELLIDO
, cmb.materno as SEGUNDO_APELLIDO
, pl.cct asignacion_cct
, (select ct.cod_turno from cct ct where ct.cct = pl.cct) asignacion_turno_id
, (select trim(tno.des_turno) from cct ct, turno tno where ct.cct = pl.cct and ct.cod_turno = tno.cod_turno) asignacion_turno
, 1 asignacion_grado
, CMB.CANCELA
from p2122_prcmb1819_alumnos_pri cmb
, p2122_plantel pl
where cmb.llaasi = pl.ordenproceso(+)
union
select
asg.curp
, asg.nompila as NOMBRES
, asg.paterno as PRIMER_APELLIDO
, asg.materno as SEGUNDO_APELLIDO
, pl.cct asignacion_cct
, (select ct.cod_turno from cct ct where ct.cct = pl.cct) asignacion_turno_id
, (select trim(tno.des_turno) from cct ct, turno tno where ct.cct = pl.cct and ct.cod_turno = tno.cod_turno) asignacion_turno
, 1 asignacion_grado
, ASG.CANCELA
from p2122_pasg1819_alumnos_pri asg -- 92 223 total menos 30 cancelada 92 193
, p2122_plantel pl
where
asg.llaasi = pl.ordenproceso(+)
and not exists
(select * from p2122_prcmb1819_alumnos_pri cmb
where asg.curp = cmb.curp
)
)
left join cct bdu on ASIGNACION_CCT = bdu.cct
left join sostenimiento sos on sos.cod_sostenimiento = bdu.cod_sostenimiento
where cancela is null
;
---
--- Asignacion secundaria
---
select
decode(trim(bdu.cod_dep_normat)
,'SF','DGOSE'
,'SG','DGOSE'
,'TV','DGOSE'
,'PR','DGOSE'
,'EP','DGOSE'
,'EX','DGOSE'
,'EE','DGOSE'
,'IZ','DGSEI'
,'EN','DGENAM'
,'TE','DGEST'
,'DP','DGPPEE'
,'DX','DGIFA'
,'AP','DGA'
,'DF','AEFCM'
,'SC','OSEP'
,'ND','CONADE'
) as DIRECCION_GENERAL
, case when substr(ASIGNACION_CCT,3,1) = 'P'
then 'PRIVADO'
else 'PUBLICO'
end as SOSTENIMIENTO
, trim(sos.DES_SOSTENIMIENTO) as SOSTENIMIENTO_AEFCM
, 'SECUNDARIA' as NIVEL_EDUCATIVO
, decode(substr(ASIGNACION_CCT,4,2)
,'CR', 'INICIAL'
,'DI', 'INICIAL'
,'JN', 'PREESCOLAR'
,'EP', 'PREESCOLAR COMUNITARIOS'
,'PR', 'PRIMARIA GENERAL'
,'BN', 'PRIMARIA PARA TRABAJADORES'
,'IX', 'PRIMARIA PARTICIPACIÓN SOCIAL'
,'ZQ', 'PREESCOLAR COMUNITARIOS'
,'ML', 'ESPECIAL'
,'CO', 'ESPECIAL'
,'DM', 'ESPECIAL'
,'SN', 'SECUNDARIA PARA TRABAJADORES'
,'ES', 'SECUNDARIA GENERAL'
,'ST', 'SECUNDARIA TÉCNICA'
,'TV', 'TELESECUNDARIA'
, 'SIN CLASIFICAR'
) as SERVICIO_EDUCATIVO
, (Select trim(alc.nombre) alcaldia from
cct ct, cit alc
Where ct.cct = ASIGNACION_CCT
And ct.cod_entidad = Alc.cod_entidad
And ct.cod_municipio = alc.cod_municipio
And '0000' = alc.cod_localidad) as ALCADIA
, ASIGNACION_CCT as CCT
, trim(bdu.nombre) as NOMBRE_PLANTEL
, asignacion_turno_id as TURNO_ID
, asignacion_turno as TURNO
, CURP
, PRIMER_APELLIDO
, SEGUNDO_APELLIDO
, NOMBRES
, asignacion_grado as GRADO
from
(
select
cmb.curp
, cmb.nombre as NOMBRES
, cmb.apel_pat as PRIMER_APELLIDO
, cmb.apel_mat as SEGUNDO_APELLIDO
, case when pl.turno_id = 120 and cmb.ampl = 2 then pl.cct_vespertino else pl.cct end asignacion_cct
, (cmb.ampl * 100) asignacion_turno_id
, (select trim(tno.des_turno) from turno tno where tno.cod_turno = (cmb.ampl * 100)) asignacion_turno
, 1 asignacion_grado
from p2122_prcmb1819_alumnos_sec cmb
, p2122_plantel pl
where cmb.llaasi = pl.ordenproceso(+)
union
select
asg.curp
, asg.nombre as NOMBRES
, asg.apel_pat as PRIMER_APELLIDO
, asg.apel_mat as SEGUNDO_APELLIDO
, case when pl.turno_id = 120 and asg.ampl = 2 then pl.cct_vespertino else pl.cct end asignacion_cct
, (asg.ampl * 100) asignacion_turno_id
, (select trim(tno.des_turno) from turno tno where tno.cod_turno = (asg.ampl * 100)) asignacion_turno
, 1 asignacion_grado
from p2122_pasg1819_alumnos_sec asg -- 119 291 menos 6 cancelados
, p2122_plantel pl
where
asg.llaasi = pl.ordenproceso(+)
and not exists
(
select * from p2122_prcmb1819_alumnos_sec cmb
where asg.curp = cmb.curp
)
)
left join cct bdu on ASIGNACION_CCT = bdu.cct
left join sostenimiento sos on sos.cod_sostenimiento = bdu.cod_sostenimiento
where
asignacion_cct is not null
--asignacion_turno is null
;
TO_NUMBER('100.00');
select * from p2122_pasg1819_alumnos_pre where curp = 'MXAS180208HDFDNNA6'