联合查询的新问题--和<哥眼神纯洁不>老大进。

[XinJW]

SELECT WorkID, MIN(atd_datetime) atd_datetime, '上班' type, CASE WHEN datepart(hh, 
      MIN(atd_datetime)) > 8 THEN '迟到' ELSE '正常' END AS remark, 
      Employee_Name
FROM (SELECT workid, CONVERT(datetime, atd_datetime) atd_datetime
        FROM Attendance
        WHERE Year(Atd_Datetime) = '2013' AND Month(Atd_Datetime) = '6') A JOIN
      Employee aa ON workid = aa.sn
GROUP BY WorkID, CONVERT(varchar, atd_datetime, 23), Employee_Name
UNION ALL
SELECT WorkID, MAX(atd_datetime) atd_datetime, '下班' type, CASE WHEN (datepart(hh, 
      MAX(atd_datetime)) > 17 AND datepart(mi, MAX(atd_datetime)) > 30) 
      THEN '正常' ELSE '早退' END AS remark, Employee_Name
FROM (SELECT workid, CONVERT(datetime, atd_datetime) atd_datetime
        FROM Attendance
        WHERE Year(Atd_Datetime) = '2013' AND Month(Atd_Datetime) = '6') A JOIN
      Employee aa ON workid = aa.sn
GROUP BY WorkID, CONVERT(varchar, atd_datetime, 23), Employee_Name
ORDER BY WorkID, atd_datetime DESC

得出的结果是正确的，但是如果一天只打一次卡或者还没到下班时间，没有下班打卡记录的数据为有问题。如下图。

这是显示上班和下班的时间一样。应该是一天打一次卡就只显示一次的。没有上班时间或者没有下班时间。

应该是这样的结果

1002 2013/6/1 18:03:00 下班 早退 延燕
1002 2013/6/1 9:52:00 上班 迟到 延燕
1005 2013/6/11 7:51:00 上班 正常 柳方杨
1006 2013/6/11 7:52:00 下班 早退 陈昭晴

[哥眼神纯洁不]

如果你一天就下午打了一次卡，算上班的还是下班的？

[XinJW]

算下班，中午12点为分割

[哥眼神纯洁不]

引用 3 楼 XinJW 的回复:

算下班，中午12点为分割

SELECT WorkID, MIN(atd_datetime) atd_datetime, '上班' type, CASE WHEN datepart(hh, 
      MIN(atd_datetime)) > 8 THEN '迟到' ELSE '正常' END AS remark, 
      Employee_Name
FROM (SELECT workid, CONVERT(datetime, atd_datetime) atd_datetime
        FROM Attendance
        WHERE Year(Atd_Datetime) = '2013' AND Month(Atd_Datetime) = '6') A JOIN
      Employee aa ON workid = aa.sn
GROUP BY WorkID, CONVERT(varchar, atd_datetime, 23), Employee_Name
having datepart(hh,MIN(atd_datetime))<12  --判断12点前的为上班时间，好像是这么写，如果不对，你再查查datepart函数
UNION ALL
SELECT WorkID, MAX(atd_datetime) atd_datetime, '下班' type, CASE WHEN (datepart(hh, 
      MAX(atd_datetime)) > 17 AND datepart(mi, MAX(atd_datetime)) > 30) 
      THEN '正常' ELSE '早退' END AS remark, Employee_Name
FROM (SELECT workid, CONVERT(datetime, atd_datetime) atd_datetime
        FROM Attendance
        WHERE Year(Atd_Datetime) = '2013' AND Month(Atd_Datetime) = '6') A JOIN
      Employee aa ON workid = aa.sn
GROUP BY WorkID, CONVERT(varchar, atd_datetime, 23), Employee_Name
having datepart(hh,MAX(atd_datetime))>12  --判断12点后的为下班时间
ORDER BY WorkID, atd_datetime DESC

[学习中-------]

本身这个逻辑不是很严谨的说。

SELECT WorkID, MIN(atd_datetime) atd_datetime, '上班' type, CASE WHEN datepart(hh, 
      MIN(atd_datetime)) > 8 THEN '迟到' ELSE '正常' END AS remark, 
      Employee_Name
FROM (SELECT workid, CONVERT(datetime, atd_datetime) atd_datetime
        FROM Attendance
        WHERE Year(Atd_Datetime) = '2013' AND Month(Atd_Datetime) = '6') A JOIN
      Employee aa ON workid = aa.sn
GROUP BY WorkID, CONVERT(varchar, atd_datetime, 23), Employee_Name
UNION ALL
SELECT WorkID, MAX(atd_datetime) atd_datetime, '下班' type, CASE WHEN CONVERT(varchar(12) ,MAX(atd_datetime) , 108 )>'17:30:00'
      THEN '正常' ELSE '早退' END AS remark, Employee_Name
FROM (SELECT workid, CONVERT(datetime, atd_datetime) atd_datetime
        FROM Attendance
        WHERE Year(Atd_Datetime) = '2013' AND Month(Atd_Datetime) = '6') A JOIN
      Employee aa ON workid = aa.sn where MAX(atd_datetime)<>min(atd_datetime)
GROUP BY WorkID, CONVERT(varchar, atd_datetime, 23), Employee_Name having MAX(atd_datetime)<>MIN(atd_datetime) atd_datetime
ORDER BY WorkID, atd_datetime DESC

[sql_sf]

版主出来下 我坐等学习就被删除了?

[XinJW]

哥眼神纯洁不 的结果正确，myselfff 的结果还是有问题。呵呵。。。。

[XinJW]

突然再想借着这个题目问下大神，这样是得出正常的结果。那我相得到反结果怎么得出呢。就是一天内没打卡或一天内只打过一次卡，打过卡是迟到或早退的。这样的结果怎么得出呢。