Difference between revisions of "PAYROLL"

Revision as of 15:10, 16 June 2016

13th mo misc VIP.txt

SELECT * FROM mypaydb.hst2015

SELECT * FROM mypaydv.hst2015

SELECT * FROM mypaydv.hst2015 WHERE HSTNUMB IN( SELECT HSTNUMB FROM mypaydb.hst2015 )

oa.hst2015_vip_b413mocomp

CREATE TABLE IF NOT EXISTS oa.hst2015_vip_b413mocomp_20151204 LIKE mypaydv.hst2015; INSERT INTO oa.hst2015_vip_b413mocomp_20151204 SELECT * FROM mypaydv.hst2015;

INSERT IGNORE INTO mypaydv.hst2015 SELECT * FROM mypaydb.hst2015 WHERE hstnumb IN( SELECT HSTNUMB FROM mypaydv.hst2015 )

DELETE FROM mypaydb.hst2015 WHERE hstnumb IN( SELECT HSTNUMB FROM mypaydv.hst2015 )

UPDATE mypaydv.hst2015 SET hstpccd = '3'

Alphalist.txt

/*select employee*/ SELECT * FROM mypaydb.`alphalist2316_2015` WHERE ( EMPTINO NOT IN (SELECT EMPNUMB FROM oa.alphalist2316_2015_sepa) OR EMPNUMB NOT IN (SELECT EMPNUMB FROM oa.alphalist2316_2015_sepa)) AND COCD = '1'

/*select included employees*/ SELECT * FROM mypaydb.`alphalist2316_2015` WHERE ( EMPTINO IN (SELECT EMPNUMB FROM oa.alphalist2316_2015_sepa) OR EMPNUMB IN (SELECT EMPNUMB FROM oa.alphalist2316_2015_sepa)) AND COCD = '1'

/*select not included employees*/ SELECT * FROM oa.alphalist2316_2015_sepa WHERE ( EMPNUMB NOT IN (SELECT EMPTINO FROM mypaydb.alphalist2316_2015) AND EMPNUMB NOT IN (SELECT EMPNUMB FROM mypaydb.alphalist2316_2015)) GROUP BY empnumb

SELECT * FROM oa.`alphalist2316_2015_sepa` WHERE recid >= '1838'

SELECT * FROM mypaydb.alphalist2316_2015 WHERE cocd = '1'

CHANGE SAP GL Entry 52.txt

SELECT * FROM mypaydb.pymsdmf_newgl WHERE SDMOACD = '153003' 153003 UPDATE mypaydb.pymsdmf_newgl SET SDMOACD = '153003' WHERE SDMOACD = '300001'

Check sapgl.txt

SELECT aa.*,bb.* FROM (SELECT aa.EMPNUMB,SUM(AMNT) AMNT,EMPLNAM,EMPFNAM,EMPMNAM FROM mypaydb.`epaysapgl` aa,mypmsdv.pmtempl bb WHERE aa.EMPNUMB = bb.EMPNUMB AND PCCD = '3' AND DATE(PCDTE) = DATE('2015-09-16') AND A_KEY = '40'

AND COCD = '7' 
GROUP BY EMPNUMB) aa,

(SELECT aa.EMPNUMB,SUM(AMNT) AMNT,EMPLNAM,EMPFNAM,EMPMNAM FROM mypaydb.`epaysapgl` aa,mypmsdv.pmtempl bb WHERE aa.EMPNUMB = bb.EMPNUMB AND PCCD = '3' AND DATE(PCDTE) = DATE('2015-09-16') AND A_KEY = '50'

AND COCD = '7' 
GROUP BY EMPNUMB) bb WHERE aa.EMPNUMB = bb.EMPNUMB AND aa.AMNT != bb.AMNT 

Discussion 806.txt

SELECT HSTNUMB,CONCAT(TRIM(EMPLNAM),' ',TRIM(EMPFNAM),' ',TRIM(EMPMNAM)) AS 'NAME',HSTCDTE,DEPNAME,CMPNAME FROM mypaydb.hst2015 LEFT JOIN mypmsdb.pmtempl ON pmtempl.EMPNUMB = hst2015.HSTNUMB LEFT JOIN mypmsdb.pmmdept ON pmmdept.DEPDPCD = hst2015.HSTDPCD LEFT JOIN mypmsdb.pmmloca ON pmmloca.LOCCODE = hst2015.HSTLCCD LEFT JOIN mypmsdb.pmmcmpy ON pmmcmpy.CMPCOCD = hst2015.HSTCOCD

SELECT * FROM mypaydb.hst2015

SELECT * FROM mypmsdb.pmmdept

SELECT * FROM mypmsdb.pmmloca

SELECT * FROM mypmsdb.pmmcmpy