----------------单独统计各指标，最后合并后存入DM_DH_DPET表，且最终把汇总结果也存入汇总表----------------------------------------------------------
--现在想起个问题，可以把汇总后的数据单独放入一个表，最后再和部门数据表关联
USE [HRPT_DEV]
GO
/****** Object: StoredProcedure [dbo].[USP_DH_STAT] Script Date: 03/17/2017 10:19:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: luo0.zhang
-- Create date: 2016.12.10
-- Description: EXEC [USP_DH_STAT_WY] '2016', '11', ''
-- =============================================
ALTER PROCEDURE [dbo].[USP_DH_STAT]
-- Add the parameters for the stored procedure here
@YEAR VARCHAR(4)
,@MONTH VARCHAR(2)
,@USER_ID VARCHAR(20)
,@RET VARCHAR(20) OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET NOCOUNT ON
/*=== A. DECLARE ============================================================START*/
-- 1. DEFAULT
DECLARE @SYSTEM VARCHAR(50) = 'HRPT';
DECLARE @PROC_NM VARCHAR(50) = 'USP_DH_STAT';
DECLARE @STRT_DT VARCHAR(30) = CONVERT(VARCHAR, GETDATE(), 121);
DECLARE @END_DT VARCHAR(30);
DECLARE @ERROR_MSG NVARCHAR(4000);
DECLARE @MonthDays int = 32-DAY(CAST(@YEAR+@MONTH+'01' AS DATETIME)+32-DAY(CAST(@YEAR+@MONTH+'01' AS DATETIME)))
DECLARE @FST_MM_DATE DATE
DECLARE @NXT_MM_DATE DATE
DECLARE @NXT_MM_DATE_YYYYMMDD VARCHAR(10)
SET @NXT_MM_DATE = DATEADD(MONTH, 1, CAST(@YEAR + @MONTH + '01' AS DATE))
SET @NXT_MM_DATE_YYYYMMDD = REPLACE(CAST(@NXT_MM_DATE AS DATE),'-','')
BEGIN TRY
BEGIN
-------------------------一 构造数据-------------------------------------------------
/**
* 当月或之前月数据确认就不统计，包括CLUB,WKT和CALC_DEPT
*/
DECLARE @CFM_YN VARCHAR(10)
SELECT @CFM_YN = T.DH_CFM_YN
FROM T_DM_DHSTC T
WHERE T.DH_YEAR = @YEAR AND T.DH_MONTH = @MONTH
PRINT 'Confirm Status : ' + @CFM_YN
IF (@CFM_YN = 'Y')
BEGIN
RETURN;
END
SET @FST_MM_DATE = CAST(@YEAR + '-' + @MONTH + '-' + '01' AS DATE)
SET @NXT_MM_DATE = DATEADD(MONTH, 1, CAST(@YEAR + '-' + @MONTH + '-' + '01' AS DATE))
--SELECT 'TESTMMDT', @FST_MM_DATE , @NXT_MM_DATE, @MONTH
--1 构造面谈率数据
DELETE FROM T_DM_DH_CVS
WHERE CVS_YEAR= @YEAR AND CVS_QUATR = DATEPART(QUARTER,@YEAR+@MONTH+'01' )
INSERT INTO T_DM_DH_CVS(
DH_TYPE
,COMP_CD
,CORP_CD
,EMP_ID
,TEAM_CD
,GRP_CD
,CVS_DATE
,CVS_USR_ID
,CVS_YN
,CVS_YEAR
,CVS_QUATR
,DH_RMK
,MO
,CREATE_DT
,CREATE_USR_ID
,UPDATE_DT
,UPDATE_USR_ID
)
SELECT
'H08'
,T3.COMP_CD
,T3.CORP_CD
,T1.EMP_ID
,T3.TEAM_CD
,T3.GROUP_CD
,INTV_DT
,T1.INTV_EMP_ID
,INTV_STATUS
,T2.INTV_YEAR
,T2.INTV_QUATR
,NULL
,NULL
,GETDATE()
,'SCSHR'
,GETDATE()
,'SCSHR'
FROM T_DM_INTV_USR T1
,T_DM_INTV T2
,TB_FR_DEPT_INFO T3
WHERE T1.INTV_SQ = T2.INTV_SQ
AND T2.DEPT_CD = T3.DEPT_CD
AND T2.INTV_TYPE = 'F01'
AND T1.INTV_CNT = 1
AND INTV_STATUS IS NOT NULL
--AND T1.INTV_DT >= @FST_MM_DATE AND T1.INTV_DT < @NXT_MM_DATE
AND T2.INTV_YEAR = @YEAR
AND T2.INTV_QUATR = DATEPART(QUARTER,@YEAR+@MONTH+'01' )
AND T1.EMP_ID NOT IN (SELECT HEAD_EMP_ID FROM TB_FR_DEPT_INFO WHERE HEAD_EMP_ID IS NOT NULL)
/*--1构造 同好会加入率数据
--同好会 是删除当前月，计算当前月加入同好会的人数 (H10)
DELETE FROM T_DM_DH_CLUB WHERE DH_YEAR = @YEAR AND DH_MONTH = @MONTH
INSERT INTO T_DM_DH_CLUB
(COMP_CD
,CORP_CD
,EMP_ID
,TEAM_CD
,GRP_CD
,CLUB_SQ
,CLUB_NM
,DH_YEAR
,DH_MONTH
,CLUB_JOIN_DATE
,CLUB_CNT
,DH_RMK
,DH_STATUS
,MO
,CREATE_DT
,CREATE_USR_ID
,UPDATE_DT
,UPDATE_USR_ID
)
SELECT
T5.COMP_CD
,T5.CORP_CD
,T5.EMP_ID
,T6.TEAM_CD
,T6.GROUP_CD GRP_CD
,T3.CLUB_SQ
,T3.CLUB_NM
,DATEPART(YEAR, GETDATE()) DH_YEAR
,RIGHT('0'+ltrim(MONTH(GETDATE())),2) DH_MONTH
,T4.JOIN_DATE CLUB_JOIN_DATE
--,T4.LEAVE_DATE
-- ,T4.CLUB_USR_PLG_YN
,COUNT(T3.CLUB_SQ)OVER(PARTITION BY T5.EMP_ID) CLUB_CNT
,'Y' DH_RMK
,'Y' DH_STATUS
,'MO' MO
,GETDATE()
,@USER_ID
,GETDATE()
,@USER_ID
FROM T_DM_CLUB T3
JOIN T_DM_CLUB_USR T4 ON T3.CLUB_SQ = T4.CLUB_SQ
JOIN TB_FR_USER_INFO T5 ON T5.USER_ID = T4.USR_ID
JOIN TB_FR_DEPT_INFO T6 ON T5.DEPT_CD = T6.DEPT_CD
WHERE (JOIN_DATE <= @YEAR+@MONTH+'01' AND JOIN_DATE IS NOT NULL )
OR (JOIN_DATE < @NXT_MM_DATE AND T4.LEAVE_DATE >= @YEAR+@MONTH+'01')
ORDER BY T3.CLUB_SQ,T4.JOIN_DATE*/
/*插入LDG表数据*/
DELETE FROM T_DM_DH_LDG
WHERE LDG_DATE >= @FST_MM_DATE AND LDG_DATE < @NXT_MM_DATE
-- SELECT * FROM T_DI_V2_LDG
INSERT INTO T_DM_DH_LDG(
COMP_CD
,CORP_CD
,EMP_ID
,TEAM_CD
,GRP_CD
,LDG_DATE
,LDG_DT
,LDG_TYPE
,LDG_LOC
,CREATE_DT
,CREATE_USR_ID
,UPDATE_DT
,UPDATE_USR_ID
)
SELECT MIN(T3.COMP_CD) COMP_CD
,MIN(T3.CORP_CD) CORP_CD
,RTRIM(LTRIM(IDNO)) EMP_ID
,MIN(T3.TEAM_CD) TEAM_CD
,MIN(T3.GROUP_CD) GROUP_CD
,SUBSTRING(T1.INOUT_DT, 1, 10) LDG_DATE
,REPLACE(REPLACE(MIN(T1.INOUT_DT),'T',' '),'+08:00','') LDG_DT--T1.INOUT_DT LDG_DT
,MIN(T1.INOUT_TYPE) LDG_TYPE
,MIN(T1.LOC_NM) LDG_LOC
,GETDATE()
,@USER_ID
,GETDATE()
,@USER_ID
FROM T_DI_V2_LDG T1
,TB_FR_USER_INFO T2
,TB_FR_DEPT_INFO T3
WHERE INOUT_TYPE = 'IN'
AND RTRIM(LTRIM(IDNO)) = T2.EMP_ID
AND T2.DEPT_CD = T3.DEPT_CD
AND T1.CARD_TYPE = '1'
AND DATEPART(HOUR,INOUT_DT) >= '17'
AND SUBSTRING(INOUT_DT, 12, 2) + SUBSTRING(INOUT_DT, 15, 2) > '2230'
AND CONVERT(VARCHAR(10),T1.INOUT_DT,120) >= @FST_MM_DATE AND CONVERT(VARCHAR(10),T1.INOUT_DT,120) < =@NXT_MM_DATE
GROUP BY RTRIM(LTRIM(IDNO)), SUBSTRING(INOUT_DT, 1, 10)
INSERT INTO T_DM_DH_LDG(
COMP_CD
,CORP_CD
,EMP_ID
,TEAM_CD
,GRP_CD
,LDG_DATE
,LDG_DT
,LDG_TYPE
,LDG_LOC
,CREATE_DT
,CREATE_USR_ID
,UPDATE_DT
,UPDATE_USR_ID
)
SELECT MIN(T3.COMP_CD) COMP_CD
,MIN(T3.CORP_CD) CORP_CD
,RTRIM(LTRIM(IDNO)) EMP_ID
,MIN(T3.TEAM_CD) TEAM_CD
,MIN(T3.GROUP_CD) GROUP_CD
,SUBSTRING(T1.INOUT_DT, 1, 10) LDG_DATE
,REPLACE(REPLACE(MIN(T1.INOUT_DT),'T',' '),'+08:00','') LDG_DT--T1.INOUT_DT LDG_DT
,MIN(T1.INOUT_TYPE) LDG_TYPE
,MIN(T1.LOC_NM) LDG_LOC
,GETDATE()
,@USER_ID
,GETDATE()
,@USER_ID
FROM T_DI_V2_LDG T1
JOIN T_DM_BT_USR T2 ON RTRIM(LTRIM(IDNO)) = T2.EMP_ID
JOIN TB_FR_DEPT_INFO T3 ON T2.DEPT_CD = T3.DEPT_CD
WHERE INOUT_TYPE = 'IN'
AND T1.CARD_TYPE = '1'
AND DATEPART(HOUR,INOUT_DT) >= '17'
AND SUBSTRING(INOUT_DT, 12, 2) + SUBSTRING(INOUT_DT, 15, 2) > '2230'
AND CONVERT(VARCHAR(10),T1.INOUT_DT,120) >= @FST_MM_DATE AND CONVERT(VARCHAR(10),T1.INOUT_DT,120) < @NXT_MM_DATE
AND CONVERT(VARCHAR(10),T1.INOUT_DT,120) >= CONVERT(VARCHAR(10),T2.BT_FR_DATE,120)
AND CONVERT(VARCHAR(10),T1.INOUT_DT,120) <= CONVERT(VARCHAR(10),DATEADD(DAY,-1,T2.BT_TO_DATE),120)
GROUP BY RTRIM(LTRIM(IDNO)), SUBSTRING(INOUT_DT, 1, 10)
/*插入集中工作表数据*/
DELETE FROM T_DM_DH_WKT
WHERE WKT_DATE >= @FST_MM_DATE AND WKT_DATE < @NXT_MM_DATE
--SELECT 'TEST_MM_DATE',@FST_MM_DATE,@NXT_MM_DATE
/*DECLARE @DI_V2_BFGATE TABLE (
RN NUMERIC(19,0)
, WORKDT VARCHAR(20)
, IDNO VARCHAR(20)
, INOUTTIME VARCHAR(20)
, INOUTGBNCD VARCHAR(20)
, IF_SQ BIGINT
--, WKT_TOT_TM NUMERIC(19,0)
);
--INSERT INTO @DI_V2_BFGATE*/
SELECT ROW_NUMBER()OVER(PARTITION BY T.WORKDT,IDNO ORDER BY INOUTTIME) RN
,T.WORKDT
,T.IDNO
,T.INOUTTIME
,T.INOUTGBNCD
--,T2.OVTM_DT
--,T3.TMOFF_DT
--,T3.TMOFF_NM
--,T3.TMOFF_TYPE
,IF_SQ INTO #DI_V2_BFGATE
FROM T_DI_V2_BFGATE T
--WHERE WORKDT >= '20170101' AND WORKDT < '20170201'
WHERE WORKDT >= @YEAR+@MONTH+'01' AND WORKDT < @NXT_MM_DATE_YYYYMMDD
AND IsNumeric(IDNO) = 1
--(1) 插入上午集中工作时间违反记录
DECLARE @WKT_TEMP TABLE (
WORKDT VARCHAR(20)
, IDNO VARCHAR(20)
, OUT_DT DATETIME
, IN_DT DATETIME
--, WKT_TOT_TM NUMERIC(19,0)
);
--插入集中工作时间内的所有数据，且整理数据格式为一条ID IN OUT
INSERT INTO @WKT_TEMP
SELECT WORKDT
,IDNO
,OUT_DT
,IN_DT
--,DATEDIFF(MI,OUT_DT,CONVERT(DATETIME,SUBSTRING(LEFT(IN_DT,8)+' ' + SUBSTRING(IN_DT,9,2)+':' + SUBSTRING(IN_DT,11,2)+':' + SUBSTRING(IN_DT,13,2),1,20))) WKT_TOT_TM
FROM(
SELECT T1.WORKDT
,T1.IDNO
,CAST(SUBSTRING(T1.INOUTTIME,0,9) AS DATE) WKT_DATE
,CONVERT(DATETIME,SUBSTRING(LEFT(T1.INOUTTIME,8)+' ' + SUBSTRING(T1.INOUTTIME,9,2)+':' + SUBSTRING(T1.INOUTTIME,11,2)+':' + SUBSTRING(T1.INOUTTIME,13,2),1,20)) OUT_DT
,CONVERT(DATETIME,SUBSTRING(LEFT(T2.INOUTTIME,8)+' ' + SUBSTRING(T2.INOUTTIME,9,2)+':' + SUBSTRING(T2.INOUTTIME,11,2)+':' + SUBSTRING(T2.INOUTTIME,13,2),1,20)) IN_DT
,T1.INOUTGBNCD
,T2.INOUTGBNCD INOUTGBNCD1
FROM #DI_V2_BFGATE T1
LEFT JOIN #DI_V2_BFGATE T2 ON (T2.IDNO = T1.IDNO
AND T2.WORKDT = T1.WORKDT
AND T2.RN = T1.RN + 1
AND IsNumeric(T2.IDNO) = 1
AND T2.INOUTGBNCD != T1.INOUTGBNCD)
WHERE T1.INOUTGBNCD = 'OUT'
-- AND T1.IDNO = '12587526' --test case
AND T1.WORKDT >= @YEAR+@MONTH+'01' AND T1.WORKDT < @NXT_MM_DATE_YYYYMMDD
AND RIGHT(T1.INOUTTIME,6) >= '090000' AND RIGHT(T1.INOUTTIME,6) <= '110000'
--OR RIGHT(T1.INOUTTIME,6) >= '140000' AND RIGHT(T1.INOUTTIME,6) <= '150000')
)T
/*SELECT 'TEST YANGLEI @WKT_TEMP_SPEED',*
FROM @WKT_TEMP
WHERE IDNO LIKE '%12587526%'*/
DECLARE @WKT_START VARCHAR(30) = '09:00:00.000';
DECLARE @WKT_END VARCHAR(30) = '11:00:00.000';
DECLARE @WKT_INTERAL VARCHAR(30) = 10;--定义违反时间差在10分钟
INSERT INTO T_DM_DH_WKT(
COMP_CD
,CORP_CD
,EMP_ID
,TEAM_CD
,GRP_CD
,WKT_DATE
,OUT_DT
,ENT_DT
,WKT_TOT_TM
,CREATE_DT
,CREATE_USR_ID
,UPDATE_DT
,UPDATE_USR_ID
)
SELECT T.COMP_CD
,T.CORP_CD
,T.EMP_ID
,T.TEAM_CD
,T.GROUP_CD
,T.WORKDT
,T.OUT_DT
,T.IN_DT
,T.WKT_TOT_TM
,GETDATE()
,'ZL'
,GETDATE()
,'ZL'
FROM (
SELECT TT.COMP_CD
,TT.CORP_CD
,TT.EMP_ID
,TT.TEAM_CD
,TT.GROUP_CD
,TT.WORKDT
,TT.OUT_DT
,TT.IN_DT
,CASE WHEN TT.OUT_DT_MI < @WKT_START AND TT.IN_DT_MI >= @WKT_START THEN DATEDIFF(MI,@WKT_START,IN_DT_MI)
WHEN (TT.OUT_DT_MI < @WKT_END AND TT.IN_DT_MI >= @WKT_END)
-- OR (TT.OUT_DT_MI < @WKT_END AND TT.IN_DT_MI IS NULL)
THEN DATEDIFF(MI,OUT_DT_MI,@WKT_END)
ELSE DATEDIFF(MI,OUT_DT,IN_DT)
END WKT_TOT_TM
,GETDATE() CREATE_DT
,'ZL' CREATE_USR_ID
,GETDATE() UPDATE_DT
,'ZL' UPDATE_USR_ID
FROM(
SELECT T3.COMP_CD
,T3.CORP_CD
,T2.EMP_ID
,T3.TEAM_CD
,T3.GROUP_CD
,T1.WORKDT
,T1.OUT_DT
,T1.IN_DT
,CONVERT(VARCHAR,T1.OUT_DT,8) OUT_DT_MI
,CONVERT(VARCHAR,T1.IN_DT,8) IN_DT_MI
-- ,T1.IN_DT
-- ,SUM(WKT_TOT_TM)OVER(PARTITION BY IDNO) FLAG
FROM @WKT_TEMP T1
JOIN TB_FR_USER_INFO T2 ON (T1.IDNO = T2.EMP_ID)
JOIN TB_FR_DEPT_INFO T3 ON (T2.DEPT_CD = T3.DEPT_CD)
WHERE (CONVERT(VARCHAR,OUT_DT,8) <= @WKT_START AND OUT_DT IS NOT NULL )
OR (CONVERT(VARCHAR,OUT_DT,8) < @WKT_END AND CONVERT(VARCHAR,IN_DT,8) >= @WKT_START)
AND T1.WORKDT >= @YEAR+@MONTH+01
AND T1.WORKDT < DATEADD(MONTH, 1, CAST(@YEAR + @MONTH + '01' AS DATE))
)TT
WHERE CASE WHEN TT.OUT_DT < @WKT_START AND TT.IN_DT >= @WKT_START THEN DATEDIFF(MI,@WKT_START,IN_DT)
WHEN TT.OUT_DT < @WKT_END AND TT.IN_DT >= @WKT_END THEN DATEDIFF(MI,OUT_DT,@WKT_END)
ELSE DATEDIFF(MI,OUT_DT,IN_DT)
END > @WKT_INTERAL
--AND TT.EMP_ID = '12587526'
)T
LEFT JOIN T_SI_GHR_OVTM T2 ON (
T.EMP_ID = T2.EMP_ID
AND T.WORKDT = REPLACE(CONVERT(VARCHAR(10),T2.OVTM_DT,120),'-','')
)
LEFT JOIN T_SI_GHR_TIME_OFF T3 ON (
T.EMP_ID = T3.EMP_ID
AND T.WORKDT = REPLACE(CONVERT(VARCHAR(10),T3.TMOFF_DT,120),'-','')
)
WHERE T2.TIME_WEEK_CD = '1' AND T2.SHIFT_TYPE = 'OFMW' AND T3.TMOFF_TYPE IN ('YC')
OR (T2.TIME_WEEK_CD = '1' AND T2.SHIFT_TYPE = 'OFMW' AND T3.TMOFF_TYPE IN ('NB') AND T.OUT_DT < T3.TMOFF_FR_TIME)
OR (T2.TIME_WEEK_CD = '1' AND T2.SHIFT_TYPE = 'OFMW' AND T3.TMOFF_TYPE IN ('NB') AND T.OUT_DT > T3.TMOFF_TO_TIME)
OR T3.TMOFF_TYPE IS NULL
SELECT 'TEST YANGLEI',*
FROM T_DM_DH_WKT
WHERE EMP_ID LIKE '%12587526%'
--SELECT 'SHANGWU TONGJI JIEGUO '
--(2)插入下午集中工作时间违反记录
DELETE FROM @WKT_TEMP;
INSERT INTO @WKT_TEMP
SELECT WORKDT
,IDNO
,OUT_DT
,CONVERT(DATETIME,SUBSTRING(LEFT(IN_DT,8)+' ' + SUBSTRING(IN_DT,9,2)+':' + SUBSTRING(IN_DT,11,2)+':' + SUBSTRING(IN_DT,13,2),1,20)) IN_DT
--,DATEDIFF(MI,OUT_DT,CONVERT(DATETIME,SUBSTRING(LEFT(IN_DT,8)+' ' + SUBSTRING(IN_DT,9,2)+':' + SUBSTRING(IN_DT,11,2)+':' + SUBSTRING(IN_DT,13,2),1,20))) WKT_TOT_TM
FROM(
SELECT WORKDT
,T1.IDNO
,CAST(SUBSTRING(T1.INOUTTIME,0,9) AS DATE) WKT_DATE
,CONVERT(DATETIME,SUBSTRING(LEFT(T1.INOUTTIME,8)+' ' + SUBSTRING(T1.INOUTTIME,9,2)+':' + SUBSTRING(T1.INOUTTIME,11,2)+':' + SUBSTRING(T1.INOUTTIME,13,2),1,20)) OUT_DT
,(SELECT T2.INOUTTIME
FROM #DI_V2_BFGATE T2
WHERE T2.IDNO = T1.IDNO
AND T2.WORKDT = T1.WORKDT
AND T2.RN = T1.RN + 1
AND T2.INOUTGBNCD != T1.INOUTGBNCD) IN_DT
,T1.INOUTGBNCD
,(SELECT T2.INOUTGBNCD
FROM #DI_V2_BFGATE T2
WHERE T2.IDNO = T1.IDNO
AND T2.WORKDT = T1.WORKDT
AND T2.RN = T1.RN + 1
AND T2.INOUTGBNCD != T1.INOUTGBNCD) INOUTGBNCD1
FROM #DI_V2_BFGATE T1
WHERE T1.INOUTGBNCD = 'OUT'
AND WORKDT >= @YEAR+@MONTH+'01' AND WORKDT < @NXT_MM_DATE_YYYYMMDD
--AND T1.INOUTTIME > '2016092910' AND T1.INOUTTIME < '2016092912'
AND RIGHT(T1.INOUTTIME,6) >= '140000' AND RIGHT(T1.INOUTTIME,6) <= '160000'
)T
DECLARE @WKT_START_PM VARCHAR(30) = '14:00:00.000';
DECLARE @WKT_END_PM VARCHAR(30) = '16:00:00.000';
DECLARE @WKT_INTERAL_PM VARCHAR(30) = 10;--定义违反时间差在10分钟
INSERT INTO T_DM_DH_WKT(
COMP_CD
,CORP_CD
,EMP_ID
,TEAM_CD
,GRP_CD
,WKT_DATE
,OUT_DT
,ENT_DT
,WKT_TOT_TM
,CREATE_DT
,CREATE_USR_ID
,UPDATE_DT
,UPDATE_USR_ID
)
SELECT T.*
FROM(
SELECT TT.COMP_CD
,TT.CORP_CD
,TT.EMP_ID
,TT.TEAM_CD
,TT.GROUP_CD
,TT.WORKDT
,TT.OUT_DT
,TT.IN_DT
,CASE WHEN TT.OUT_DT_MI < @WKT_START_PM AND TT.IN_DT_MI >= @WKT_START_PM THEN DATEDIFF(MI,@WKT_START_PM,IN_DT_MI)
WHEN (TT.OUT_DT_MI < @WKT_END_PM AND TT.IN_DT_MI >= @WKT_END_PM)
-- OR (TT.OUT_DT_MI < @WKT_END AND TT.IN_DT_MI IS NULL)
THEN DATEDIFF(MI,OUT_DT_MI,@WKT_END_PM)
ELSE DATEDIFF(MI,OUT_DT,IN_DT)
END WKT_TOT_TM
,GETDATE() CRT_DT
,'ZL' CRT_ID
,GETDATE() UPT_DT
,'ZL' UPT_ID
FROM(
SELECT T3.COMP_CD
,T3.CORP_CD
,T2.EMP_ID
,T3.TEAM_CD
,T3.GROUP_CD
,T1.WORKDT
,T1.OUT_DT
,T1.IN_DT
,CONVERT(VARCHAR,T1.OUT_DT,8) OUT_DT_MI
,CONVERT(VARCHAR,T1.IN_DT,8) IN_DT_MI
-- ,SUM(WKT_TOT_TM)OVER(PARTITION BY IDNO) FLAG
FROM @WKT_TEMP T1
JOIN TB_FR_USER_INFO T2 ON (T1.IDNO = T2.EMP_ID)
JOIN TB_FR_DEPT_INFO T3 ON (T2.DEPT_CD = T3.DEPT_CD)
WHERE (CONVERT(VARCHAR,OUT_DT,8) <= @WKT_START_PM AND OUT_DT IS NOT NULL )
OR (CONVERT(VARCHAR,OUT_DT,8) < @WKT_END_PM AND CONVERT(VARCHAR,IN_DT,8) >= @WKT_START_PM)
AND T1.WORKDT >= @YEAR+@MONTH+01
AND T1.WORKDT < DATEADD(MONTH, 1, CAST(@YEAR + @MONTH + '01' AS DATE))
)TT
WHERE CASE WHEN TT.OUT_DT < @WKT_START_PM AND TT.IN_DT >= @WKT_START_PM THEN DATEDIFF(MI,@WKT_START_PM,IN_DT)
WHEN TT.OUT_DT < @WKT_END_PM AND TT.IN_DT >= @WKT_END_PM THEN DATEDIFF(MI,OUT_DT,@WKT_END_PM)
ELSE DATEDIFF(MI,OUT_DT,IN_DT)
END > @WKT_INTERAL_PM
)T
LEFT JOIN T_SI_GHR_OVTM T2 ON (
T.EMP_ID = T2.EMP_ID
AND T.WORKDT = REPLACE(CONVERT(VARCHAR(10),T2.OVTM_DT,120),'-','')
)
LEFT JOIN T_SI_GHR_TIME_OFF T3 ON (
T.EMP_ID = T3.EMP_ID
AND T.WORKDT = REPLACE(CONVERT(VARCHAR(10),T3.TMOFF_DT,120),'-','')
)
WHERE T2.TIME_WEEK_CD = '1' AND T2.SHIFT_TYPE = 'OFMW' AND T3.TMOFF_TYPE IN ('YC')
OR (T2.TIME_WEEK_CD = '1' AND T2.SHIFT_TYPE = 'OFMW' AND T3.TMOFF_TYPE IN ('NB') AND T.OUT_DT < T3.TMOFF_FR_TIME)
OR (T2.TIME_WEEK_CD = '1' AND T2.SHIFT_TYPE = 'OFMW' AND T3.TMOFF_TYPE IN ('NB') AND T.OUT_DT > T3.TMOFF_TO_TIME)
OR T3.TMOFF_TYPE IS NULL
DROP TABLE #DI_V2_BFGATE
--------GROUP 部门人数--------------------
DECLARE @DEPT_LIST_CNT AS TABLE
(
COMP_CD VARCHAR(20)
, CORP_CD VARCHAR(20)
, TEAM_CD VARCHAR(20)
, TEAM_NM NVARCHAR(100)
, DEPT_CD VARCHAR(20)
, DEPT_NM NVARCHAR(100)
, ORG_LVL_CD VARCHAR(20)
, GRP_CNT INT
);
INSERT INTO @DEPT_LIST_CNT
SELECT T2.COMP_CD, T2.CORP_CD, T2.TEAM_CD, T2.TEAM_NM, T2.GROUP_CD, T2.GROUP_NM
,MAX(T2.ORG_LVL_CD) ORG_LVL_CD
,COUNT(T1.USER_ID) DEPT_CNT
FROM TB_FR_USER_INFO T1
JOIN TB_FR_DEPT_INFO T2 ON T1.DEPT_CD = T2.DEPT_CD
--WHERE T1.S_EMP_TYPE IN ( 'AA', 'BA', 'EA' ) -- 在职, Assignment, Assignment Return
WHERE T1.USER_TYPE IN ( 'AAA','BAA', 'DA1' )
AND T1.RETIRE_DATE IS NULL
AND T1.USER_STATUS != 'DA'
GROUP BY T2.COMP_CD, T2.CORP_CD, T2.TEAM_CD, T2.TEAM_NM, T2.GROUP_CD, T2.GROUP_NM
---------------------TEAM 人数---------------------------
DECLARE @TEAM_GRP_LIST_CNT AS TABLE
(
COMP_CD VARCHAR(20)
,CORP_CD VARCHAR(20)
,TEAM_CD VARCHAR(20)
, TEAM_CNT INT
, GRP_CD NVARCHAR(100)
, ORG_LVL_CD VARCHAR(20)
, GRP_CNT INT
);
INSERT INTO @TEAM_GRP_LIST_CNT
SELECT T1.COMP_CD
,T1.CORP_CD
,T1.TEAM_CD
,T1.TEAM_CNT
,T2.DEPT_CD GRP_CD
,T2.ORG_LVL_CD
,T2.GRP_CNT
FROM(
SELECT T.COMP_CD
,T.CORP_CD
,T.TEAM_CD
,MAX(ORG_LVL_CD) ORG_LVL_CD
,SUM(T.GRP_CNT) TEAM_CNT
FROM @DEPT_LIST_CNT T
GROUP BY T.COMP_CD
,T.CORP_CD
,T.TEAM_CD
) T1
INNER JOIN @DEPT_LIST_CNT T2 ON (T1.TEAM_CD = T2.TEAM_CD
AND T1.COMP_CD = T2.COMP_CD
AND T1.CORP_CD = T2.CORP_CD)
-- SELECT *
--FROM @TEAM_GRP_LIST_CNT
--------2构造-加班偏重度数据--------------------------
--1 部门前10%平均加班小时数
/*--------本地GROUP 部门人数--------------------*/
/*--------本地GROUP 部门人数--------------------*/
DECLARE @LR_DEPT_LIST_CNT AS TABLE
(
COMP_CD VARCHAR(20)
, CORP_CD VARCHAR(20)
, TEAM_CD VARCHAR(20)
, TEAM_NM NVARCHAR(100)
, DEPT_CD VARCHAR(20)
, DEPT_NM NVARCHAR(100)
, ORG_LVL_CD VARCHAR(20)
, GRP_CNT INT
);
INSERT INTO @LR_DEPT_LIST_CNT
SELECT T2.COMP_CD, T2.CORP_CD, T2.TEAM_CD, T2.TEAM_NM, T2.GROUP_CD, T2.GROUP_NM
,MAX(T2.ORG_LVL_CD) ORG_LVL_CD ,COUNT(T1.USER_ID) DEPT_CNT
FROM TB_FR_USER_INFO T1
JOIN TB_FR_DEPT_INFO T2 ON T1.DEPT_CD = T2.DEPT_CD
WHERE T1.USER_TYPE IN ( 'AAA', 'DA1' )
GROUP BY T2.COMP_CD, T2.CORP_CD, T2.TEAM_CD, T2.TEAM_NM, T2.GROUP_CD, T2.GROUP_NM
/*----------本地TEAM 人数------------------*/
DECLARE @LR_TEAM_GRP_LIST_CNT AS TABLE
(
COMP_CD VARCHAR(20)
,CORP_CD VARCHAR(20)
,TEAM_CD VARCHAR(20)
, TEAM_CNT INT
, GRP_CD NVARCHAR(100)
, ORG_LVL_CD VARCHAR(20)
, GRP_CNT INT
);
INSERT INTO @LR_TEAM_GRP_LIST_CNT
SELECT T1.COMP_CD
,T1.CORP_CD
,T1.TEAM_CD
,T1.TEAM_CNT
,T2.DEPT_CD GRP_CD
,ORG_LVL_CD
,T2.GRP_CNT
FROM(
SELECT T.COMP_CD
,T.CORP_CD
,T.TEAM_CD
,SUM(T.GRP_CNT) TEAM_CNT
FROM @LR_DEPT_LIST_CNT T
GROUP BY T.COMP_CD
,T.CORP_CD
,T.TEAM_CD
) T1
INNER JOIN @LR_DEPT_LIST_CNT T2 ON (T1.TEAM_CD = T2.TEAM_CD
AND T1.COMP_CD = T2.COMP_CD
AND T1.CORP_CD = T2.CORP_CD)
--SELECT * FROM @LR_TEAM_GRP_LIST_CNT WHERE ORG_LVL_CD = 'TM'
/*---------查询需要的所有group-----------------------*/
SELECT * INTO #TEMP
FROM(
SELECT DISTINCT T1.TEAM_CD,T1.GROUP_CD GRP_CD, T1.TEAM_NM,T1.GROUP_NM GRP_NM--,T2.GRP_CD
FROM TB_FR_DEPT_INFO T1
WHERE T1.ORG_LVL_CD NOT IN('CP','TM')
AND T1.GROUP_CD IS NOT NULL
AND T1.GROUP_CD != 'EA190080'
)T
/*一 --------------------各部门每个人加班时间的百分比----------------------------------------------- */
DECLARE @GRP_PERCNT AS TABLE
(
PERCNT NUMERIC(19,2)
,TEAM_CD VARCHAR(20)
,TEAM_NM NVARCHAR(50)
,GRP_CD VARCHAR(20)
,GRP_NM NVARCHAR(50)
,GRP_CNT INT
,ORG_LVL_CD VARCHAR(20)
, SQ INT
, EMP_ID NVARCHAR(100)
, OVT_TOT_TM NUMERIC(19,2)
);
INSERT INTO @GRP_PERCNT
SELECT CAST(CAST(T1.SQ AS NUMERIC(19,2)) / CAST(T2.GRP_CNT AS NUMERIC(19,2)) * 100 AS NUMERIC(19, 2)) PERCNT
--CAST(T1.SQ / T2.GRP_CNT * 100.0 AS NUMERIC(19, 2)) PERCNT
, T3.TEAM_CD
, T3.TEAM_NM
, T3.GRP_CD
, T3.GRP_NM
, T2.GRP_CNT
, T2.ORG_LVL_CD
, T1.SQ
, EMP_ID
, T1.OVT_TOT_TM
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY GRP_CD ORDER BY OVT_TOT_TM DESC) AS SQ
, TEAM_CD
, GRP_CD
, EMP_ID
, CAST(OVT_TOT_TM/60 AS decimal(19,1)) OVT_TOT_TM
FROM T_DM_DH_OVT
WHERE DH_YEAR = @YEAR AND DH_MONTH = @MONTH
--AND GRP_CD = 'EA190086'
) T1
JOIN @LR_TEAM_GRP_LIST_CNT T2 ON T1.GRP_CD = T2.GRP_CD
JOIN #TEMP T3 ON T1.GRP_CD = T3.GRP_CD
-- SELECT * FROM #TEMP
/*----------------------------各部门前10%加班平均小时数------------------------------------------------------*/
SELECT TEAM_CD,GRP_CD
,TEAM_NM,GRP_NM
,COUNT(EMP_ID) TOP10_CNT /*--前10%加班的人数*/
,SUM(OVT_TOT_TM) TOP10_TOT_TM--前10%加班人员加班小时总和
,SUM(OVT_TOT_TM)/COUNT(EMP_ID) DEPT_TOP10_HOUR INTO #DEPT_TOP10_HOUR
FROM @GRP_PERCNT
WHERE PERCNT <= 10
AND GRP_CNT >10
GROUP BY TEAM_CD,GRP_CD,TEAM_NM,GRP_NM
UNION
SELECT TEAM_CD,GRP_CD--,ORG_LVL_CD
,TEAM_NM,GRP_NM
,1 TOP10_CNT --部门人数小于10人的部门,10%加班人数为1
,SUM(OVT_TOT_TM) TOP10_TOT_TM--前10%加班人员加班小时数总和
,SUM(OVT_TOT_TM)/COUNT(EMP_ID)
FROM @GRP_PERCNT
WHERE ORG_LVL_CD != 'TM'
AND GRP_CNT <= 10
GROUP BY TEAM_CD,GRP_CD,TEAM_NM,GRP_NM
/*-------------------------------------------------------------------------------------------------*/
/*二--------------------各TEAM每个人加班时间的百分比-----------------------------------------------*/
DECLARE @TEAM_PERCNT AS TABLE
(
PERCNT NUMERIC(19,2)
,TEAM_CD VARCHAR(20)
,TEAM_NM NVARCHAR(50)
,GRP_CD VARCHAR(20)
,GRP_NM NVARCHAR(50)
,GRP_CNT INT
,ORG_LVL_CD VARCHAR(20)
, SQ INT
, EMP_ID NVARCHAR(100)
, OVT_TOT_TM NUMERIC(19,2)
);
INSERT INTO @TEAM_PERCNT
SELECT CAST(CAST(T1.SQ AS NUMERIC(19,2)) / CAST(T2.TEAM_CNT AS NUMERIC(19,2)) * 100 AS NUMERIC(19, 2)) PERCNT
--CAST(T1.SQ / T2.GRP_CNT * 100.0 AS NUMERIC(19, 2)) PERCNT
, T2.TEAM_CD
, T2.TEAM_NM
, '-' GRP_CD
, '-' GRP_NM
, T2.TEAM_CNT
, 'TM' ORG_LVL_CD
, T1.SQ
, EMP_ID
, T1.OVT_TOT_TM
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY GRP_CD ORDER BY OVT_TOT_TM DESC) AS SQ
, TEAM_CD
, GRP_CD
, EMP_ID
, CAST(OVT_TOT_TM/60 AS decimal(19,1)) OVT_TOT_TM
FROM T_DM_DH_OVT
WHERE DH_YEAR = @YEAR AND DH_MONTH = @MONTH
--AND GRP_CD = 'EA190086'
) T1
RIGHT JOIN(
SELECT DISTINCT T1.TEAM_CD,T1.TEAM_NM,T2.TEAM_CNT
FROM #TEMP T1
LEFT JOIN @LR_TEAM_GRP_LIST_CNT T2 ON T1.TEAM_CD = T2.TEAM_CD
)T2 ON T1.TEAM_CD = T2.TEAM_CD
----------------------------各TEAM前10%加班平均小时数------------------------------------------------------
SELECT TEAM_CD,'-' GRP_CD--,MAX(ORG_LVL_CD) ORG_LVL_CD
,TEAM_NM,'-' GRP_NM
,COUNT(EMP_ID) TOP10_CNT --前10%加班的人数
,SUM(OVT_TOT_TM) TOP10_TOT_TM--前10%加班人员加班小时总和
,SUM(OVT_TOT_TM)/COUNT(EMP_ID) TEAM_HOUR10_CNT INTO #TEAM_HOUR10_CNT
FROM @TEAM_PERCNT
WHERE PERCNT <= 10
AND GRP_CNT >10
GROUP BY TEAM_CD,TEAM_NM
-- ORDER BY TEAM_CD
UNION
SELECT TEAM_CD,'-' GRP_CD--,ORG_LVL_CD
,TEAM_NM,'-' GRP_NM
,1 TOP10_CNT --TEAM人数小于10人的TEAM,10%加班人数为1
,SUM(OVT_TOT_TM) TOP10_TOT_TM--前10%加班人员加班小时数总和
,SUM(OVT_TOT_TM)/COUNT(EMP_ID)
FROM @TEAM_PERCNT
WHERE ORG_LVL_CD != 'TM'
AND GRP_CNT <= 10
GROUP BY TEAM_CD,TEAM_NM
/*------------------------------------------------------------------------------------------------*/
/*--------------------各法人每个人加班时间的百分比-----------------------------------------------*/
DECLARE @SUB_PERCNT AS TABLE
(
PERCNT NUMERIC(19,2)
,TEAM_CD VARCHAR(20)
,TEAM_NM NVARCHAR(50)
,GRP_CD VARCHAR(20)
,GRP_NM NVARCHAR(50)
,GRP_CNT INT
,ORG_LVL_CD VARCHAR(20)
, SQ INT
, EMP_ID NVARCHAR(100)
, OVT_TOT_TM NUMERIC(19,2)
);
INSERT INTO @SUB_PERCNT
SELECT CAST(CAST(T1.SQ AS NUMERIC(19,2)) / CAST(SUB_CNT AS NUMERIC(19,2)) * 100 AS NUMERIC(19, 2)) PERCNT
, '-' TEAM_CD
, '-' TEAM_NM
, '-' GRP_CD
, '-' GRP_NM
, SUB_CNT
, 'CP' ORG_LVL_CD
, T1.SQ
, EMP_ID
, T1.OVT_TOT_TM
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY OVT_TOT_TM DESC) AS SQ
, '-' TEAM_CD
, '-' GRP_CD
, EMP_ID
, CAST(OVT_TOT_TM/60 AS decimal(19,1)) OVT_TOT_TM
FROM T_DM_DH_OVT
WHERE DH_YEAR = @YEAR AND DH_MONTH = @MONTH
) T1
,( /*--查询本地员工总数*/
SELECT T2.COMP_CD, T2.CORP_CD
,'CP' ORG_LVL_CD ,COUNT(DISTINCT T1.USER_ID) SUB_CNT
FROM TB_FR_USER_INFO T1
JOIN TB_FR_DEPT_INFO T2 ON T1.DEPT_CD = T2.DEPT_CD
WHERE T1.USER_TYPE IN ( 'AAA', 'DA1' )
GROUP BY T2.COMP_CD, T2.CORP_CD
)T2
----------------------------各法人前10%加班平均小时数------------------------------------------------------
SELECT '-' TEAM_CD,'-' GRP_CD--,MAX(ORG_LVL_CD) ORG_LVL_CD
,'-' TEAM_NM,'-' GRP_NM
,COUNT(EMP_ID) TOP10_CNT --前10%加班的人数
,SUM(OVT_TOT_TM) TOP10_TOT_TM--前10%加班人员加班小时总和
,SUM(OVT_TOT_TM)/COUNT(EMP_ID) SUB_HOUR10_CNT INTO #SUB_HOUR10_CNT
FROM @SUB_PERCNT
WHERE PERCNT <= 10
AND GRP_CNT >10
DELETE FROM T_DM_DH_OVP WHERE DH_YEAR = @YEAR AND DH_MONTH =@MONTH
INSERT INTO T_DM_DH_OVP(
COMP_CD
,CORP_CD
,TEAM_CD
,GRP_CD
,DH_YEAR
,DH_MONTH
--,OVT_AVG_TM
,OVT_TOP_TM
--,OVP_VAL
--,DH_RMK
--,DH_STATUS
--,MO
,CREATE_DT
,CREATE_USR_ID
,UPDATE_DT
,UPDATE_USR_ID
)
SELECT 'C10' COMP_CD
,'EA19' CORP_CD
,TEAM_CD
,T1.GRP_CD GRP_CD
,@YEAR--@YEAR DH_YEAR
,@MONTH--@MONTH DH_MONTH
-- ,SUM(OVT_TOT_TM) OVT_AVG_TM
-- ,T2.CNT) TOT_PAX_CNT
--,SUM(CAST(OVT_TOT_TM/60 AS decimal(19,1))) / T2.GRP_CNT) OVT_AVG_TM --部门平均加班小时数=部门加班小时总数/部门（外包员工+正式员工）人数
,T1.DEPT_TOP10_HOUR OVT_TOP_TM
--,T3.HOUR_TOP10) - SUM(CAST(OVT_TOT_TM/60 AS decimal(19,1))) / T2.GRP_CNT) OVP_VAL
--,T1.DH_RMK DH_RMK
--,T1.DH_STATUS) DH_STATUS
-- ,T1.MO) MO
,GETDATE() CREATE_DT
,'SCHR' CREATE_USR_ID
,GETDATE() UPDATE_DT
,'SCHR' UPDATE_USR_ID
FROM(
SELECT *
FROM #DEPT_TOP10_HOUR
UNION
SELECT *
FROM #TEAM_HOUR10_CNT
UNION
SELECT *
FROM #SUB_HOUR10_CNT
)T1
DROP TABLE #TEMP
DROP TABLE #DEPT_TOP10_HOUR
DROP TABLE #SUB_HOUR10_CNT
DROP TABLE #TEAM_HOUR10_CNT
------------2 构造统计数据 T_DM_DHSTC
--这时 T_DM_DHSTC表中的CYM_YN是null或N 都需要重新插入
DELETE FROM T_DM_DHSTC
WHERE DH_YEAR = @YEAR AND DH_MONTH = @MONTH
INSERT INTO T_DM_DHSTC(
DHSTC_CD
,DH_YEAR
,DH_MONTH
,CREATE_DT
,CREATE_USR_ID
,UPDATE_DT
,UPDATE_USR_ID)
VALUES(@YEAR+@MONTH,@YEAR,@MONTH,GETDATE(),@USER_ID,GETDATE(),@USER_ID);
END
SET @END_DT = CONVERT(VARCHAR, GETDATE(), 121);
SET @ERROR_MSG = 'Success';
------------------------二 统计各业务表数据-------------------------------------------------
BEGIN
/* SELECT 'shijian'
,MAX(DH_TYPE) DH_TYPE
,GRP_CD
,COUNT(*) DH_CNT
,SUM(CASE WHEN TRB_GRADE = 'R' THEN 1 ELSE 0 END) AS H05_R
,SUM(CASE WHEN TRB_GRADE = 'Y' THEN 1 ELSE 0 END) AS H05_Y
FROM T_DM_DH_TRB
WHERE DEL_YN = 'N'
AND DH_STATUS = 'Y'
AND TRB_DATE >= @FST_MM_DATE AND TRB_DATE < @NXT_MM_DATE
GROUP BY GRP_CD*/
-------------事件-----------------
DECLARE @DH_TRB TABLE (
DH_TYPE VARCHAR(20)
, GRP_CD VARCHAR(20)
, DH_CNT VARCHAR(20)
, H05_R VARCHAR(20)
, H05_Y VARCHAR(20)
);
INSERT INTO @DH_TRB
SELECT MAX(DH_TYPE) DH_TYPE
,GRP_CD
,COUNT(*) DH_CNT
,SUM(CASE WHEN TRB_GRADE = 'R' THEN 1 ELSE 0 END) AS H05_R
,SUM(CASE WHEN TRB_GRADE = 'Y' THEN 1 ELSE 0 END) AS H05_Y
FROM T_DM_DH_TRB
WHERE DEL_YN = 'N'
AND DH_STATUS = 'Y'
AND TRB_DATE >= @FST_MM_DATE AND TRB_DATE < @NXT_MM_DATE
GROUP BY GRP_CD
-------------住宿违反-----------------
DECLARE @DH_LDG TABLE (
DH_TYPE VARCHAR(20)
, GRP_CD VARCHAR(20)
, DH_CNT VARCHAR(20)
);
INSERT INTO @DH_LDG
SELECT MAX(DH_TYPE) DH_TYPE
,GRP_CD
,COUNT(*) DH_CNT
FROM T_DM_DH_LDG
WHERE DEL_YN = 'N'
AND DH_STATUS = 'Y'
AND LDG_DATE >= @FST_MM_DATE AND LDG_DATE < @NXT_MM_DATE
GROUP BY GRP_CD
-------------上下班时间违反-----------------
DECLARE @DH_TMF TABLE (
DH_TYPE VARCHAR(20)
, GRP_CD VARCHAR(20)
, DH_CNT VARCHAR(20)
);
INSERT INTO @DH_TMF
SELECT MAX(DH_TYPE) DH_TYPE
,GRP_CD
,COUNT(*) DH_CNT
FROM T_DM_DH_TMF
WHERE TMF_DATE >= @FST_MM_DATE AND TMF_DATE < @NXT_MM_DATE
AND DH_STATUS = 'Y'
GROUP BY GRP_CD
/* SELECT 'SHANGXIANBAN'
,MAX(DH_TYPE) DH_TYPE
,GRP_CD
,COUNT(*) DH_CNT
FROM T_DM_DH_TMF
WHERE TMF_DATE >= @FST_MM_DATE AND TMF_DATE < @NXT_MM_DATE
AND DH_STATUS = 'Y'
GROUP BY GRP_CD*/
-------------集中工作时间违反-----------------
DECLARE @DH_WKT TABLE (
DH_TYPE VARCHAR(20)
, GRP_CD VARCHAR(20)
, DH_CNT VARCHAR(20)
);
INSERT INTO @DH_WKT
SELECT MAX(DH_TYPE) DH_TYPE
,GRP_CD
,COUNT(DISTINCT EMP_ID) DH_CNT
FROM T_DM_DH_WKT
WHERE WKT_DATE >= @FST_MM_DATE AND WKT_DATE < @NXT_MM_DATE
AND DH_STATUS = 'Y'
GROUP BY GRP_CD
-------------信息安全违反-----------------
DECLARE @DH_SECU TABLE (
DH_TYPE VARCHAR(20)
, GRP_CD VARCHAR(20)
, DH_CNT VARCHAR(20)
);
INSERT INTO @DH_SECU
SELECT MAX(DH_TYPE) DH_TYPE
,GRP_CD
,COUNT(*) DH_CNT
FROM T_DM_DH_SECU
WHERE SECU_DATE >= @FST_MM_DATE AND SECU_DATE < @NXT_MM_DATE
AND DEL_YN = 'N'
AND DH_STATUS = 'Y'
GROUP BY GRP_CD
-------------加班统计-----------------
DECLARE @DH_OVT TABLE (
DH_TYPE VARCHAR(20)
, GRP_CD VARCHAR(20)
, OVT_AVG_TM numeric(19,2)
);
INSERT INTO @DH_OVT
SELECT 'H06'
,GRP_CD
,SUM(OVT_TOT_TM)/MAX(GRP_CNT) OVT_AVG_TM
FROM @GRP_PERCNT
GROUP BY TEAM_CD,TEAM_NM,GRP_CD,GRP_NM
/*-------------核心人员流失-----------------
DECLARE @DH_MPR TABLE (
DH_TYPE VARCHAR(20)
, GRP_CD VARCHAR(20)
, DH_CNT VARCHAR(20)
);
INSERT INTO @DH_MPR
SELECT MAX(DH_TYPE) DH_TYPE
,GRP_CD
,COUNT(*) DH_CNT
FROM T_DM_DH_MPR
WHERE MPR_DATE >= @FST_MM_DATE AND MPR_DATE < @NXT_MM_DATE
AND DEL_YN = 'N'
AND DH_STATUS = 'Y'
GROUP BY GRP_CD*/
/* SELECT 'MPR',T.*
FROM @DH_MPR T*/
/*-------------同好会加入率统计-----------------
DECLARE @DH_CLUB TABLE (
DH_TYPE VARCHAR(20)
, GRP_CD VARCHAR(20)
, DH_CNT VARCHAR(20)
);
INSERT INTO @DH_CLUB
SELECT MAX(DH_TYPE) DH_TYPE
,GRP_CD
,COUNT(*) DH_CNT
FROM T_DM_DH_CLUB
WHERE DH_YEAR = @YEAR
AND DH_MONTH = @MONTH
AND DH_STATUS = 'Y'
GROUP BY GRP_CD*/
-------------面谈率统计-----------------
DECLARE @DH_CVS TABLE (
DH_TYPE VARCHAR(20)
, GRP_CD VARCHAR(20)
, DH_CNT VARCHAR(20)
, CVS_DEPT_PAX VARCHAR(20)
);
INSERT INTO @DH_CVS
SELECT T1.DH_TYPE, T2.GRP_CD,T1.DH_CNT,CVS_DEPT_PAX
FROM(
SELECT MAX(DH_TYPE) DH_TYPE
,GRP_CD
,COUNT(*) DH_CNT
FROM T_DM_DH_CVS
WHERE CVS_DATE >= @FST_MM_DATE AND CVS_DATE < @NXT_MM_DATE
AND CVS_YEAR = @YEAR
AND DH_STATUS = 'Y'
AND CVS_YN = 'Y'
GROUP BY GRP_CD) T1
RIGHT JOIN
( --按季度求面谈部门总人数
select GRP_CD,CVS_QUATR,COUNT(*) CVS_DEPT_PAX
from T_DM_DH_CVS
WHERE DH_STATUS = 'Y'
AND CVS_QUATR = DATEPART(QQ,@FST_MM_DATE)
AND CVS_YEAR = @YEAR
GROUP BY GRP_CD,CVS_YEAR,CVS_QUATR
)T2
ON T1.GRP_CD = T2.GRP_CD
/* SELECT 'MANDQ',@FST_MM_DATE,@NXT_MM_DATE,DATEPART(QQ,@FST_MM_DATE)
SELECT 'CVSTEST'
,T1.*
FROM @DH_CVS T1
ORDER BY GRP_CD*/
---------------事故事件预防-----------------------------
DECLARE @DH_ACM_TRB TABLE (
DH_TYPE VARCHAR(20)
, GRP_CD VARCHAR(20)
, DH_CNT VARCHAR(20)
);
INSERT INTO @DH_ACM_TRB
SELECT MAX(T1.DH_TYPE) DH_TYPE,T1.GRP_CD,SUM(T1.PAX_CNT) H11_CNT
FROM T_DM_DH_ACM T1
WHERE T1.ACM_YEAR = @YEAR
AND T1.ACM_MONTH = @MONTH
AND T1.ACM_TYPE = 'ACM10'
AND T1.DH_STATUS = 'Y'
AND T1.DEL_YN = 'N'
GROUP BY TEAM_CD,GRP_CD
---------------座谈会-----------------------------
DECLARE @DH_ACM_ZTH TABLE (
DH_TYPE VARCHAR(20)
, GRP_CD VARCHAR(20)
, DH_CNT VARCHAR(20)
);
INSERT INTO @DH_ACM_ZTH
SELECT MAX(T1.DH_TYPE) DH_TYPE,T1.GRP_CD,SUM(T1.PAX_CNT) H12_CNT
FROM T_DM_DH_ACM T1
WHERE T1.ACM_YEAR = @YEAR
AND T1.ACM_MONTH = @MONTH
AND T1.ACM_TYPE = 'ACM20'
AND T1.DH_STATUS = 'Y'
AND T1.DEL_YN = 'N'
GROUP BY TEAM_CD,GRP_CD
---------跨部门活动--------------------------
DECLARE @DH_ACM_DEPT TABLE (
DH_TYPE VARCHAR(20)
, GRP_CD VARCHAR(20)
, DH_CNT VARCHAR(20)
);
INSERT INTO @DH_ACM_DEPT
SELECT MAX(T1.DH_TYPE) DH_TYPE ,T1.GRP_CD,SUM(T1.ACM_DGR) H13_ACM_CNT
FROM T_DM_DH_ACM T1
WHERE T1.ACM_YEAR = @YEAR
AND T1.ACM_MONTH = @MONTH
AND T1.ACM_TYPE = 'ACM30'
AND T1.DH_STATUS = 'Y'
AND T1.DEL_YN = 'N'
GROUP BY TEAM_CD,GRP_CD
---------公益活动--------------------------
DECLARE @DH_ACM_GZ TABLE (
DH_TYPE VARCHAR(20)
, GRP_CD VARCHAR(20)
, PTACM_CNT VARCHAR(20)
, CXACM_CNT VARCHAR(20)
);
WITH T_MART AS (
SELECT MAX(T1.DH_TYPE) DH_TYPE ,T1.GRP_CD,T1.ACM_DGR_TYPE,SUM(T1.ACM_DGR) H13_ACM_DGR
FROM T_DM_DH_ACM T1
WHERE T1.ACM_YEAR = @YEAR
AND T1.ACM_MONTH = @MONTH
AND T1.ACM_TYPE = 'ACM40'
AND T1.DH_STATUS = 'Y'
AND T1.DEL_YN = 'N'
GROUP BY TEAM_CD,GRP_CD,ACM_DGR_TYPE
)
INSERT INTO @DH_ACM_GZ
SELECT DH_TYPE,GRP_CD
,MAX(CASE WHEN ACM_DGR_TYPE = 'ACM4010' THEN H13_ACM_DGR END) PTACM
,MAX(CASE WHEN ACM_DGR_TYPE = 'ACM4020' THEN H13_ACM_DGR END) CXACM
FROM T_MART
GROUP BY DH_TYPE,GRP_CD
DELETE FROM T_DM_DHSTC_DEPT
WHERE DHSTC_CD IN (SELECT DHSTC_CD
FROM T_DM_DHSTC
WHERE DH_YEAR = @YEAR AND DH_MONTH = @MONTH
)
/*SELECT @YEAR+@MONTH DHSTC_CD
,CASE WHEN @MONTH = '01' THEN '01'
WHEN @MONTH = '02' THEN '01'
WHEN @MONTH = '03' THEN '01'
WHEN @MONTH = '04' THEN '02'
WHEN @MONTH = '05' THEN '02'
WHEN @MONTH = '06' THEN '02'
WHEN @MONTH = '07' THEN '03'
WHEN @MONTH = '08' THEN '03'
WHEN @MONTH = '09' THEN '03'
WHEN @MONTH = '10' THEN '04'
WHEN @MONTH = '11' THEN '04'
WHEN @MONTH = '12' THEN '04'
END STC_QUATR
,CASE WHEN @MONTH = '01' THEN '01'
WHEN @MONTH = '02' THEN '02'
WHEN @MONTH = '03' THEN '03'
WHEN @MONTH = '04' THEN '01'
WHEN @MONTH = '05' THEN '02'
WHEN @MONTH = '06' THEN '03'
WHEN @MONTH = '07' THEN '01'
WHEN @MONTH = '08' THEN '02'
WHEN @MONTH = '09' THEN '03'
WHEN @MONTH = '10' THEN '01'
WHEN @MONTH = '11' THEN '02'
WHEN @MONTH = '12' THEN '03'
END STC_MONTH
,T9.CVS_DEPT_PAX CVS_DEPT_PAX_CNT
,T.TEAM_CD
,(SELECT D.DEPT_NM FROM TB_FR_DEPT_INFO D WHERE T.TEAM_CD = D.DEPT_CD) TEAM_NM
,T.TEAM_CNT
,T.GRP_CD
,(SELECT D.DEPT_NM FROM TB_FR_DEPT_INFO D WHERE T.GRP_CD = D.DEPT_CD) GRP_NM
,T.GRP_CNT
,T.ORG_LVL_CD
,ISNULL(T3.DH_CNT,0) TMF_CNT --上下班时间
,ISNULL(T4.DH_CNT,0) WKT_CNT --集中工作时间
,ISNULL(T5.DH_CNT,0) SECU_CNT --信息安全
,ISNULL(T2.DH_CNT,0) LDG_CNT --住宿违反
,ISNULL(T1.DH_CNT,0) TRB_CNT
,ISNULL(T8.OVT_AVG_TM,0) --加班
,ISNULL(T8.OVP_VAL,0) --加班偏重度
,ISNULL(T6.DH_CNT,0) MPR_CNT --核心人员流失
,ISNULL(T9.DH_CNT,0) CVS_CNT --面谈率
,ISNULL(T7.DH_CNT,0) CLUB_CNT --同好会加入率
,ISNULL(T11.DH_CNT,0) ACM_TRB_CNT --事件事故预防
,ISNULL(T12.DH_CNT,0) ACM_ZTH_CNT --座谈会
,ISNULL(T13.DH_CNT,0) ACM_DEPT_CNT --跨部门活动
,ISNULL(T14.PTACM_CNT,0) PTACM_CNT --普通公益活动
,ISNULL(T14.CXACM_CNT,0) CXACM_CNT --创新公益活动
,ISNULL(T8.OVT_TOP_TM,0) --10%平均加班小时数
,ISNULL(T1.H05_R,0)
,ISNULL(T1.H05_Y,0) --事故事件(Yellow)
,GETDATE()
,@USER_ID
,GETDATE()
,@USER_ID
FROM @TEAM_GRP_LIST_CNT T
LEFT JOIN @DH_TRB T1 ON ( T.GRP_CD = T1.GRP_CD)
LEFT JOIN @DH_LDG T2 ON ( T.GRP_CD = T2.GRP_CD)
LEFT JOIN @DH_TMF T3 ON ( T.GRP_CD = T3.GRP_CD)
LEFT JOIN @DH_WKT T4 ON ( T.GRP_CD = T4.GRP_CD)
LEFT JOIN @DH_SECU T5 ON ( T.GRP_CD = T5.GRP_CD)
LEFT JOIN @DH_MPR T6 ON ( T.GRP_CD = T6.GRP_CD)
LEFT JOIN @DH_CLUB T7 ON ( T.GRP_CD = T7.GRP_CD)
LEFT JOIN @DH_OVP T8 ON ( T.GRP_CD = T8.GRP_CD)
LEFT JOIN @DH_CVS T9 ON ( T.GRP_CD = T9.GRP_CD)
LEFT JOIN @DH_ACM_TRB T11 ON ( T.GRP_CD = T11.GRP_CD)
LEFT JOIN @DH_ACM_ZTH T12 ON ( T.GRP_CD = T12.GRP_CD)
LEFT JOIN @DH_ACM_DEPT T13 ON ( T.GRP_CD = T13.GRP_CD)
LEFT JOIN @DH_ACM_GZ T14 ON ( T.GRP_CD = T14.GRP_CD) */
INSERT INTO T_DM_DHSTC_DEPT(
DHSTC_CD
,STC_QUATR
,STC_MONTH
,CVS_DEPT_PAX_CNT
,TEAM_CD
,TEAM_NM
,TEAM_PAX_CNT
,DEPT_CD
,DEPT_NM
,DEPT_PAX_CNT
,ORG_LVL_CD
,H01_CNT --上下班时间
,H02_CNT --集中工作时间
,H03_CNT --信息安全
,H04_CNT --住宿违反
,H05_CNT
,H06_CNT --加班
--,H07_CNT --加班偏重度
-- ,H08_CNT --核心人员流失
,H09_CNT --面谈率
--,H10_CNT --同好会加入率
,H11_CNT --事件事故预防
,H12_CNT --座谈会
,H13_CNT --跨部门活动
,PTACM_CNT --普通公益活动
,CXACM_CNT --创新公益活动
--,H06_10 --10%平均加班小时数
,H05_R
,H05_Y --事故事件(Yellow)
,CREATE_DT
,CREATE_USR_ID
,UPDATE_DT
,UPDATE_USR_ID
)
SELECT @YEAR+@MONTH DHSTC_CD
,CASE WHEN @MONTH = '01' THEN '01'
WHEN @MONTH = '02' THEN '01'
WHEN @MONTH = '03' THEN '01'
WHEN @MONTH = '04' THEN '02'
WHEN @MONTH = '05' THEN '02'
WHEN @MONTH = '06' THEN '02'
WHEN @MONTH = '07' THEN '03'
WHEN @MONTH = '08' THEN '03'
WHEN @MONTH = '09' THEN '03'
WHEN @MONTH = '10' THEN '04'
WHEN @MONTH = '11' THEN '04'
WHEN @MONTH = '12' THEN '04'
END STC_QUATR
,CASE WHEN @MONTH = '01' THEN '01'
WHEN @MONTH = '02' THEN '02'
WHEN @MONTH = '03' THEN '03'
WHEN @MONTH = '04' THEN '01'
WHEN @MONTH = '05' THEN '02'
WHEN @MONTH = '06' THEN '03'
WHEN @MONTH = '07' THEN '01'
WHEN @MONTH = '08' THEN '02'
WHEN @MONTH = '09' THEN '03'
WHEN @MONTH = '10' THEN '01'
WHEN @MONTH = '11' THEN '02'
WHEN @MONTH = '12' THEN '03'
END STC_MONTH
,T9.CVS_DEPT_PAX CVS_DEPT_PAX_CNT
,T.TEAM_CD
,(SELECT D.DEPT_NM FROM TB_FR_DEPT_INFO D WHERE T.TEAM_CD = D.DEPT_CD) TEAM_NM
,T.TEAM_CNT
,T.GRP_CD
,(SELECT D.DEPT_NM FROM TB_FR_DEPT_INFO D WHERE T.GRP_CD = D.DEPT_CD) GRP_NM
,T.GRP_CNT
,T.ORG_LVL_CD
,ISNULL(T3.DH_CNT,0) TMF_CNT --上下班时间
,ISNULL(T4.DH_CNT,0) WKT_CNT --集中工作时间
,ISNULL(T5.DH_CNT,0) SECU_CNT --信息安全
,ISNULL(T2.DH_CNT,0) LDG_CNT --住宿违反
,ISNULL(T1.DH_CNT,0) TRB_CNT
,ISNULL(T8.OVT_AVG_TM,0) --平均加班
--,ISNULL(T8.OVP_VAL,0) --加班偏重度
--,ISNULL(T6.DH_CNT,0) MPR_CNT --核心人员流失
,ISNULL(T9.DH_CNT,0) CVS_CNT --面谈率
-- ,ISNULL(T7.DH_CNT,0) CLUB_CNT --同好会加入率
,ISNULL(T11.DH_CNT,0) ACM_TRB_CNT --事件事故预防
,ISNULL(T12.DH_CNT,0) ACM_ZTH_CNT --座谈会
,ISNULL(T13.DH_CNT,0) ACM_DEPT_CNT --跨部门活动
,ISNULL(T14.PTACM_CNT,0) PTACM_CNT --普通公益活动
,ISNULL(T14.CXACM_CNT,0) CXACM_CNT --创新公益活动
--,ISNULL(T8.OVT_TOP_TM,0) --10%平均加班小时数
,ISNULL(T1.H05_R,0)
,ISNULL(T1.H05_Y,0) --事故事件(Yellow)
,GETDATE()
,@USER_ID
,GETDATE()
,@USER_ID
FROM @TEAM_GRP_LIST_CNT T
LEFT JOIN @DH_TRB T1 ON ( T.GRP_CD = T1.GRP_CD)
LEFT JOIN @DH_LDG T2 ON ( T.GRP_CD = T2.GRP_CD)
LEFT JOIN @DH_TMF T3 ON ( T.GRP_CD = T3.GRP_CD)
LEFT JOIN @DH_WKT T4 ON ( T.GRP_CD = T4.GRP_CD)
LEFT JOIN @DH_SECU T5 ON ( T.GRP_CD = T5.GRP_CD)
--LEFT JOIN @DH_MPR T6 ON ( T.GRP_CD = T6.GRP_CD)
--LEFT JOIN @DH_CLUB T7 ON ( T.GRP_CD = T7.GRP_CD)
LEFT JOIN @DH_OVT T8 ON ( T.GRP_CD = T8.GRP_CD)
LEFT JOIN @DH_CVS T9 ON ( T.GRP_CD = T9.GRP_CD)
LEFT JOIN @DH_ACM_TRB T11 ON ( T.GRP_CD = T11.GRP_CD)
LEFT JOIN @DH_ACM_ZTH T12 ON ( T.GRP_CD = T12.GRP_CD)
LEFT JOIN @DH_ACM_DEPT T13 ON ( T.GRP_CD = T13.GRP_CD)
LEFT JOIN @DH_ACM_GZ T14 ON ( T.GRP_CD = T14.GRP_CD)
/*SELECT 'T_DM_DHSTC_DEPT',* FROM T_DM_DHSTC_DEPT
SELECT '@TEAM_GRP_LIST_CNT',* FROM @TEAM_GRP_LIST_CNT*/
------------------------save group by--------------------------------------
DELETE FROM T_DM_DHSTC_DEPT_GTHR WHERE DH_YEAR = @YEAR AND DH_MONTH = @MONTH
INSERT INTO T_DM_DHSTC_DEPT_GTHR
SELECT MAX(T2.DH_YEAR) DH_YEAR
,MAX(T2.DH_MONTH) DH_MONTH
,GROUPING(T1.TEAM_CD) GRP_TEAM_CD
,ISNULL(T1.TEAM_CD,'-') TEAM_CD
,MAX(T1.TEAM_NM) TEAM_NM
,GROUPING(T1.DEPT_CD) GRP_GRP_CD
,ISNULL(T1.DEPT_CD,'-') GRP_CD
,MAX(T1.DEPT_NM) GRP_NM
,MAX(T1.ORG_LVL_CD) ORG_LVL_CD
,SUM(T1.DEPT_PAX_CNT) DEPT_PAX_CNT
,MAX(GRP_PAX_CNT) GRP_PAX_CNT
,MAX(SUB_TOATL) SUB_TOATL
,SUM(T1.H01_CNT) H01_CNT
,SUM(T1.H02_CNT) H02_CNT
,SUM(T1.H03_CNT) H03_CNT
,SUM(T1.H04_CNT) H04_CNT
,SUM(T1.H05_Y) H05_Y_CNT
,SUM(T1.H05_R) H05_R_CNT
,AVG(T1.H06_CNT) H06_CNT --得出各部门平均加班小时数和TEAM平均加班小时数
--,AVG(T1.H06_10) H06_10 --得出各部门平均前10%加班小时数和TEAM平均前10%加班小时数
/*--,SUM(T1.H07_CNT) H07_CNT */
,SUM(T1.H08_CNT) H08_CNT
,SUM(T1.H09_CNT) H09_CNT
,SUM(T1.H10_CNT) H10_CNT
,SUM(T1.H11_CNT) H11_CNT
,SUM(T1.H12_CNT) H12_CNT
,SUM(T1.H13_CNT) H13_CNT
,SUM(T1.PTACM_CNT) PTACM_CNT
,SUM(T1.CXACM_CNT) CXACM_CNT
FROM T_DM_DHSTC_DEPT T1
JOIN T_DM_DHSTC T2 ON (T1.DHSTC_CD = T2.DHSTC_CD)
--JOIN AVG_STAT T3 ON (T1.DEPT_CD = T3.DEPT_CD)
--JOIN TB_FR_DEPT_INFO T4 ON (T1.DEPT_CD = T4.GROUP_CD AND T2.DH_YEAR = @YEAR AND T2.DH_MONTH = @MONTH)
JOIN
(SELECT T.DEPT_CD GRP_CD
,SUM(T.DEPT_PAX_CNT) OVER (PARTITION BY T.TEAM_CD) GRP_PAX_CNT
FROM T_DM_DHSTC_DEPT T
,T_DM_DHSTC T2
WHERE T.DHSTC_CD = T2.DHSTC_CD
AND T2.DH_YEAR = @YEAR AND T2.DH_MONTH = @MONTH
)DEPT_PAX_CNT ON (T1.DEPT_CD = DEPT_PAX_CNT.GRP_CD)
, (
SELECT SUM(T1.DEPT_PAX_CNT) SUB_TOATL
FROM T_DM_DHSTC_DEPT T1
,T_DM_DHSTC T2
WHERE T1.DHSTC_CD = T2.DHSTC_CD
AND T2.DH_YEAR = @YEAR AND T2.DH_MONTH = @MONTH
GROUP BY T2.DH_YEAR,T2.DH_MONTH
)T_SUB_TOATL
WHERE 1=1
AND T2.DH_YEAR = @YEAR
AND T2.DH_MONTH = @MONTH
GROUP BY T1.TEAM_CD,T1.DEPT_CD WITH ROLLUP
END
SET @END_DT = CONVERT(VARCHAR, GETDATE(), 121);
SET @ERROR_MSG = 'Success';
SELECT @RET = 'Success'
END TRY
/* === C. PROCESS ==============================================================END */
/* === D. EXCEPTION ==========================================================START */
BEGIN CATCH
PRINT ERROR_MESSAGE();
IF @@TRANCOUNT > 0
ROLLBACK
SET @END_DT = CONVERT(VARCHAR, GETDATE(), 121);
SET @ERROR_MSG = ERROR_MESSAGE();
END CATCH
/*=== D. EXCEPTION ============================================================END*/
-----------------------------------记录数据库中DDL的日志--------------------------------------------------
USE [HRPT_DEV]
GO
/****** Object: DdlTrigger [TRG_DDL_EVT] Script Date: 03/17/2017 10:22:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [TRG_DDL_EVT]
ON DATABASE
WITH EXEC AS CALLER
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DATA XML;
DECLARE @SCHEMA SYSNAME;
DECLARE @OBJECT SYSNAME;
DECLARE @EVENT_TYPE SYSNAME;
DECLARE @LOGIN_NAME SYSNAME;
SET @DATA = EVENTDATA();
SET @EVENT_TYPE = EVENTDATA().value('(EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @LOGIN_NAME = EVENTDATA().value('(EVENT_INSTANCE/LoginName)[1]', 'sysname');
SET @SCHEMA = EVENTDATA().value('(EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @OBJECT = EVENTDATA().value('(EVENT_INSTANCE/ObjectName)[1]', 'sysname');
IF @OBJECT IS NOT NULL
PRINT ' ' + @EVENT_TYPE + ' - ' + @SCHEMA + '.' + @OBJECT
ELSE
PRINT ' ' + @EVENT_TYPE + ' - ' + @SCHEMA;
IF @EVENT_TYPE IS NULL
PRINT CONVERT(NVARCHAR(MAX), @DATA);
INSERT [dbo].[DDL_EVT_LOG]
( POST_TM, DB_USR, HOST_NM, EVT, SCMA, OBJ, QURY, XML_EVT, PGM_NM )
VALUES (
GETDATE()
, @LOGIN_NAME
, HOST_NAME()
, @EVENT_TYPE
, CONVERT(SYSNAME, @SCHEMA)
, CONVERT(SYSNAME, @OBJECT)
, @DATA.value('(EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
, @DATA
, PROGRAM_NAME()
);
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
DISABLE TRIGGER [TRG_DDL_EVT] ON DATABASE
GO
ENABLE TRIGGER [TRG_DDL_EVT] ON DATABASE
GO