Difference between revisions of "TIME KEEPING"
(→Check No. of leave credits) |
|||
| (8 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| − | + | [[Main_Page | Main Page]] > [[Application_Development_Team | Application Development Team]] > [[System_Support | System Support]] | |
| + | =='''Check No. of leave credits'''== | ||
//* TO CHECK THE LEAVE OF AN VIP EMPLOYEE PER CUTOFF*\\ | //* TO CHECK THE LEAVE OF AN VIP EMPLOYEE PER CUTOFF*\\ | ||
SELECT SUM(MHVVLLV) VL,SUM(MHVSLLV) SL FROM mypaydv.mhv2015 WHERE MHVNUMB = '20079331' AND DATE(MHVCDTE) = DATE('2015-10-16') | SELECT SUM(MHVVLLV) VL,SUM(MHVSLLV) SL FROM mypaydv.mhv2015 WHERE MHVNUMB = '20079331' AND DATE(MHVCDTE) = DATE('2015-10-16') | ||
| Line 10: | Line 11: | ||
//* TO CHECK THE ENTITLED LEAVE OF AN EMPLOYEE*\\ | //* TO CHECK THE ENTITLED LEAVE OF AN EMPLOYEE*\\ | ||
SELECT * FROM mypaydv.svv2015 WHERE svvnumb = '20079331' | SELECT * FROM mypaydv.svv2015 WHERE svvnumb = '20079331' | ||
| − | |||
| Line 21: | Line 21: | ||
== '''DELETE FPID''' == | == '''DELETE FPID''' == | ||
| − | |||
SELECT * FROM mypaydb.pytchro WHERE CROCRNO LIKE '%984%' | SELECT * FROM mypaydb.pytchro WHERE CROCRNO LIKE '%984%' | ||
SELECT * FROM mypaydb.fpid WHERE FPENUMB LIKE '%15000890' | SELECT * FROM mypaydb.fpid WHERE FPENUMB LIKE '%15000890' | ||
| Line 28: | Line 27: | ||
97004104 | 97004104 | ||
| − | |||
== '''Empinfo by hst''' == | == '''Empinfo by hst''' == | ||
| − | |||
DROP TABLE IF EXISTS oa.emp2015; | DROP TABLE IF EXISTS oa.emp2015; | ||
CREATE TABLE IF NOT EXISTS oa.emp2015 ( | CREATE TABLE IF NOT EXISTS oa.emp2015 ( | ||
| Line 53: | Line 50: | ||
UNION ALL | UNION ALL | ||
SELECT EMPNUMB,EMPLNAM,EMPFNAM,EMPMNAM,EMPMENO,EMPBDTE,EMPSXCD FROM mypmsdv.pmhempl | SELECT EMPNUMB,EMPLNAM,EMPFNAM,EMPMNAM,EMPMENO,EMPBDTE,EMPSXCD FROM mypmsdv.pmhempl | ||
| − | |||
| − | |||
| Line 75: | Line 70: | ||
GROUP BY HSTNUMB) bb WHERE aa.EMPNUMB = bb.HSTNUMB | GROUP BY HSTNUMB) bb WHERE aa.EMPNUMB = bb.HSTNUMB | ||
ORDER BY | ORDER BY | ||
| − | |||
== '''Get Employee's In and Out''' == | == '''Get Employee's In and Out''' == | ||
| − | |||
// RNF | // RNF | ||
| Line 93: | Line 86: | ||
select * from mypaydv.unibio2016v where fpid = '001' | select * from mypaydv.unibio2016v where fpid = '001' | ||
| − | + | == '''Selecting Specific Cutoff 52''' == | |
| − | == '''Selecting Specific Cutoff 52 | + | |
| − | + | ||
SELECT * FROM mypaydb.hst2015 WHERE SUBSTR(HSTCOFF,1,2)='01' AND HSTHOLD = 'Y' | SELECT * FROM mypaydb.hst2015 WHERE SUBSTR(HSTCOFF,1,2)='01' AND HSTHOLD = 'Y' | ||
| Line 102: | Line 93: | ||
/* if HSTHOLD = Y payment code (HSTPMCD) is automatically equal to C (Cash)*/ | /* if HSTHOLD = Y payment code (HSTPMCD) is automatically equal to C (Cash)*/ | ||
| − | + | == '''Consolidated Manhour and OT Of May''' == | |
| − | == '''Consolidated Manhour | + | |
| − | + | ||
SELECT * FROM oliver.emp2316 | SELECT * FROM oliver.emp2316 | ||
DROP TABLE IF EXISTS oa.emp2015; | DROP TABLE IF EXISTS oa.emp2015; | ||
| Line 333: | Line 322: | ||
AND (aa.HSTCOCD = '1' OR aa.HSTCOCD = '2' OR aa.HSTCOCD = '3' OR aa.HSTCOCD = '9' OR aa.HSTCOCD = 'A' OR aa.HSTCOCD = 'B' OR | AND (aa.HSTCOCD = '1' OR aa.HSTCOCD = '2' OR aa.HSTCOCD = '3' OR aa.HSTCOCD = '9' OR aa.HSTCOCD = 'A' OR aa.HSTCOCD = 'B' OR | ||
aa.HSTCOCD = '6' OR aa.HSTCOCD = '7' ) | aa.HSTCOCD = '6' OR aa.HSTCOCD = '7' ) | ||
| − | ORDER BY CONCAT(TRIM(cc.EMPLNAM),', ',TRIM(cc.EMPFNAM),' ',TRIM(cc. | + | ORDER BY CONCAT(TRIM(cc.EMPLNAM),', ',TRIM(cc.EMPFNAM),' ',TRIM(cc.EMPMN |
| − | + | ||
| − | + | ||
| − | + | ||
/* request 2015.05.05 */ | /* request 2015.05.05 */ | ||
| Line 559: | Line 545: | ||
AND (aa.HSTCOCD = '1' OR aa.HSTCOCD = '2' OR aa.HSTCOCD = '3' OR aa.HSTCOCD = '7') | AND (aa.HSTCOCD = '1' OR aa.HSTCOCD = '2' OR aa.HSTCOCD = '3' OR aa.HSTCOCD = '7') | ||
AND aa.HSTLCCD = 'MEY' | AND aa.HSTLCCD = 'MEY' | ||
| − | ORDER BY CONCAT(TRIM(cc.EMPLNAM),', ',TRIM(cc.EMPFNAM),' ',TRIM(cc.EMPMNAM)) | + | ORDER BY CONCAT(TRIM(cc.EMPLNAM),', ',TRIM(cc.EMPFNAM),' ',TRIM(cc.EMPMNAM)) |
| − | + | ||
| − | + | ||
== '''Deletion Of BIOMETRICS 52''' == | == '''Deletion Of BIOMETRICS 52''' == | ||
| Line 573: | Line 557: | ||
SELECT * FROM mypmsdb.pmtempl WHERE EMPNUMB = '20032387' | SELECT * FROM mypmsdb.pmtempl WHERE EMPNUMB = '20032387' | ||
| − | =='''ERROR | + | =='''ERROR in Deleting Efile'''== |
| − | + | ||
/*delete only in tables pmtsepa, pmhrfempl, pmhempl | /*delete only in tables pmtsepa, pmhrfempl, pmhempl | ||
| − | |||
SELECT * FROM mypmsdb.pmtsepa WHERE SEPNUMB = '20033924' | SELECT * FROM mypmsdb.pmtsepa WHERE SEPNUMB = '20033924' | ||
| Line 588: | Line 570: | ||
== '''Hold an Employee 52''' == | == '''Hold an Employee 52''' == | ||
SELECT * FROM mypmsdb.pmtempl WHERE emplnam = 'brillantes' | SELECT * FROM mypmsdb.pmtempl WHERE emplnam = 'brillantes' | ||
| − | |||
SELECT * FROM mypaydb.hst2015 WHERE HSTNUMB = '15000665' AND HSTCDTE = '2015-08-01' | SELECT * FROM mypaydb.hst2015 WHERE HSTNUMB = '15000665' AND HSTCDTE = '2015-08-01' | ||
| Line 600: | Line 581: | ||
UPDATE mypaydb.hst2015 SET HSTHOLD = 'Y',HSTPMCD = 'C', HSTBKCD = '' | UPDATE mypaydb.hst2015 SET HSTHOLD = 'Y',HSTPMCD = 'C', HSTBKCD = '' | ||
WHERE HSTNUMB = '15000665' AND DATE(HSTCDTE) = DATE('2015-08-01') | WHERE HSTNUMB = '15000665' AND DATE(HSTCDTE) = DATE('2015-08-01') | ||
| − | |||
== '''Deduct Special Holiday''' == | == '''Deduct Special Holiday''' == | ||
| Line 639: | Line 619: | ||
== '''DTRPROFLIST Report''' == | == '''DTRPROFLIST Report''' == | ||
SELECT * FROM mypaydb.dt2016 WHERE dtrnumb = "13000668" | SELECT * FROM mypaydb.dt2016 WHERE dtrnumb = "13000668" | ||
| − | |||
| − | |||
SELECT | SELECT | ||
| Line 663: | Line 641: | ||
LEFT OUTER JOIN mypaydb.un2016 ee ON aa.DTRNUMB = ee.UNTNUMB AND aa.DTRDATE = ee.UNTDATE | LEFT OUTER JOIN mypaydb.un2016 ee ON aa.DTRNUMB = ee.UNTNUMB AND aa.DTRDATE = ee.UNTDATE | ||
WHERE aa.DTRNUMB = '14003945' AND aa.DTRDATE BETWEEN DATE('2016-01-01') AND DATE('2016-01-31') | WHERE aa.DTRNUMB = '14003945' AND aa.DTRDATE BETWEEN DATE('2016-01-01') AND DATE('2016-01-31') | ||
| − | |||
=='''ESSI LEAVE ADJUSTMENT (2016-02-16)'''== | =='''ESSI LEAVE ADJUSTMENT (2016-02-16)'''== | ||
| − | |||
SELECT * FROM oa.2016_ESSI_DED_20160216 | SELECT * FROM oa.2016_ESSI_DED_20160216 | ||
| Line 693: | Line 669: | ||
=='''Movement of Employee 52 (position)'''== | =='''Movement of Employee 52 (position)'''== | ||
| − | |||
//* for those still active employees*// | //* for those still active employees*// | ||
SELECT * FROM mypmsdb.`pmtemve` WHERE emvnumb = '20017046' | SELECT * FROM mypmsdb.`pmtemve` WHERE emvnumb = '20017046' | ||
| Line 715: | Line 690: | ||
SELECT * FROM mypmsdb.`pmtemve` WHERE emvnumb = '20017046' | SELECT * FROM mypmsdb.`pmtemve` WHERE emvnumb = '20017046' | ||
SELECT * FROM mypmsdb.pmmposn WHERE POSPSCD = '136' | SELECT * FROM mypmsdb.pmmposn WHERE POSPSCD = '136' | ||
| + | |||
| + | [[Main_Page | Main Page]] > [[Application_Development_Team | Application Development Team]] > [[System_Support | System Support]] | ||
Latest revision as of 09:15, 1 September 2016
Main Page > Application Development Team > System Support
Contents
- 1 Check No. of leave credits
- 2 DELETE FPID
- 3 Empinfo by hst
- 4 Get Employee's In and Out
- 5 Selecting Specific Cutoff 52
- 6 Consolidated Manhour and OT Of May
- 7 Deletion Of BIOMETRICS 52
- 8 ERROR in Deleting Efile
- 9 Hold an Employee 52
- 10 Deduct Special Holiday
- 11 DTRPROFLIST Report
- 12 ESSI LEAVE ADJUSTMENT (2016-02-16)
- 13 Movement of Employee 52 (position)
Check No. of leave credits
//* TO CHECK THE LEAVE OF AN VIP EMPLOYEE PER CUTOFF*\\ SELECT SUM(MHVVLLV) VL,SUM(MHVSLLV) SL FROM mypaydv.mhv2015 WHERE MHVNUMB = '20079331' AND DATE(MHVCDTE) = DATE('2015-10-16')
//* TO CHECK THE PREVIOUS LEAVE OF AN EMPLOYEE (RNF LEAVE) BEFORE TRANSFERRING TO VIP*\\
SELECT * FROM mypaydv.slvlrecon201 WHERE LVAB_ENUMB = '20079331'
//* TO CHECK THE ENTITLED LEAVE OF AN EMPLOYEE*\\ SELECT * FROM mypaydv.svv2015 WHERE svvnumb = '20079331'
//*CHECKED FOR THE ADJUSTMENT OF LEAVE*\\
SELECT SUM(VL) AS 'VL' FROM(
SELECT LOANUMB,SUM(LOAVLRM) AS 'VL' FROM mypaydv.loamgr2015 GROUP BY LOANUMB
UNION ALL
SELECT LOANUMB,SUM(LOAVLRM) AS 'VL' FROM mypaydv.loasup2015 GROUP BY LOANUMB) a
WHERE LOANUMB = '20079331'
DELETE FPID
SELECT * FROM mypaydb.pytchro WHERE CROCRNO LIKE '%984%' SELECT * FROM mypaydb.fpid WHERE FPENUMB LIKE '%15000890'
SELECT * FROM mypmsdb.pmtempl WHERE empnumb = '15003283'
97004104
Empinfo by hst
DROP TABLE IF EXISTS oa.emp2015; CREATE TABLE IF NOT EXISTS oa.emp2015 ( EMPNUMB VARCHAR(15), EMPLNAM VARCHAR(30), EMPFNAM VARCHAR(30), EMPMNAM VARCHAR(30), EMPMENO VARCHAR(30), EMPBDTE DATE, EMPSXCD VARCHAR(10), UNIQUE KEY `EMPNUMB` (`EMPNUMB`) ) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT IGNORE oa.emp2015
SELECT EMPNUMB,EMPLNAM,EMPFNAM,EMPMNAM,EMPMENO,EMPBDTE,EMPSXCD FROM mypmsdb.pmtempl
UNION ALL
SELECT EMPNUMB,EMPLNAM,EMPFNAM,EMPMNAM,EMPMENO,EMPBDTE,EMPSXCD FROM mypmsdv.pmtempl
UNION ALL
SELECT EMPNUMB,EMPLNAM,EMPFNAM,EMPMNAM,EMPMENO,EMPBDTE,EMPSXCD FROM mypmsdb.pmhempl
UNION ALL
SELECT EMPNUMB,EMPLNAM,EMPFNAM,EMPMNAM,EMPMENO,EMPBDTE,EMPSXCD FROM mypmsdv.pmhempl
SELECT TRIM(aa.EMPNUMB) EMPLOYEENUMBER,
TRIM(aa.EMPLNAM) LASTNAME,
TRIM(aa.EMPFNAM) FIRSTNAME,
TRIM(aa.EMPMNAM) MIDDLENAME,
TRIM(aa.EMPMENO) PHILHEALTHNUMBER,
TRIM(aa.EMPBDTE) DATEOFBIRTH,
TRIM(aa.EMPSXCD) SEX
FROM oa.emp2015 aa,
(SELECT HSTNUMB FROM mypaydb.hst2013 WHERE SUBSTR(hstcoff,1,2) = '02' AND HSTCOCD = '7' AND HSTMEDE > 0 GROUP BY HSTNUMB UNION SELECT HSTNUMB FROM mypaydv.hst2013 WHERE SUBSTR(hstcoff,1,2) = '02' AND HSTCOCD = '7' AND HSTMEDE > 0 GROUP BY HSTNUMB) bb WHERE aa.EMPNUMB = bb.HSTNUMB ORDER BY
Get Employee's In and Out
// RNF
Select * from mypaydb.fpid where fpenumb = '15002535'
Select * from mypaydb.mst_attlogs where fp_id = '1048' and fp_trxdate >= '2016-05-19'
//VIP
select * from mypaydv.fpid where fpenumb = '13003743'
select * from mypaydv.unibio2016v where fpid = '001'
Selecting Specific Cutoff 52
SELECT * FROM mypaydb.hst2015 WHERE SUBSTR(HSTCOFF,1,2)='01' AND HSTHOLD = 'Y'
SELECT * FROM mypaydb.hst2015 WHERE SUBSTR(HSTCOFF,1,2)='01' AND HSTPMCD = 'A' AND HSTBKCD = '064'
/* if HSTHOLD = Y payment code (HSTPMCD) is automatically equal to C (Cash)*/
Consolidated Manhour and OT Of May
SELECT * FROM oliver.emp2316 DROP TABLE IF EXISTS oa.emp2015; CREATE TABLE IF NOT EXISTS oa.emp2015 ( EMPNUMB VARCHAR(15), EMPLNAM VARCHAR(30), EMPFNAM VARCHAR(30), EMPMNAM VARCHAR(30), UNIQUE KEY `EMPNUMB` (`EMPNUMB`) ) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT IGNORE oa.emp2015 SELECT EMPNUMB,EMPLNAM,EMPFNAM,EMPMNAM FROM mypmsdb.pmtempl UNION ALL SELECT EMPNUMB,EMPLNAM,EMPFNAM,EMPMNAM FROM mypmsdb.pmhempl UNION ALL SELECT EMPNUMB,EMPLNAM,EMPFNAM,EMPMNAM FROM mypmsdv.pmhempl UNION ALL SELECT EMPNUMB,EMPLNAM,EMPFNAM,EMPMNAM FROM mypmsdv.pmtempl
SELECT IFNULL((SELECT CMPNAME FROM mypmsdb.pmmcmpy WHERE CMPCOCD = aa.HSTCOCD LIMIT 1),' ') `Company`, IFNULL((SELECT DEPNAME FROM mypmsdb.pmmdept WHERE DEPDPCD = aa.HSTDPCD LIMIT 1),' ') `Department`, CONCAT(TRIM(cc.EMPLNAM),', ',TRIM(cc.EMPFNAM),' ',TRIM(cc.EMPMNAM)) `Emp Name`,aa.HSTMPAY `Basic Rate`,aa.HSTMALL `Allowance`,aa.HSTESCD `ESCD`,oa.*, IF(aa.HSTPCCD = '1','WEEKLY','MONTHLY') `EClass`
FROM mypaydb.hst2013 aa,
(SELECT MHRNUMB `Employee No.`, MHRCDTE `CutOff Date`, MHRTRDY `Tardy`, MHRUTME `Undertime`, MHRSLLV `Filed SL`, MHRVLLV `Filed VL`, MHRULOA `Unpaid LOA`, MHRWOLV `Wihout LOA`, MHRDLOA `Disapproved`, MHROTHR `Ithers`, MHRDAYS `No. of Days`, MHRESCD `Employment Status`, MHRRRD1 `Regular Hours`, MHRRRO1 `Regular OT`, MHRNRD1 `Night Shift Hours`, MHRNRO1 `Night Shift OT`, MHRRRD2 `Rest Day`, MHRRRO2 `Rest Day OT`, MHRNRO2 `Rest Day Night Shift OT`, MHRRSD1 `Special Holiday Regular Hours`, MHRRSO1 `Special Holiday Regular OT`, MHRNSD1 `Special Holiday Night Shift`, MHRRSD2 `Special Holiday Rest Day`, MHRRSO2 `Special Holiday Rest Day OT`, MHRRLD1 `Legal Holiday Regular Hours`, MHRRLO1 `Legal Holiday OT`, MHRNLD1 `Legal Holiday Night Shift Hours`, MHRRLD2 `Legal Holiday Rest Day Hours`, MHRRLO2 `Legal Holiday Rest Day OT`, MHRNLO1 `Legal Holiday Night Shift Hours OT`, MHRNLD2 `Legal Holiday Night Shift Rest Day Hours`, MHRNLO2 `Legal Holiday Night Shift Rest Day OT`, MHRNRD2 `Night Shift Rest Day`, MHRLHOL `Legal Holiday`, MHRSHOL `Special Holiday`, MHRRLEG `Rest Day Legal Holiday`, MHRRSPC `Rest Day Special Holiday`, MHRFPER `Field Personnel`, MHRADSL `SL Adjustment`, MHRADVL `VL Adjustment`, MHRDDSL `SL Deduction`, MHRDDVL `VL Deduction`, MHRADFL `Faterl Leave Adjustment` FROM mypaydb.mhr2013 UNION ALL
SELECT MHWNUMB `Employee No.`, MHWCDTE `CutOff Date`, MHWTRDY `Tardy`, MHWUTME `Undertime`, MHWSLLV `Filed SL`, MHWVLLV `Filed VL`, MHWULOA `Unpaid LOA`, MHWWOLV `Wihout LOA`, MHWDLOA `Disapproved`, MHWOTHR `Ithers`, MHWDAYS `No. of Days`, MHWESCD `Employment Status`, MHWRRD1 `Regular Hours`, MHWRRO1 `Regular OT`, MHWNRD1 `Night Shift Hours`, MHWNRO1 `Night Shift OT`, MHWRRD2 `Rest Day`, MHWRRO2 `Rest Day OT`, MHWNRO2 `Rest Day Night Shift OT`, MHWRSD1 `Special Holiday Regular Hours`, MHWRSO1 `Special Holiday Regular OT`, MHWNSD1 `Special Holiday Night Shift`, MHWRSD2 `Special Holiday Rest Day`, MHWRSO2 `Special Holiday Rest Day OT`, MHWRLD1 `Legal Holiday Regular Hours`, MHWRLO1 `Legal Holiday OT`, MHWNLD1 `Legal Holiday Night Shift Hours`, MHWRLD2 `Legal Holiday Rest Day Hours`, MHWRLO2 `Legal Holiday Rest Day OT`, MHWNLO1 `Legal Holiday Night Shift Hours OT`, MHWNLD2 `Legal Holiday Night Shift Rest Day Hours`, MHWNLO2 `Legal Holiday Night Shift Rest Day OT`, MHWNRD2 `Night Shift Rest Day`, MHWLHOL `Legal Holiday`, MHWSHOL `Special Holiday`, MHWRLEG `Rest Day Legal Holiday`, MHWRSPC `Rest Day Special Holiday`, MHWFPER `Field Personnel`, MHWADSL `SL Adjustment`, MHWADVL `VL Adjustment`, MHWDDSL `SL Deduction`, MHWDDVL `VL Deduction`, MHWADFL `Faterl Leave Adjustment` FROM mypaydb.mhw2013 ) oa,oa.emp2014 cc WHERE aa.HSTNUMB = oa.`Employee No.` AND aa.HSTNUMB = cc.EMPNUMB AND DATE(aa.HSTCDTE) = DATE(oa.`CutOff Date`) /* AND (SUBSTR(aa.HSTCOFF,1,2) = '01' OR SUBSTR(aa.HSTCOFF,1,2) = '02') */ AND (aa.HSTCOCD = '1' OR aa.HSTCOCD = '2' OR aa.HSTCOCD = '3' OR aa.HSTCOCD = '9' OR aa.HSTCOCD = 'A' OR aa.HSTCOCD = 'B' OR aa.HSTCOCD = '6' OR aa.HSTCOCD = '7' )
ORDER BY CONCAT(TRIM(cc.EMPLNAM),', ',TRIM(cc.EMPFNAM),' ',TRIM(cc.EMPMNAM))
/* request 2014.05.07 */ SELECT IFNULL((SELECT CMPNAME FROM mypmsdb.pmmcmpy WHERE CMPCOCD = aa.HSTCOCD LIMIT 1),' ') `Company`, IFNULL((SELECT DEPNAME FROM mypmsdb.pmmdept WHERE DEPDPCD = aa.HSTDPCD LIMIT 1),' ') `Department`, CONCAT(TRIM(cc.EMPLNAM),', ',TRIM(cc.EMPFNAM),' ',TRIM(cc.EMPMNAM)) `Emp Name`,aa.HSTMPAY `Basic Rate`,aa.HSTMALL `Allowance`,aa.HSTESCD `ESCD`,oa.*, IF(aa.HSTPCCD = '1','WEEKLY','MONTHLY') `EClass`
FROM mypaydb.hst2014 aa,
(SELECT MHRNUMB `Employee No.`, MHRCDTE `CutOff Date`, MHRTRDY `Tardy`, MHRUTME `Undertime`, MHRSLLV `Filed SL`, MHRVLLV `Filed VL`, MHRULOA `Unpaid LOA`, MHRWOLV `Wihout LOA`, MHRDLOA `Disapproved`, MHROTHR `Ithers`, MHRDAYS `No. of Days`, MHRESCD `Employment Status`, MHRRRD1 `Regular Hours`, MHRRRO1 `Regular OT`, MHRNRD1 `Night Shift Hours`, MHRNRO1 `Night Shift OT`, MHRRRD2 `Rest Day`, MHRRRO2 `Rest Day OT`, MHRNRO2 `Rest Day Night Shift OT`, MHRRSD1 `Special Holiday Regular Hours`, MHRRSO1 `Special Holiday Regular OT`, MHRNSD1 `Special Holiday Night Shift`, MHRRSD2 `Special Holiday Rest Day`, MHRRSO2 `Special Holiday Rest Day OT`, MHRRLD1 `Legal Holiday Regular Hours`, MHRRLO1 `Legal Holiday OT`, MHRNLD1 `Legal Holiday Night Shift Hours`, MHRRLD2 `Legal Holiday Rest Day Hours`, MHRRLO2 `Legal Holiday Rest Day OT`, MHRNLO1 `Legal Holiday Night Shift Hours OT`, MHRNLD2 `Legal Holiday Night Shift Rest Day Hours`, MHRNLO2 `Legal Holiday Night Shift Rest Day OT`, MHRNRD2 `Night Shift Rest Day`, MHRLHOL `Legal Holiday`, MHRSHOL `Special Holiday`, MHRRLEG `Rest Day Legal Holiday`, MHRRSPC `Rest Day Special Holiday`, MHRFPER `Field Personnel`, MHRADSL `SL Adjustment`, MHRADVL `VL Adjustment`, MHRDDSL `SL Deduction`, MHRDDVL `VL Deduction`, MHRADFL `Faterl Leave Adjustment` FROM mypaydb.mhr2014 UNION ALL
SELECT MHWNUMB `Employee No.`, MHWCDTE `CutOff Date`, MHWTRDY `Tardy`, MHWUTME `Undertime`, MHWSLLV `Filed SL`, MHWVLLV `Filed VL`, MHWULOA `Unpaid LOA`, MHWWOLV `Wihout LOA`, MHWDLOA `Disapproved`, MHWOTHR `Ithers`, MHWDAYS `No. of Days`, MHWESCD `Employment Status`, MHWRRD1 `Regular Hours`, MHWRRO1 `Regular OT`, MHWNRD1 `Night Shift Hours`, MHWNRO1 `Night Shift OT`, MHWRRD2 `Rest Day`, MHWRRO2 `Rest Day OT`, MHWNRO2 `Rest Day Night Shift OT`, MHWRSD1 `Special Holiday Regular Hours`, MHWRSO1 `Special Holiday Regular OT`, MHWNSD1 `Special Holiday Night Shift`, MHWRSD2 `Special Holiday Rest Day`, MHWRSO2 `Special Holiday Rest Day OT`, MHWRLD1 `Legal Holiday Regular Hours`, MHWRLO1 `Legal Holiday OT`, MHWNLD1 `Legal Holiday Night Shift Hours`, MHWRLD2 `Legal Holiday Rest Day Hours`, MHWRLO2 `Legal Holiday Rest Day OT`, MHWNLO1 `Legal Holiday Night Shift Hours OT`, MHWNLD2 `Legal Holiday Night Shift Rest Day Hours`, MHWNLO2 `Legal Holiday Night Shift Rest Day OT`, MHWNRD2 `Night Shift Rest Day`, MHWLHOL `Legal Holiday`, MHWSHOL `Special Holiday`, MHWRLEG `Rest Day Legal Holiday`, MHWRSPC `Rest Day Special Holiday`, MHWFPER `Field Personnel`, MHWADSL `SL Adjustment`, MHWADVL `VL Adjustment`, MHWDDSL `SL Deduction`, MHWDDVL `VL Deduction`, MHWADFL `Faterl Leave Adjustment` FROM mypaydb.mhw2014 ) oa,oa.emp2014 cc WHERE aa.HSTNUMB = oa.`Employee No.` AND aa.HSTNUMB = cc.EMPNUMB AND DATE(aa.HSTCDTE) = DATE(oa.`CutOff Date`) AND SUBSTR(aa.HSTCOFF,1,2) = '04' AND (aa.HSTCOCD = '1' OR aa.HSTCOCD = '2' OR aa.HSTCOCD = '3' OR aa.HSTCOCD = '9' OR aa.HSTCOCD = 'A' OR aa.HSTCOCD = 'B' OR aa.HSTCOCD = '6' OR aa.HSTCOCD = '7' )
ORDER BY CONCAT(TRIM(cc.EMPLNAM),', ',TRIM(cc.EMPFNAM),' ',TRIM(cc.EMPMN
/* request 2015.05.05 */
SELECT * FROM oa.emp2015;
SELECT IFNULL((SELECT CMPNAME FROM mypmsdb.pmmcmpy WHERE CMPCOCD = aa.HSTCOCD LIMIT 1),' ') `Company`, IFNULL((SELECT DEPNAME FROM mypmsdb.pmmdept WHERE DEPDPCD = aa.HSTDPCD LIMIT 1),' ') `Department`, CONCAT(TRIM(cc.EMPLNAM),', ',TRIM(cc.EMPFNAM),' ',TRIM(cc.EMPMNAM)) `Emp Name`,aa.HSTMPAY `Basic Rate`,aa.HSTMALL `Allowance`,aa.HSTESCD `ESCD`,oa.*, CONCAT('\,`Employee No.`) `ENUMB`, IF(aa.HSTPCCD = '1','WEEKLY','MONTHLY') `EClass`
FROM mypaydb.hst2015 aa,
(SELECT MHRNUMB `Employee No.`, MHRCDTE `CutOff Date`, MHRTRDY `Tardy`, MHRUTME `Undertime`, MHRSLLV `Filed SL`, MHRVLLV `Filed VL`, MHRULOA `Unpaid LOA`, MHRWOLV `Wihout LOA`, MHRDLOA `Disapproved`, MHROTHR `Ithers`, MHRDAYS `No. of Days`, MHRESCD `Employment Status`, MHRRRD1 `Regular Hours`, MHRRRO1 `Regular OT`, MHRNRD1 `Night Shift Hours`, MHRNRO1 `Night Shift OT`, MHRRRD2 `Rest Day`, MHRRRO2 `Rest Day OT`, MHRNRO2 `Rest Day Night Shift OT`, MHRRSD1 `Special Holiday Regular Hours`, MHRRSO1 `Special Holiday Regular OT`, MHRNSD1 `Special Holiday Night Shift`, MHRRSD2 `Special Holiday Rest Day`, MHRRSO2 `Special Holiday Rest Day OT`, MHRRLD1 `Legal Holiday Regular Hours`, MHRRLO1 `Legal Holiday OT`, MHRNLD1 `Legal Holiday Night Shift Hours`, MHRRLD2 `Legal Holiday Rest Day Hours`, MHRRLO2 `Legal Holiday Rest Day OT`, MHRNLO1 `Legal Holiday Night Shift Hours OT`, MHRNLD2 `Legal Holiday Night Shift Rest Day Hours`, MHRNLO2 `Legal Holiday Night Shift Rest Day OT`, MHRNRD2 `Night Shift Rest Day`, MHRLHOL `Legal Holiday`, MHRSHOL `Special Holiday`, MHRRLEG `Rest Day Legal Holiday`, MHRRSPC `Rest Day Special Holiday`, MHRFPER `Field Personnel`, MHRADSL `SL Adjustment`, MHRADVL `VL Adjustment`, MHRDDSL `SL Deduction`, MHRDDVL `VL Deduction`, MHRADFL `Faternity Leave Adjustment` FROM mypaydb.mhr2015 UNION ALL
SELECT MHWNUMB `Employee No.`, MHWCDTE `CutOff Date`, MHWTRDY `Tardy`, MHWUTME `Undertime`, MHWSLLV `Filed SL`, MHWVLLV `Filed VL`, MHWULOA `Unpaid LOA`, MHWWOLV `Wihout LOA`, MHWDLOA `Disapproved`, MHWOTHR `Ithers`, MHWDAYS `No. of Days`, MHWESCD `Employment Status`, MHWRRD1 `Regular Hours`, MHWRRO1 `Regular OT`, MHWNRD1 `Night Shift Hours`, MHWNRO1 `Night Shift OT`, MHWRRD2 `Rest Day`, MHWRRO2 `Rest Day OT`, MHWNRO2 `Rest Day Night Shift OT`, MHWRSD1 `Special Holiday Regular Hours`, MHWRSO1 `Special Holiday Regular OT`, MHWNSD1 `Special Holiday Night Shift`, MHWRSD2 `Special Holiday Rest Day`, MHWRSO2 `Special Holiday Rest Day OT`, MHWRLD1 `Legal Holiday Regular Hours`, MHWRLO1 `Legal Holiday OT`, MHWNLD1 `Legal Holiday Night Shift Hours`, MHWRLD2 `Legal Holiday Rest Day Hours`, MHWRLO2 `Legal Holiday Rest Day OT`, MHWNLO1 `Legal Holiday Night Shift Hours OT`, MHWNLD2 `Legal Holiday Night Shift Rest Day Hours`, MHWNLO2 `Legal Holiday Night Shift Rest Day OT`, MHWNRD2 `Night Shift Rest Day`, MHWLHOL `Legal Holiday`, MHWSHOL `Special Holiday`, MHWRLEG `Rest Day Legal Holiday`, MHWRSPC `Rest Day Special Holiday`, MHWFPER `Field Personnel`, MHWADSL `SL Adjustment`, MHWADVL `VL Adjustment`, MHWDDSL `SL Deduction`, MHWDDVL `VL Deduction`, MHWADFL `Faternity Leave Adjustment` FROM mypaydb.mhw2015 ) oa,oa.emp2015 cc WHERE aa.HSTNUMB = oa.`Employee No.` AND aa.HSTNUMB = cc.EMPNUMB AND DATE(aa.HSTCDTE) = DATE(oa.`CutOff Date`) AND aa.HSTCOFF = '041' AND aa.HSTPCCD = '2' AND (aa.HSTCOCD = '1' OR aa.HSTCOCD = '2' OR aa.HSTCOCD = '3' OR aa.HSTCOCD = '9' OR aa.HSTCOCD = 'A' OR aa.HSTCOCD = 'B' OR aa.HSTCOCD = '6' OR aa.HSTCOCD = '7' )
ORDER BY CONCAT(TRIM(cc.EMPLNAM),', ',TRIM(cc.EMPFNAM),' ',TRIM(cc.EMPMNAM))
SELECT * FROM mypmsdb.pmtmwee LIMIT 200
/* request 2015.09.10 aileen meyc
- /
SELECT * FROM oa.emp2015;
SELECT IFNULL((SELECT CMPNAME FROM mypmsdb.pmmcmpy WHERE CMPCOCD = aa.HSTCOCD LIMIT 1),' ') `Company`, IFNULL((SELECT DEPNAME FROM mypmsdb.pmmdept WHERE DEPDPCD = aa.HSTDPCD LIMIT 1),' ') `Department`, CONCAT(TRIM(cc.EMPLNAM),', ',TRIM(cc.EMPFNAM),' ',TRIM(cc.EMPMNAM)) `Emp Name`,aa.HSTMPAY `Basic Rate`,aa.HSTMALL `Allowance`,aa.HSTESCD `ESCD`,oa.*, CONCAT('\,`Employee No.`) `ENUMB`, IF(aa.HSTPCCD = '1','WEEKLY','MONTHLY') `EClass`
FROM mypaydb.hst2014 aa,
(SELECT MHRNUMB `Employee No.`, MHRCDTE `CutOff Date`, MHRTRDY `Tardy`, MHRUTME `Undertime`, MHRSLLV `Filed SL`, MHRVLLV `Filed VL`, MHRULOA `Unpaid LOA`, MHRWOLV `Wihout LOA`, MHRDLOA `Disapproved`, MHROTHR `Ithers`, MHRDAYS `No. of Days`, MHRESCD `Employment Status`, MHRRRD1 `Regular Hours`, MHRRRO1 `Regular OT`, MHRNRD1 `Night Shift Hours`, MHRNRO1 `Night Shift OT`, MHRRRD2 `Rest Day`, MHRRRO2 `Rest Day OT`, MHRNRO2 `Rest Day Night Shift OT`, MHRRSD1 `Special Holiday Regular Hours`, MHRRSO1 `Special Holiday Regular OT`, MHRNSD1 `Special Holiday Night Shift`, MHRRSD2 `Special Holiday Rest Day`, MHRRSO2 `Special Holiday Rest Day OT`, MHRRLD1 `Legal Holiday Regular Hours`, MHRRLO1 `Legal Holiday OT`, MHRNLD1 `Legal Holiday Night Shift Hours`, MHRRLD2 `Legal Holiday Rest Day Hours`, MHRRLO2 `Legal Holiday Rest Day OT`, MHRNLO1 `Legal Holiday Night Shift Hours OT`, MHRNLD2 `Legal Holiday Night Shift Rest Day Hours`, MHRNLO2 `Legal Holiday Night Shift Rest Day OT`, MHRNRD2 `Night Shift Rest Day`, MHRLHOL `Legal Holiday`, MHRSHOL `Special Holiday`, MHRRLEG `Rest Day Legal Holiday`, MHRRSPC `Rest Day Special Holiday`, MHRFPER `Field Personnel`, MHRADSL `SL Adjustment`, MHRADVL `VL Adjustment`, MHRDDSL `SL Deduction`, MHRDDVL `VL Deduction`, MHRADFL `Faternity Leave Adjustment` FROM mypaydb.mhr2014 UNION ALL
SELECT MHWNUMB `Employee No.`, MHWCDTE `CutOff Date`, MHWTRDY `Tardy`, MHWUTME `Undertime`, MHWSLLV `Filed SL`, MHWVLLV `Filed VL`, MHWULOA `Unpaid LOA`, MHWWOLV `Wihout LOA`, MHWDLOA `Disapproved`, MHWOTHR `Ithers`, MHWDAYS `No. of Days`, MHWESCD `Employment Status`, MHWRRD1 `Regular Hours`, MHWRRO1 `Regular OT`, MHWNRD1 `Night Shift Hours`, MHWNRO1 `Night Shift OT`, MHWRRD2 `Rest Day`, MHWRRO2 `Rest Day OT`, MHWNRO2 `Rest Day Night Shift OT`, MHWRSD1 `Special Holiday Regular Hours`, MHWRSO1 `Special Holiday Regular OT`, MHWNSD1 `Special Holiday Night Shift`, MHWRSD2 `Special Holiday Rest Day`, MHWRSO2 `Special Holiday Rest Day OT`, MHWRLD1 `Legal Holiday Regular Hours`, MHWRLO1 `Legal Holiday OT`, MHWNLD1 `Legal Holiday Night Shift Hours`, MHWRLD2 `Legal Holiday Rest Day Hours`, MHWRLO2 `Legal Holiday Rest Day OT`, MHWNLO1 `Legal Holiday Night Shift Hours OT`, MHWNLD2 `Legal Holiday Night Shift Rest Day Hours`, MHWNLO2 `Legal Holiday Night Shift Rest Day OT`, MHWNRD2 `Night Shift Rest Day`, MHWLHOL `Legal Holiday`, MHWSHOL `Special Holiday`, MHWRLEG `Rest Day Legal Holiday`, MHWRSPC `Rest Day Special Holiday`, MHWFPER `Field Personnel`, MHWADSL `SL Adjustment`, MHWADVL `VL Adjustment`, MHWDDSL `SL Deduction`, MHWDDVL `VL Deduction`, MHWADFL `Faternity Leave Adjustment` FROM mypaydb.mhw2014 ) oa,oa.emp2014 cc WHERE aa.HSTNUMB = oa.`Employee No.` AND aa.HSTNUMB = cc.EMPNUMB AND DATE(aa.HSTCDTE) = DATE(oa.`CutOff Date`) AND SUBSTR(aa.HSTCOFF,1,2) = '09' AND (aa.HSTCOCD = '1' OR aa.HSTCOCD = '2' OR aa.HSTCOCD = '3' OR aa.HSTCOCD = '7') AND aa.HSTLCCD = 'MEY'
ORDER BY CONCAT(TRIM(cc.EMPLNAM),', ',TRIM(cc.EMPFNAM),' ',TRIM(cc.EMPMNAM))
Deletion Of BIOMETRICS 52
SELECT * FROM mypaydb.pytchro WHERE CROCRNO LIKE '%1040%' SELECT * FROM mypaydb.fpid WHERE FPENUMB LIKE '%97004104'
97004104
UPDATE mypaydb.pytchro SET CROCRNO = 'X00000001040' WHERE CRONUMB = '97004104'
SELECT * FROM mypmsdb.pmtempl WHERE EMPNUMB = '20032387'
ERROR in Deleting Efile
/*delete only in tables pmtsepa, pmhrfempl, pmhempl
SELECT * FROM mypmsdb.pmtsepa WHERE SEPNUMB = '20033924' SELECT * FROM mypmsdb.pmtempl WHERE EMPNUMB = '20033924'
SELECT * FROM mypmsdb.`pmhrfempl` WHERE EMPNUMB = '20033924' SELECT * FROM mypmsdb.`pmhempl` WHERE EMPNUMB = '20033924'
DELETE FROM mypmsdb.pmtsepa WHERE SEPNUMB = '20033924'
Hold an Employee 52
SELECT * FROM mypmsdb.pmtempl WHERE emplnam = 'brillantes'
SELECT * FROM mypaydb.hst2015 WHERE HSTNUMB = '15000665' AND HSTCDTE = '2015-08-01'
UPDATE mypaydb.hst2015 SET HSTBKCD = ' ' WHERE HSTNUMB = '15000665' AND DATE(HSTCDTE) = DATE('2015-08-01')
UPDATE `2015mosppe`.`08012015hstf` SET HSTHOLD = 'Y',HSTPMCD = 'C', HSTBKCD = WHERE HSTNUMB = '15000665' AND DATE(HSTCDTE) = DATE('2015-08-01')
UPDATE mypaydb.hst2015 SET HSTHOLD = 'Y',HSTPMCD = 'C', HSTBKCD =
WHERE HSTNUMB = '15000665' AND DATE(HSTCDTE) = DATE('2015-08-01')
Deduct Special Holiday
CREATE TABLE IF NOT EXISTS oa.2015_SPPE_DED_20151116
/*without remaining hrs*/
SELECT bb.*,aa.* FROM mypaydb.mhr2015 aa,oa.`2015_SPPE_DED_20151116` bb WHERE aa.MHRNUMB = bb.EMP_NO AND DATE(aa.MHRCDTE) = DATE('2015-11-16') AND REM_HRS != 0
SELECT * FROM oa.2015_SPPE_DED_20151116
UPDATE mypaydb.mhr2015 aa,oa.`2015_SPPE_DED_20151116` bb SET aa.MHROTHR = (aa.MHROTHR + (aa.MHRSHOL - bb.REM_HRS))
WHERE aa.MHRNUMB = bb.EMP_NO AND DATE(aa.MHRCDTE) = DATE('2015-11-16') AND REM_HRS != 0
UPDATE mypaydb.mhr2015 aa,oa.`2015_SPPE_DED_20151116` bb SET aa.MHRSHOL = bb.REM_HRS
WHERE aa.MHRNUMB = bb.EMP_NO AND DATE(aa.MHRCDTE) = DATE('2015-11-16') AND REM_HRS != 0
/*with remaining hrs*/
SELECT bb.*,aa.* FROM mypaydb.mhr2015 aa,oa.`2015_SPPE_DED_20151116` bb WHERE aa.MHRNUMB = bb.EMP_NO AND DATE(aa.MHRCDTE) = DATE('2015-11-16') AND REM_HRS != 0
SELECT * FROM oa.2015_SPPE_DED_20151116
UPDATE mypaydb.mhr2015 aa,oa.`2015_SPPE_DED_20151116` bb SET aa.MHROTHR = (aa.MHROTHR + (aa.MHRSHOL - bb.REM_HRS))
WHERE aa.MHRNUMB = bb.EMP_NO AND DATE(aa.MHRCDTE) = DATE('2015-11-16') AND REM_HRS != 0
UPDATE mypaydb.mhr2015 aa,oa.`2015_SPPE_DED_20151116` bb SET aa.MHRSHOL = bb.REM_HRS
WHERE aa.MHRNUMB = bb.EMP_NO AND DATE(aa.MHRCDTE) = DATE('2015-11-16') AND REM_HRS != 0
DTRPROFLIST Report
SELECT * FROM mypaydb.dt2016 WHERE dtrnumb = "13000668"
SELECT aa.DTRNUMB , aa.DTRDATE,
(CASE WHEN aa.DTRAMIN != '0000' THEN CONCAT(SUBSTR(aa.DTRAMIN,1,2),':',SUBSTR(aa.DTRAMIN,3,2)) ELSE END) DTRAMIN,
(CASE WHEN aa.DTRAMOT != '0000' THEN CONCAT(SUBSTR(aa.DTRAMOT,1,2),':',SUBSTR(aa.DTRAMOT,3,2)) ELSE END) DTRAMOT, (CASE WHEN aa.DTRPMIN != '0000' THEN CONCAT(SUBSTR(aa.DTRPMIN,1,2),':',SUBSTR(aa.DTRPMIN,3,2)) ELSE END) DTRPMIN, (CASE WHEN aa.DTRPMOT != '0000' THEN CONCAT(SUBSTR(aa.DTRPMOT,1,2),':',SUBSTR(aa.DTRPMOT,3,2)) ELSE END) DTRPMOT, (CASE WHEN aa.DTR_AMI != '0000' THEN CONCAT(SUBSTR(aa.DTR_AMI,1,2),':',SUBSTR(aa.DTR_AMI,3,2)) ELSE END) DTR_AMI, (CASE WHEN aa.DTR_AMO != '0000' THEN CONCAT(SUBSTR(aa.DTR_AMO,1,2),':',SUBSTR(aa.DTR_AMO,3,2)) ELSE END) DTR_AMO, (CASE WHEN aa.DTR_PMI != '0000' THEN CONCAT(SUBSTR(aa.DTR_PMI,1,2),':',SUBSTR(aa.DTR_PMI,3,2)) ELSE END) DTR_PMI, (CASE WHEN aa.DTR_PMO != '0000' THEN CONCAT(SUBSTR(aa.DTR_PMO,1,2),':',SUBSTR(aa.DTR_PMO,3,2)) ELSE END) DTR_PMO, CONCAT(SUBSTR(bb.OBTDTME,1,2),':',SUBSTR(bb.OBTDTME,3,2)) OBTDTME, CONCAT(SUBSTR(bb.OBTATME,1,2),':',SUBSTR(bb.OBTATME,3,2)) OBTATME, CONCAT(SUBSTR(cc.OTMFTME,1,2),':',SUBSTR(cc.OTMFTME,3,2)) OTMFTME, CONCAT(SUBSTR(cc.OTMTTME,1,2),':',SUBSTR(cc.OTMTTME,3,2)) OTMTTME, IFNULL(dd.LOAOPTN,) LOAOPTN, IFNULL(dd.LOACODE,) LOACODE, (CASE WHEN dd.LOAHOUR != '0.00000' THEN dd.LOAHOUR ELSE END) LOAHOUR, (CASE WHEN dd.LOAHRPD != '0.00000' THEN dd.LOAHRPD ELSE END) LOAHRPD, CONCAT(SUBSTR(ee.UNTSTME,1,2),':',SUBSTR(ee.UNTSTME,3,2)) UNTSTME, CONCAT(SUBSTR(ee.UNTETME,1,2),':',SUBSTR(ee.UNTETME,3,2)) UNTETME FROM mypaydb.dt2016 aa LEFT OUTER JOIN mypaydb.ob2016 bb ON aa.DTRNUMB = bb.OBTNUMB AND bb.OBTDATE = aa.DTRDATE LEFT OUTER JOIN mypaydb.ot2016 cc ON aa.DTRNUMB = cc.OTMNUMB AND aa.DTRDATE = cc.OTMDATE LEFT OUTER JOIN mypaydb.lv2016 dd ON aa.DTRNUMB = dd.LOANUMB AND aa.DTRDATE = dd.LOADATE LEFT OUTER JOIN mypaydb.un2016 ee ON aa.DTRNUMB = ee.UNTNUMB AND aa.DTRDATE = ee.UNTDATE WHERE aa.DTRNUMB = '14003945' AND aa.DTRDATE BETWEEN DATE('2016-01-01') AND DATE('2016-01-31')
ESSI LEAVE ADJUSTMENT (2016-02-16)
SELECT * FROM oa.2016_ESSI_DED_20160216
SELECT * FROM mypaydb.mhr2016
SELECT bb.*,aa.* FROM mypaydb.mhr2016 aa,oa.`2016_ESSI_DED_20160216` bb WHERE aa.MHRNUMB = bb.EMP_NO AND DATE(aa.MHRCDTE) = DATE('2016-02-16')
SELECT * FROM oa.2016_ESSI_DED_20160216
/* update SH*/ UPDATE mypaydb.mhr2016 aa,oa.`2016_ESSI_DED_20160216` bb SET aa.MHRSHOL = bb.REM_HRS WHERE aa.MHRNUMB = bb.EMP_NO AND DATE(aa.MHRCDTE) = DATE('2016-02-16')
/* update for VL*/
UPDATE mypaydb.mhr2016 aa,oa.`2016_ESSI_DED_20160216` bb SET aa.MHRVLLV = bb.VL
WHERE aa.MHRNUMB = bb.EMP_NO AND DATE(aa.MHRCDTE) = DATE('2016-02-16')
/* update for OTHERS*/ UPDATE mypaydb.mhr2016 aa,oa.`2016_ESSI_DED_20160216` bb SET aa.MHROTHR = bb.OTHERS WHERE aa.MHRNUMB = bb.EMP_NO AND DATE(aa.MHRCDTE) = DATE('2016-02-16')
Movement of Employee 52 (position)
//* for those still active employees*// SELECT * FROM mypmsdb.`pmtemve` WHERE emvnumb = '20017046' SELECT * FROM mypmsdb.pmmposn WHERE POSPSCD = '136'
//*for those previous employees*//
--INFORMATION : NAME, STATUS--
SELECT * FROM mypmsdb.pmhempl WHERE EMPLNAM LIKE 'NAVARRO' AND EMPFNAM = 'SARAH'
--INFORMATION : SEPARATION DATE, REASON OF LEAVING--
SELECT * FROM mypmsdb.pmtsepa WHERE SEPNUMB = '20017046'
--INFORMATION : POSITION--
SELECT * FROM mypmsdb.`pmtemve` WHERE emvnumb = '20017046' SELECT * FROM mypmsdb.pmmposn WHERE POSPSCD = '136'
