过滤某些字段重复的记录
过滤某些字段重复的记录
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