过滤某些字段重复的记录

过滤某些字段重复的记录

CREATE PROCEDURE usp_UniqueAttendRecord (@BeginTime DATETIME,@EndTime DATETIME) 
 AS

SELECT DeptCode,EmpCode,MIN(PDATE) AS 'PDATE' INTO #InTimes FROM attend
WHERE  PDate>@BeginTime AND  PDate<@EndTime AND  iomode='I'
GROUP BY DeptCode,EmpCode,iomode,DATEPART(DAY,PDate)

SELECT DeptCode,EmpCode,MAX(PDATE) AS PDATE INTO #OutTimes FROM attend
WHERE  PDate>@BeginTime AND  PDate<@EndTime AND iomode='O'
GROUP BY DeptCode,empcode,iomode,DATEPART(DAY,PDate)

SELECT A.DeptCode,A.EmpCode,A.PDate AS 'InTime',B.PDate AS 'OutTime'
FROM  #InTimes AS A
LEFT JOIN #OutTimes AS B ON A.EmpCode=B.EmpCode AND DATEPART(YEAR,A.PDate)=DATEPART(YEAR,B.PDate)  AND DATEPART(MONTH,A.PDate)=DATEPART(MONTH,B.PDate) AND DATEPART(DAY,A.PDate)=DATEPART(DAY,B.PDate)
UNION
SELECT A.DeptCode,A.EmpCode,B.PDate AS 'InTime',A.PDate AS 'OutTime'
FROM  #OutTimes AS A
LEFT JOIN #InTimes AS B ON A.EmpCode=B.EmpCode AND DATEPART(YEAR,A.PDate)=DATEPART(YEAR,B.PDate)  AND DATEPART(MONTH,A.PDate)=DATEPART(MONTH,B.PDate) AND DATEPART(DAY,A.PDate)=DATEPART(DAY,B.PDate)
ORDER BY A.DeptCode,A.EmpCode

DROP TABLE #InTimes
DROP TABLE #OutTimes

转载于:https://www.cnblogs.com/kenly33/archive/2007/06/03/769355.html