|
1
|
INSERT INTO dbo.hr_md_emp_md0017
|
|
2
|
SELECT CONVERT(VARCHAR(20), '100') AS landscape ,
|
|
3
|
md02.emp_id ,
|
|
4
|
md15.hiring_date AS endate,
|
|
5
|
REPLACE(CAST(DATEADD(YEAR, -1, GETDATE()) AS DATE), '-', '')stdate ,
|
|
6
|
md15.hiring_date AS std ,
|
|
7
|
REPLACE(CAST(DATEADD(YEAR, -1, GETDATE()) AS DATE), '-', '') AS etd ,
|
|
8
|
CONVERT(VARCHAR(20), '12') AS oriquot,
|
|
9
|
CONVERT(VARCHAR(20), 'System') AS userchange,
|
|
10
|
format(getdate(),'yyyyMMddHHmmss') AS laschange,
|
|
11
|
CONVERT(VARCHAR(20), 'System') AS createby,
|
|
12
|
format(getdate(),'yyyyMMddHHmmss') AS createdate,
|
|
13
|
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)
|
|
14
|
IS NOT NULL THEN
|
|
15
|
(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'
|
|
16
|
END AS seq,
|
|
17
|
CONVERT(VARCHAR(20),'1000') AS absentyp,
|
|
18
|
CONVERT(VARCHAR(20), '12') AS remainquot
|
|
19
|
FROM dbo.hr_md_emp_md0002 md02
|
|
20
|
LEFT OUTER JOIN dbo.hr_md_emp_md0015 md15 ON md02.emp_id = md15.emp_id
|
|
21
|
WHERE md02.employee_status = '01'
|
|
22
|
AND md15.hiring_date <= REPLACE(CAST(DATEADD(YEAR, -1, GETDATE()) AS DATE),'-', '')
|
|
23
|
AND md02.end_date = '99991231'
|
|
24
|
AND md02.emp_id NOT IN (
|
|
25
|
SELECT DISTINCT md02.emp_id
|
|
26
|
FROM dbo.hr_md_emp_md0002 md02
|
|
27
|
LEFT OUTER JOIN dbo.hr_md_emp_md0015 md15 ON md02.emp_id = md15.emp_id
|
|
28
|
LEFT OUTER JOIN dbo.hr_md_emp_md0017 md17 ON md17.emp_id = md02.emp_id
|
|
29
|
WHERE md02.employee_status = '01'
|
|
30
|
AND md15.hiring_date <= REPLACE(CAST(DATEADD(YEAR, -1, GETDATE()) AS DATE),'-', '')
|
|
31
|
AND md02.end_date = '99991231'
|
|
32
|
AND SUBSTRING( md17.end_deduction,0,5) <= YEAR(GETDATE())
|
|
33
|
)
|
|
34
|
--AND md02.emp_id = '01052312'
|