SQLSERVER存储过程及创建临时表

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