INSERT INTO HRMS.TRAINING_CLASS

INSERT INTO HRMS.TRAINING_CLASS
SELECT Row_Number() OVER (ORDER BY [課程名稱] ASC)
,'C011' + dbo.PadLeft(CONVERT(VARCHAR,Row_Number() OVER (ORDER BY [課程名稱] ASC)),'0',5)
,[課程名稱]
,CASE MIN([外訓/內訓 ]) WHEN '内训' THEN '0A' ELSE '1A' END
,'PRO',[課程名稱],'','Y',1,1
,ISNULL(MIN([上課日期]),GETDATE())
,ISNULL(MIN([上課日期]),GETDATE())
FROM DB_0926.DBO.TRN_COS GROUP BY [課程名稱]

 

 

INSERT INTO HRMS.TRAINING_INFO
SELECT Row_Number() OVER (ORDER BY [課程名稱] ASC)
,b.CLASS_ID
,'KK11' + dbo.PadLeft(CONVERT(VARCHAR,Row_Number() OVER (ORDER BY [課程名稱] ASC)),'0',5)
,ISNULL(a.[上課講師],'N/A')
,ISNULL(a.[工号],'N/A')
,SUBSTRING(CONVERT(VARCHAR,b.CREATE_DATE,120),1,10) + ' ' + SUBSTRING([上課時間],1,5) + ':00'
,SUBSTRING(CONVERT(VARCHAR,b.CREATE_DATE,120),1,10) + ' ' + SUBSTRING([上課時間],7,5) + ':00'
,SUBSTRING(CONVERT(VARCHAR,b.CREATE_DATE,120),1,10) + ' ' + SUBSTRING([上課時間],1,5) + ':00'
,SUBSTRING(CONVERT(VARCHAR,b.CREATE_DATE,120),1,10) + ' ' + SUBSTRING([上課時間],7,5) + ':00'
,MAX([學時])
,MAX([上課地點])
,CASE ISNULL(MAX([考核成績/_心得報告]),-1) WHEN -1 THEN 'BS' ELSE 'XD' END
,'A','',1,1,SUBSTRING(CONVERT(VARCHAR,b.CREATE_DATE,120),1,10) + ' ' + SUBSTRING([上課時間],1,5) + ':00'
,SUBSTRING(CONVERT(VARCHAR,b.CREATE_DATE,120),1,10) + ' ' + SUBSTRING([上課時間],1,5) + ':00'
FROM DB_0926.DBO.TRN_COS a,HRMS.TRAINING_CLASS b
WHERE a.[課程名稱]=b.CLASS_NAME
GROUP BY b.CLASS_ID,b.CREATE_DATE,[課程名稱],[上課講師],[工号],[上課時間]

 

INSERT INTO HRMS.TRAINING_EMP
SELECT c.TRAINING_ID
,ISNULL(a.[工号],'N/A')
,ISNULL(a.[姓名],'N/A')
,'A',ISNULL([考核成績/_心得報告],-1),ISNULL(CONVERT(VARCHAR,[考核成績/_心得報告]),'OK.DOC'),'',1,1
,SUBSTRING(CONVERT(VARCHAR,b.CREATE_DATE,120),1,10) + ' ' + SUBSTRING([上課時間],1,5) + ':00'
,SUBSTRING(CONVERT(VARCHAR,b.CREATE_DATE,120),1,10) + ' ' + SUBSTRING([上課時間],1,5) + ':00'
FROM DB_0926.DBO.TRN_COS a,HRMS.TRAINING_CLASS b,HRMS.TRAINING_INFO c
WHERE a.[課程名稱]=b.CLASS_NAME AND b.CLASS_ID=c.TRAINING_CLASS AND ISNULL(a.[上課講師],'N/A')=c.TEACHER_NAME
AND ISNULL(a.[工号],'N/A')=c.TEACHER_NO
AND SUBSTRING(CONVERT(VARCHAR,b.CREATE_DATE,120),1,10) + ' ' + SUBSTRING([上課時間],1,5) + ':00'=c.PLAN_START
AND SUBSTRING(CONVERT(VARCHAR,b.CREATE_DATE,120),1,10) + ' ' + SUBSTRING([上課時間],7,5) + ':00'=c.PLAN_END

 

INSERT INTO HRMS.EMP_PER
SELECT TOP 395 b.Code,a.EMP_NO,a.EMP_NAME,'2011','Q2'
,CONVERT(DECIMAL(15,6),Q2),'U',1,1,GETDATE(),GETDATE()
FROM DB_0926.dbo.TRS_Q a,KSHR.txcard0609.dbo.ZlDept b
WHERE a.DEPT_NAME=b.Name
AND Q2 IS NOT NULL