USE [ChiefMes]
GO
/****** Object: StoredProcedure [dbo].[Kenta_UpandAddEmpOEEConfig] Script Date: 05/12/2015 08:34:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Kenta_UpandAddEmpOEEConfig]
(
@IDStr nvarchar(100)=''
)
AS
begin
if(@IDStr<>'')
begin
--declare @splitTable Table (
--EmpID nvarchar(20) not null
--)
--insert into @splitTable
--select Col from dbo.FN_SplitSTR(@EmpIDStr,',') fstt
update A set A.EndDate=DATEADD(dd,-1,GETDATE())
from Kenta_EmpOEEConfig A
join
(select * from Kenta_EmpOEEConfig p
where p.id=(select max(id) from Kenta_EmpOEEConfig a where a.empid=p.empid)
) B
on A.id=B.ID
join
dbo.FN_SplitSTR(@IDStr,',') fstt
on A.ID=fstt.Col
insert into Kenta_EmpOEEConfig
(EmpID,EmpName,BCCode,EmpGroupNo,MachineGroup,MachineNo,
Flag,BCGroup,CreateDate,UpdateBy,UpdateDate,ShowOEE,ShowMointor,StartDate)
select EmpID,EmpName, case when BCCode='A' then 'B' else 'A' end,
EmpGroupNo,MachineGroup,MachineNo,
Flag,BCGroup,CreateDate,UpdateBy,UpdateDate,ShowOEE,ShowMointor,GETDATE()
from Kenta_EmpOEEConfig p
join dbo.FN_SplitSTR(@IDStr,',') fstt on p.ID=fstt.Col
where p.id=(select max(id) from Kenta_EmpOEEConfig a where a.empid=p.empid)
end
end
GO
select * from Kenta_EmpOEEConfig order by StartDate asc,ID asc
Exec [Kenta_UpandAddEmpOEEConfig] '13,34'