49 lines
1.3 KiB
Transact-SQL
49 lines
1.3 KiB
Transact-SQL
truncate table FCLDB..SSR
|
|
GO
|
|
UPDATE ssr SET SESNo=CONVERT(NVARCHAR(50),CONVERT(INT, SESNo1))
|
|
GO
|
|
INSERT into [dbo].[SSR](
|
|
[SSRId],
|
|
[SES_No],
|
|
[WorkOrder],
|
|
[Op],
|
|
[RequisitionerId],
|
|
[RequisitionerName],
|
|
[DepartmentId],
|
|
[DepartmentName],
|
|
[SubmmisionDate],
|
|
[ReturnDate],
|
|
[CompletionDate],
|
|
[ContractNo],
|
|
[Address],
|
|
[Remark],
|
|
[InputDate],
|
|
[IsRetruned],
|
|
NotRetrunDuration,
|
|
ContractAdmin)
|
|
|
|
select NEWID(),
|
|
SESNo,
|
|
(case when WorkorderOp is not null and WorkorderOp like'%-%' then SUBSTRING(WorkorderOp,1,CHARINDEX('-',WorkorderOp)-1) end) as WorkOrder,
|
|
(case when WorkorderOp is not null and WorkorderOp like'%-%' then SUBSTRING(WorkorderOp,CHARINDEX('-',WorkorderOp)+1 ,len(WorkorderOp)-charindex('-',WorkorderOp)) end) as Op,
|
|
|
|
(SELECT TOP 1 UserId FROM dbo.Sys_User WHERE UserName=ssr.Requisitioner) AS RequisitionerId,
|
|
Requisitioner,
|
|
(SELECT TOP 1 DepartId FROM dbo.Base_Depart WHERE DepartName=ssr.Department) AS DepartId,
|
|
Department,
|
|
SubmisionDate,
|
|
null,
|
|
CompletedDate,
|
|
(SELECT TOP 1 FO FROM dbo.FC_SESReport WHERE SES_No=ssr.SESNo) AS FO,
|
|
Addresss,
|
|
ssr.Remark,
|
|
InputDate,
|
|
'False',
|
|
DATEDIFF(DAY,CompletedDate,GETDATE()),
|
|
(SELECT TOP 1 Contract_Admin FROM FC_SESRelatedData
|
|
WHERE FO_NO=(SELECT TOP 1 FO FROM dbo.FC_SESReport WHERE SES_No=ssr.SESNo))
|
|
from fcexceldb..ssr ssr
|
|
WHERE ssr.SESNo IS NOT NULL
|
|
|
|
GO
|