45 lines
2.7 KiB
SQL
45 lines
2.7 KiB
SQL
/* ------------------------------------------------------------------
|
|
Carga historica de salidas (2022 en adelante) en adtb00062
|
|
desde adtb00003 (status_t = 'D' + fecha_salida + observacion libre).
|
|
origen = 'H'. Para deshacer: DELETE FROM adtb00062 WHERE origen = 'H'
|
|
Correr DESPUES de adprmt017_desvinculacion.sql. Idempotente.
|
|
------------------------------------------------------------------- */
|
|
SET NOCOUNT ON;
|
|
|
|
;WITH h AS (
|
|
SELECT a.num_emp, a.fecha_salida, a.departamento, a.cod_puesto,
|
|
ISNULL(a.tipo_empleado,'NORMAL') AS tipo_empleado, a.fech_efec,
|
|
UPPER(ISNULL(a.observacion,'')) AS obs
|
|
FROM dbo.adtb00003 a
|
|
WHERE a.status_t = 'D'
|
|
AND a.fecha_salida >= '2022-01-01'
|
|
AND a.num_emp >= 39
|
|
AND NOT EXISTS (SELECT 1 FROM dbo.adtb00062 d WHERE d.num_emp = a.num_emp AND d.fecha_salida = a.fecha_salida)
|
|
)
|
|
INSERT INTO dbo.adtb00062 (num_trx, num_emp, fecha_salida, cod_causa, recontratable, detalle,
|
|
departamento, cod_puesto, tipo_empleado, fech_ingreso, origen, us_crea, fech_crea)
|
|
SELECT NULL, h.num_emp, h.fecha_salida,
|
|
CASE WHEN h.obs LIKE '%CAMBIO DE EMPRESA%' OR h.obs LIKE '%CAMBIO A ORDEL%'
|
|
OR h.obs LIKE '%CAMBIO A CARIBBEAN%' OR h.obs LIKE '%TRASLAD%' THEN 12
|
|
WHEN h.obs LIKE '%CIERRE%' THEN 6
|
|
WHEN h.obs LIKE '%DESAHUCIO%' THEN 4
|
|
WHEN h.obs LIKE '%DESPID%' THEN 5
|
|
WHEN h.obs LIKE '%RENUNCIA%' THEN 1
|
|
WHEN h.obs LIKE '%ABANDONO%' THEN 2
|
|
WHEN h.obs LIKE '%DIMISION%' THEN 3
|
|
WHEN h.obs LIKE '%3 MESES%' OR h.obs LIKE '%PRUEBA%' THEN 8
|
|
WHEN h.obs LIKE '%FIN DE CONTRATO%' OR h.obs LIKE '%TEMPORADA%' THEN 7
|
|
WHEN h.obs LIKE '%MUTUO%' THEN 9
|
|
WHEN h.obs LIKE '%PENSION%' OR h.obs LIKE '%RETIRO%' THEN 10
|
|
WHEN h.obs LIKE '%FALLEC%' THEN 11
|
|
ELSE 99 END,
|
|
CASE WHEN h.obs LIKE '%NO REINGRESO%' OR h.obs LIKE '%NO RECONTRAT%' THEN 'N' END,
|
|
LEFT(NULLIF(LTRIM(RTRIM(h.obs)),''), 200),
|
|
h.departamento, h.cod_puesto, h.tipo_empleado, h.fech_efec, 'H', SUSER_SNAME(), GETDATE()
|
|
FROM h;
|
|
|
|
SELECT c.desc_causa, COUNT(*) n
|
|
FROM dbo.adtb00062 d JOIN dbo.adtb00061 c ON c.cod_causa = d.cod_causa
|
|
WHERE d.origen = 'H'
|
|
GROUP BY c.desc_causa ORDER BY 2 DESC;
|