Difference between revisions of "TIME KEEPING"
(Created page with "== '''Check No. of leave credits.txt''' == //* TO CHECK THE LEAVE OF AN VIP EMPLOYEE PER CUTOFF*\\ SELECT SUM(MHVVLLV) VL,SUM(MHVSLLV) SL FROM mypaydv.mhv2015 WHERE MHVNUMB =...") |
|||
| Line 1: | Line 1: | ||
| − | == '''Check No. of leave credits | + | == '''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*\\ | ||
| Line 21: | Line 21: | ||
| − | == '''DELETE FPID | + | == '''DELETE FPID''' == |
SELECT * FROM mypaydb.pytchro WHERE CROCRNO LIKE '%984%' | SELECT * FROM mypaydb.pytchro WHERE CROCRNO LIKE '%984%' | ||
| Line 31: | Line 31: | ||
| − | == '''Empinfo by hst | + | == '''Empinfo by hst''' == |
DROP TABLE IF EXISTS oa.emp2015; | DROP TABLE IF EXISTS oa.emp2015; | ||
| Line 78: | Line 78: | ||
| − | == '''Get Employee's In and Out | + | == '''Get Employee's In and Out''' == |
// RNF | // RNF | ||
| Line 104: | Line 104: | ||
| − | == '''Consolidated Manhour And Ot Of May | + | == '''Consolidated Manhour And Ot Of May''' == |
SELECT * FROM oliver.emp2316 | SELECT * FROM oliver.emp2316 | ||
| Line 564: | Line 564: | ||
| − | == Deletion Of BIOMETRICS 52 | + | == Deletion Of BIOMETRICS 52 == |
SELECT * FROM mypaydb.pytchro WHERE CROCRNO LIKE '%1040%' | SELECT * FROM mypaydb.pytchro WHERE CROCRNO LIKE '%1040%' | ||
SELECT * FROM mypaydb.fpid WHERE FPENUMB LIKE '%97004104' | SELECT * FROM mypaydb.fpid WHERE FPENUMB LIKE '%97004104' | ||
| Line 574: | Line 574: | ||
SELECT * FROM mypmsdb.pmtempl WHERE EMPNUMB = '20032387' | SELECT * FROM mypmsdb.pmtempl WHERE EMPNUMB = '20032387' | ||
| − | + | == ERROR In Deleting Efile == | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | == ERROR In Deleting Efile | + | |
/*delete only in tables pmtsepa, pmhrfempl, pmhempl | /*delete only in tables pmtsepa, pmhrfempl, pmhempl | ||
| Line 596: | Line 587: | ||
DELETE FROM mypmsdb.pmtsepa WHERE SEPNUMB = '20033924' | DELETE FROM mypmsdb.pmtsepa WHERE SEPNUMB = '20033924' | ||
| − | + | == Hold an Employee 52 == | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | == Hold an Employee 52 | + | |
SELECT * FROM mypmsdb.pmtempl WHERE emplnam = 'brillantes' | SELECT * FROM mypmsdb.pmtempl WHERE emplnam = 'brillantes' | ||
| Line 619: | Line 603: | ||
| − | + | == Deduct Special Holiday == | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | == Deduct Special Holiday | + | |
CREATE TABLE IF NOT EXISTS oa.2015_SPPE_DED_20151116 | CREATE TABLE IF NOT EXISTS oa.2015_SPPE_DED_20151116 | ||
| Line 662: | Line 638: | ||
WHERE aa.MHRNUMB = bb.EMP_NO AND DATE(aa.MHRCDTE) = DATE('2015-11-16') AND REM_HRS != 0 | WHERE aa.MHRNUMB = bb.EMP_NO AND DATE(aa.MHRCDTE) = DATE('2015-11-16') AND REM_HRS != 0 | ||
| − | + | == DTRPROFLIST Report == | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | == DTRPROFLIST Report | + | |
SELECT * FROM mypaydb.dt2016 WHERE dtrnumb = "13000668" | SELECT * FROM mypaydb.dt2016 WHERE dtrnumb = "13000668" | ||
| Line 706: | Line 666: | ||
| − | ==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 735: | Line 693: | ||
WHERE aa.MHRNUMB = bb.EMP_NO AND DATE(aa.MHRCDTE) = DATE('2016-02-16') | WHERE aa.MHRNUMB = bb.EMP_NO AND DATE(aa.MHRCDTE) = DATE('2016-02-16') | ||
| − | + | == Movement of Employee 52 (position) == | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | == Movement of Employee 52 (position) | + | |
Revision as of 17:06, 16 June 2016
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.txt
- 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.txt
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.EMPMNAM))
/* 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'
