22,210
社区成员
发帖
与我相关
我的任务
分享
USE AdventureWorks2012;
GO
DECLARE @Student TABLE
(
S_Number INT
, S_Name VARCHAR(10)
, Mobile VARCHAR(20)
, Class VARCHAR(10)
)
;
DECLARE @Check TABLE
(
S_Name VARCHAR(10)
, CheckDate DATETIME
, CheckTime DATETIME
)
;
SELECT
S.[S_Number] '学号'
, S.[S_Name] '姓名'
, S.[Class] '班级'
, S.[Mobile] '联系方式'
, CASE WHEN DATEDIFF(MM,ISNULL(C2.[CheckTime],GETDATE()),ISNULL(C1.[CheckTime],GETDATE())) = 45 THEN 'Normal'
WHEN DATEDIFF(MM,ISNULL(C2.[CheckTime],GETDATE()),ISNULL(C1.[CheckTime],GETDATE())) <45 THEN 'Late/Early'
ELSE 'Exception' END AS '考勤情况'
, C1.[CheckDate] '考勤日期'
FROM @Student S
LEFT JOIN (SELECT S_Name, CheckDate,CheckTime,
ROW_NUMBER() OVER(PARTITION BY S_Name ORDER BY CheckDate ASC ,CheckTime ASC ) CN FROM @Check) C1
ON C1.S_Name = S.S_Name AND C1.CN%2 =1
LEFT JOIN (SELECT S_Name, CheckDate,CheckTime,
ROW_NUMBER() OVER(PARTITION BY S_Name ORDER BY CheckDate ASC ,CheckTime ASC ) CN FROM @Check) C2
ON C2.S_Name = S.S_Name AND C2.CN%2 =0
;