Difference between revisions of "PAYROLL"

Revision as of 15:12, 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

Get total 13th month pay per company

SELECT * FROM (

   SELECT 'VIP' `MCLASS`,bb.CMPNAME AS Company,COUNT(aa.AHSNUMB) Total_NO_Employee,
   SUM(IF(aa.AHSPMCD = 'A',aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS,0) )`ATM`,
   SUM(IF(aa.AHSPMCD = 'C' AND !(aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS,0) ) `CASH`,
   SUM(IF(aa.AHSPMCD = 'C' AND (aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS,0) ) `HOLD`,
   SUM(aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS) Total_Amount,'2014' `Year`
   FROM mypaydv.`bonus2014` aa,mypmsdb.`pmmcmpy` bb
   WHERE !(aa.AHSCOCD = 'C' AND aa.AHSCOCD = '4' ) AND aa.AHSCOCD = bb.CMPCOCD GROUP BY AHSCOCD
   UNION ALL
   SELECT 'VIP' `MCLASS`,bb.CMPNAME AS Company,COUNT(aa.AHSNUMB) Total_NO_Employee,
   SUM(IF(aa.AHSPMCD = 'A',aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS,0) )`ATM`,
   SUM(IF(aa.AHSPMCD = 'C' AND !(aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS,0) ) `CASH`,
   SUM(IF(aa.AHSPMCD = 'C' AND (aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS,0) ) `HOLD`,
   SUM(aa.AHS13MO + aa.AHSSLPY + aa.AHSBONS) Total_Amount,'2013' `Year`
    FROM mypaydv.`bonus2013` aa,mypmsdb.`pmmcmpy` bb
   WHERE !(aa.AHSCOCD = 'C' AND aa.AHSCOCD = '4' )AND aa.AHSCOCD = bb.CMPCOCD GROUP BY AHSCOCD

   UNION ALL

   SELECT 'RNF' `MCLASS`,bb.CMPNAME AS Company,COUNT(aa.AHSNUMB) Total_NO_Employee,
   SUM(IF(aa.AHSPMCD = 'A',aa.AHS13MO + aa.AHSSLPY,0) )`ATM`,
   SUM(IF(aa.AHSPMCD = 'C' AND !(aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY,0) ) `CASH`,
   SUM(IF(aa.AHSPMCD = 'C' AND (aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY,0) ) `HOLD`,
   SUM(aa.AHS13MO + aa.AHSSLPY ) Total_Amount,'2014' `Year`
   FROM mypaydb.`bonus2014` aa,mypmsdb.`pmmcmpy` bb
   WHERE !(aa.AHSCOCD = 'C' AND aa.AHSCOCD = '4' ) AND aa.AHSCOCD = bb.CMPCOCD GROUP BY AHSCOCD
   UNION ALL
   SELECT 'RNF' `MCLASS`,bb.CMPNAME AS Company,COUNT(aa.AHSNUMB) Total_NO_Employee,
   SUM(IF(aa.AHSPMCD = 'A',aa.AHS13MO + aa.AHSSLPY,0) )`ATM`,
   SUM(IF(aa.AHSPMCD = 'C' AND !(aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY,0) ) `CASH`,
   SUM(IF(aa.AHSPMCD = 'C' AND (aa.AHSSTAT = 'H'),aa.AHS13MO + aa.AHSSLPY,0) ) `HOLD`,
   SUM(aa.AHS13MO + aa.AHSSLPY ) Total_Amount,'2013' `Year`
    FROM mypaydb.`bonus2013` aa,mypmsdb.`pmmcmpy` bb
   WHERE !(aa.AHSCOCD = 'C' AND aa.AHSCOCD = '4' )AND aa.AHSCOCD = bb.CMPCOCD GROUP BY AHSCOCD

) me ORDER BY Company,`Year`