47 lines
3.7 KiB
Plaintext
47 lines
3.7 KiB
Plaintext
INSERT INTO tx_skpp_copy(id_pen,nama,nip,nrk,golongan,status_pegawai,tugas,dinas,akhir,id_kep,no_pen,tgl_pen,id_hak,tgl_tmt,ga_pok,t_issum,t_anak,t_lain,t_jab,t_beras,tpph,bulat,p_beras,iuran,ppph,lain_2,potong,gaber,u_gaji1,u_gaji2,jml_u_gaji,u_beras1,u_beras2,jml_u_beras,id_kan,kon_peg,status_id,tgl_cetak,tgl_peg,keterangan,nama_kantor1,nama_kantor2,wilayah,f_migrasi_data)
|
|
SELECT IDPen,Nama,NIP,NRK,Gol,StaPeg,Tugas,Dinas,Akhir,IDKep,NoPen,TglPen,IDHak,TglTMT,GaPok,TIssum,TAnak,TLain,TJab,TBeras,TPPH,Bulat,PBeras,Iuran,PPPH,Lain2,Potong,Gaber,UGaji1,UGaji2,JmlUGaji,UBeras1,UBeras2,JmlUBeras,IDKan,KonPeg,Status,TglCtk,TglPeg,Keterangan,namakantor1,namakantor2,wil,1 FROM T_Pegawai;
|
|
|
|
INSERT INTO tx_keluarga_copy(nip,nama,hubungan,tgl_lahir) SELECT NIP,NmAnak,HubAnak,TglLahir FROM T_Keluarga;
|
|
|
|
SELECT akhir,
|
|
case when SUBSTRING_INDEX(akhir,' ',1)='JANUARI' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-01-01')
|
|
when (SUBSTRING_INDEX(akhir,' ',1)='PEBRUARI' OR SUBSTRING_INDEX(akhir,' ',1)='FEBRUARI') THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-02-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='MARET' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-03-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='APRIL' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-04-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='MEI' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-05-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='JUNI' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-06-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='JULI' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-07-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='AGUSTUS' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-08-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='SEPTEMBER' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-09-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='OKTOBER' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-10-01')
|
|
when (SUBSTRING_INDEX(akhir,' ',1)='NOPEMBER' OR SUBSTRING_INDEX(akhir,' ',1)='NOVEMBER') THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-11-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='DESEMBER' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-12-01')
|
|
end AS tgl_akhir
|
|
FROM tx_skpp ts WHERE akhir IS NOT NULL;
|
|
|
|
UPDATE tx_skpp INNER JOIN
|
|
(SELECT id, tgl_akhir FROM (
|
|
SELECT id,
|
|
case when SUBSTRING_INDEX(akhir,' ',1)='JANUARI' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-01-01')
|
|
when (SUBSTRING_INDEX(akhir,' ',1)='PEBRUARI' OR SUBSTRING_INDEX(akhir,' ',1)='FEBRUARI') THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-02-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='MARET' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-03-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='APRIL' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-04-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='MEI' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-05-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='JUNI' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-06-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='JULI' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-07-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='AGUSTUS' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-08-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='SEPTEMBER' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-09-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='OKTOBER' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-10-01')
|
|
when (SUBSTRING_INDEX(akhir,' ',1)='NOPEMBER' OR SUBSTRING_INDEX(akhir,' ',1)='NOVEMBER') THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-11-01')
|
|
when SUBSTRING_INDEX(akhir,' ',1)='DESEMBER' THEN CONCAT(SUBSTRING_INDEX(akhir,' ',-1),'-12-01')
|
|
end AS tgl_akhir
|
|
FROM tx_skpp ts
|
|
WHERE akhir IS NOT NULL
|
|
AND LENGTH(SUBSTRING_INDEX(akhir,' ',-1))=4
|
|
) xx) y
|
|
ON tx_skpp.id=y.id
|
|
SET tx_skpp.tgl_akhir=y.tgl_akhir;
|
|
|
|
UPDATE tx_skpp SET no_skpp = CONCAT(id_pen,'/SKPP/073.521');
|
|
UPDATE tx_skpp SET created_by='37a46f1e-2686-40e5-972c-1821446a9bd6';
|