INSERT INTO  dbo.hr_md_emp_md0017 
SELECT  CONVERT(VARCHAR(20), '100') AS landscape ,
        md02.emp_id ,
        md15.hiring_date AS endate,
        REPLACE(CAST(DATEADD(YEAR, -1, GETDATE()) AS DATE), '-', '')stdate ,
        md15.hiring_date AS std ,
        REPLACE(CAST(DATEADD(YEAR, -1, GETDATE()) AS DATE), '-', '') AS etd ,
        CONVERT(VARCHAR(20), '12') AS oriquot,
                 CONVERT(VARCHAR(20), 'System') AS userchange,
                format(getdate(),'yyyyMMddHHmmss') AS laschange,
                 CONVERT(VARCHAR(20), 'System') AS createby,
                 format(getdate(),'yyyyMMddHHmmss') AS createdate,
                CASE WHEN (SELECT TOP(1) CONVERT(VARCHAR(20), CONVERT(INT,seq)+1) AS seq FROM dbo.hr_md_emp_md0017 WHERE emp_id= md02.emp_id ORDER BY seq desc) 
                IS NOT NULL THEN 
                (SELECT TOP(1) CONVERT(VARCHAR(20), CONVERT(INT,seq)+1) AS seq FROM dbo.hr_md_emp_md0017 WHERE emp_id= md02.emp_id ORDER BY seq DESC) ELSE '1'
                END AS seq,
                CONVERT(VARCHAR(20),'1000') AS absentyp,
                 CONVERT(VARCHAR(20), '12') AS remainquot
FROM    dbo.hr_md_emp_md0002 md02
        LEFT OUTER JOIN dbo.hr_md_emp_md0015 md15 ON md02.emp_id = md15.emp_id
WHERE   md02.employee_status = '01'
        AND md15.hiring_date <= REPLACE(CAST(DATEADD(YEAR, -1, GETDATE()) AS DATE),'-', '')
        AND md02.end_date = '99991231'
                AND md02.emp_id NOT IN (
                SELECT  DISTINCT md02.emp_id 
                FROM    dbo.hr_md_emp_md0002 md02
                                LEFT OUTER JOIN dbo.hr_md_emp_md0015 md15 ON md02.emp_id = md15.emp_id
                                LEFT OUTER JOIN dbo.hr_md_emp_md0017 md17 ON md17.emp_id = md02.emp_id
                WHERE   md02.employee_status = '01'
                                AND md15.hiring_date <= REPLACE(CAST(DATEADD(YEAR, -1, GETDATE()) AS DATE),'-', '')
                                AND md02.end_date = '99991231'
                                AND SUBSTRING( md17.end_deduction,0,5) <= YEAR(GETDATE())        
                )
        --AND md02.emp_id = '01052312'