Select * from Employee
Where el_CD <= 7
And UserID Not IN (
Select WWH_UserID from WH_WorkHistory Where WWH_CheckYear = 2004 And WWH_CheckMonth = 5 And WWH_CheckDay = 3
And WWH_TS_CD = 1
)
And JoinCompanyDay < Convert(Varchar(10), Getdate(), 121)
Select Count(*) from Employee
Where el_CD <= 7
Execute WH_Work_WorkStatical_sp
Select dbo.fn_Get_Employee_Team(36)
/*
작성자 : 한영민
작성일 : 2004-04-30
설 명 : 근태관리 통계 Count
*/
ALTER Procedure dbo.WH_Work_WorkStatical_sp
@SearchDate Varchar(10) = '0'
AS
IF @SearchDate = '0'
SET @SearchDate = Convert(DateTime, Getdate())
Select
Isnull(Sum(WC1), 0) AS 연차,
Isnull(Sum(WC2), 0) AS 월차,
Isnull(Sum(WC3), 0) AS 반차,
Isnull(Sum(WC4), 0) AS 리프레쉬,
Isnull(Sum(WC5), 0) AS 보건,
Isnull(Sum(WC6), 0) AS 대체휴가,
Isnull(Sum(WC7), 0) AS 훈련,
Isnull(Sum(WC8), 0) AS 경조,
Isnull(Sum(WC9), 0) AS 병가,
Isnull(Sum(WC10), 0) AS 출장,
Isnull(Sum(WC11), 0) AS 교육,
Isnull(Sum(WC12), 0) AS 결근,
Isnull(Sum(WC13), 0) AS 지각,
dbo.fn_Get_OverCheckCntBYDay(0, Convert(Varchar(10), @SearchDate, 121)) AS 석식대,
dbo.fn_Get_OverCheckCntBYDay(1, Convert(Varchar(10), @SearchDate, 121)) AS 야간식대,
Isnull(Sum(WC16), 0) AS 조퇴,
Isnull(Sum(WC17), 0) AS 정출,
Isnull(Sum(WC18), 0) AS 정퇴,
Isnull(Sum(WC19), 0) AS 공휴출,
Isnull(Sum(WC20), 0) AS 전퇴,
Isnull(Sum(WC21), 0) AS 공휴퇴,
Isnull(Sum(WC23), 0) AS 별정휴무,
Isnull(Sum(WC1), 0)+Isnull(Sum(WC2), 0)+Isnull(Sum(WC3), 0)+Isnull(Sum(WC4), 0)+Isnull(Sum(WC5), 0)+
Isnull(Sum(WC6), 0)+Isnull(Sum(WC7), 0)+Isnull(Sum(WC8), 0)+Isnull(Sum(WC9), 0)+Isnull(Sum(WC10), 0)+
Isnull(Sum(WC11), 0)+Isnull(Sum(WC12), 0)+Isnull(Sum(WC13), 0)+Isnull(Sum(WC16), 0)+Isnull(Sum(WC17), 0)+
Isnull(Sum(WC18), 0)+Isnull(Sum(WC19), 0)+Isnull(Sum(WC20), 0)+Isnull(Sum(WC21), 0)+Isnull(Sum(WC23), 0) AS 총합
From
(
SELECT
Case WC_CD When 1 Then WWH2.Cnt/IsDualInsert End AS WC1,
Case WC_CD When 2 Then WWH2.Cnt/IsDualInsert End AS WC2,
Case WC_CD When 3 Then WWH2.Cnt/IsDualInsert End AS WC3,
Case WC_CD When 4 Then WWH2.Cnt/IsDualInsert End AS WC4,
Case WC_CD When 5 Then WWH2.Cnt/IsDualInsert End AS WC5,
Case WC_CD When 6 Then WWH2.Cnt/IsDualInsert End AS WC6,
Case WC_CD When 7 Then WWH2.Cnt/IsDualInsert End AS WC7,
Case WC_CD When 8 Then WWH2.Cnt/IsDualInsert End AS WC8,
Case WC_CD When 9 Then WWH2.Cnt/IsDualInsert End AS WC9,
Case WC_CD When 10 Then WWH2.Cnt/IsDualInsert End AS WC10,
Case WC_CD When 11 Then WWH2.Cnt/IsDualInsert End AS WC11,
Case WC_CD When 12 Then WWH2.Cnt/IsDualInsert End AS WC12,
Case WC_CD When 13 Then WWH2.Cnt/IsDualInsert End AS WC13,
Case WC_CD When 16 Then WWH2.Cnt/IsDualInsert End AS WC16,
Case WC_CD When 17 Then WWH2.Cnt/IsDualInsert End AS WC17,
Case WC_CD When 18 Then WWH2.Cnt/IsDualInsert End AS WC18,
Case WC_CD When 19 Then WWH2.Cnt/IsDualInsert End AS WC19,
Case WC_CD When 20 Then WWH2.Cnt/IsDualInsert End AS WC20,
Case WC_CD When 21 Then WWH2.Cnt/IsDualInsert End AS WC21,
Case WC_CD When 23 Then WWH2.Cnt/IsDualInsert End AS WC23,
Count(*) AS WC0
from WH_WorkCode AS WWC1
Right Outer Join
(
Select WWH_WC_CD, count(WWH_CD) as Cnt From WH_WorkHistory
Where WWH_CheckYear = Year(@SearchDate)
And WWH_CheckMonth = Month(@SearchDate)
And WWH_CheckDay = Day(@SearchDate)
GROUP BY WWH_WC_CD
) WWH2 on WWC1.WC_CD=WWH2.WWH_WC_CD
GROUP BY
Case WC_CD When 1 Then WWH2.Cnt/IsDualInsert End,
Case WC_CD When 2 Then WWH2.Cnt/IsDualInsert End,
Case WC_CD When 3 Then WWH2.Cnt/IsDualInsert End,
Case WC_CD When 4 Then WWH2.Cnt/IsDualInsert End ,
Case WC_CD When 5 Then WWH2.Cnt/IsDualInsert End ,
Case WC_CD When 6 Then WWH2.Cnt/IsDualInsert End ,
Case WC_CD When 7 Then WWH2.Cnt/IsDualInsert End ,
Case WC_CD When 8 Then WWH2.Cnt/IsDualInsert End ,
Case WC_CD When 9 Then WWH2.Cnt/IsDualInsert End ,
Case WC_CD When 10 Then WWH2.Cnt/IsDualInsert End,
Case WC_CD When 11 Then WWH2.Cnt/IsDualInsert End ,
Case WC_CD When 12 Then WWH2.Cnt/IsDualInsert End,
Case WC_CD When 13 Then WWH2.Cnt/IsDualInsert End,
Case WC_CD When 16 Then WWH2.Cnt/IsDualInsert End,
Case WC_CD When 17 Then WWH2.Cnt/IsDualInsert End,
Case WC_CD When 18 Then WWH2.Cnt/IsDualInsert End,
Case WC_CD When 19 Then WWH2.Cnt/IsDualInsert End,
Case WC_CD When 20 Then WWH2.Cnt/IsDualInsert End,
Case WC_CD When 21 Then WWH2.Cnt/IsDualInsert End,
Case WC_CD When 23 Then WWH2.Cnt/IsDualInsert End
) AS WorkStatical
