set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:Garry
-- Create date: 2012-01-16
-- Description: 查詢人員離歸崗報表
-- =============================================
-- EXEC [spPersonOnAndOffPost] '2012-1-15 10:00:00','2012-1-17 23:00:00'
CREATE PROC [dbo].[spPersonOnAndOffPost]
@StartDate datetime, --工號
@EndDate datetime --班別
AS
-- *************** STEP 1 ：建立以日期為欄位的 TABLE ***************
DECLARE @colDATE nvarchar(20)
DECLARE @sSQL nvarchar(1000)
--算出時間差、當天次數
SELECT A.*,B.sDATE,B.OFFCOUNT INTO #EMPDATE FROM (
SELECT ID,[USER_ID],[USER_NAME],SHIFT,department_id,department_name,OFFDATE,ONDATE,[TYPE],
DATEDIFF(minute,OFFDATE, ISNULL(ONDATE,CAST((CONVERT(VARCHAR (10), OFFDATE, 111)+' 23:59:59') AS DATETIME))) AS TIMETOTAL
FROM dtblPersonPostManage ) A LEFT JOIN
( SELECT [USER_ID],SHIFT,department_id,department_name,CONVERT(VARCHAR (10), OFFDATE, 111) AS sDATE,COUNT(1) AS OFFCOUNT
FROM dtblPersonPostManage
GROUP BY [USER_ID],SHIFT,department_id,department_name ,CONVERT(VARCHAR (10), OFFDATE, 111)
) B
ON A.[USER_ID] = B.[USER_ID] AND CONVERT(VARCHAR (10), A.OFFDATE, 111) = B.sDATE
WHERE A.[type] ='ON' AND OFFDATE BETWEEN @StartDate AND @EndDate
ORDER BY [USER_ID],TIMETOTAL DESC
-- SELECT * FROM dtblPersonPostManage
-- SUM TIME BY 工號、日期
SELECT [USER_ID],sDATE,SUM(TIMETOTAL) AS DAYTIMETOTAL INTO #TIMETOTALDATE FROM #EMPDATE GROUP BY [USER_ID],sDATE
--找出離崗最長時間的資料
SELECT * INTO #LongestData FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY [USER_ID],SDATE
ORDER BY [USER_ID],SDATE,TIMETOTAL DESC) AS Rowid,
ID,[USER_ID],[USER_NAME],SHIFT,department_id,department_name,OFFDATE AS OFFDATELONG,ONDATE AS ONDATELONG
FROM #EMPDATE
) EMPDATE WHERE Rowid =1
SELECT EMP.[USER_ID],EMP.[USER_NAME],EMP.SHIFT,EMP.department_id,EMP.department_name,EMP.OFFDATELONG,EMP.ONDATELONG,EMP.OFFCOUNT,EMP.sDATE,
EMP.TIMETOTAL,TOTALDATA.DAYTIMETOTAL FROM (
SELECT A.[USER_ID],A.[USER_NAME],A.SHIFT,A.department_id,A.department_name,A.OFFDATELONG,A.ONDATELONG,B.OFFCOUNT,sDATE,B.TIMETOTAL
FROM #LongestData A LEFT JOIN #EMPDATE B ON A.ID = B.ID ) EMP JOIN #TIMETOTALDATE TOTALDATA
ON EMP.[USER_ID] = TOTALDATA.[USER_ID] AND EMP.SDATE = TOTALDATA.SDATE
DROP TABLE #EMPDATE
DROP TABLE #LongestData
DROP TABLE #TIMETOTALDATE
Return
