Select * from WH_WhoDateTime
WH_Get_WorkerSetting_sp '2006-09-27', '2006-10-01'
/*
작성자 : ByVINS
작성일 : 2004-03-17
설 명 : 별정근무자 세팅 정도 가져 오기
*/
ALTER Procedure dbo.WH_Get_WorkerSetting_sp
@StartDate Varchar(10)
, @LastDate Varchar(10)
AS
Select WDT_CD, C.CT_Desc, WDT_setDate, groupName, E.UserName
from dbo.WH_WhoDateTime AS WDT
INNER JOIN Employee AS E ON WDT.WDT_UserCN = E.UserCN
INNER JOIN CodeGroup AS G ON E.groupCD = G.groupCD
INNER JOIN CompanyTimer As C ON C.CT_CD = WDT.WDT_CT_CD
Where WDT_setDate >= @StartDate And WDT_setDate <= @LastDate
Order by WDT_setDate Desc
GO
/*
작성자 : ByVINS
작성일 : 2004-03-17
설 명 : 별정근무자 삭제
*/
CREATE Procedure dbo.WH_del_WorkerSetting_sp
@WDT_CD int
AS
Delete from dbo.WH_WhoDateTime Where WDT_CD = @WDT_CD
If @@Error <> 0 Goto Err
Return 0
Err:
Return -1
GO
/*
작성자 : ByVINS
작성일 : 2004-03-16
설 명 : 별정근무자 등록 세팅
*/
ALTER Procedure dbo.WH_NotGenelar_WorkerSetting_sp
@UserCNs NVarchar(4000)
, @setDates NVarchar(4000)
, @WDT_CT_CD NVarchar(3)
AS
Declare @IsHoliday INT
DECLARE @DescName NVarchar(100), @strSQL NVarchar(4000)
SET @IsHoliday = 1
DECLARE CUR_Worker CURSOR
FOR
--테이블 변수를 이용한 Split함수 구현후 테이블 형식 리턴
Select DescName from dbo.Split(@setDates)
OPEN CUR_Worker
--첫 로우 FETCH
FETCH NEXT FROM CUR_Worker INTO @DescName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL = '
Insert WH_WhoDateTime(WDT_setDate, WDT_UserCN, WDT_GroupCD, WDT_CT_CD)
Select '''+ @DescName +''', UserCN, groupCD, '+@WDT_CT_CD+' from Employee
Where UserCN IN (Select DescName from dbo.Split(''' + @UserCNs + ''')) '
Execute Sp_ExecuteSQL @strSQL
IF @@Error <> 0 Goto Err
FETCH NEXT FROM CUR_Worker INTO @DescName
END
CLOSE CUR_Worker
DEALLOCATE CUR_Worker
Return 0
Err:
Return -1
GO
Declare @WDT_CT_CD Varchar(10)
SET @WDT_CT_CD = '1245'
Declare @DescName Varchar(10)
SET @DescName = '2006-09-29'
Declare @UserCNs Varchar(4000)
SET @UserCNs = '100001,100003,100004,100005'
Declare @strSQL NVarchar(4000)
SET @strSQL = '
Insert WH_WhoDateTime(WDT_setDate, WDT_UserCN, WDT_GroupCD, WDT_CT_CD)
Select '''+ @DescName +''', UserCN, groupCD, '+@WDT_CT_CD+' from Employee
Where UserCN IN (Select DescName from dbo.Split(''' + @UserCNs + ''')) '
Select @strSQL
execute sp_executesql @strSQL
Select * from WH_WhoDateTime
Insert WH_WhoDateTime(WDT_setDate, WDT_UserCN, WDT_GroupCD, WDT_CT_CD)
Select '2006-09-30', UserCN, groupCD, 1245 from Employee
Where UserCN IN (Select DescName from dbo.Split('100001,100'))
