select WorkID,MIN(atd_datetime)atd_datetime,'上班'type from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A
group by WorkID,CONVERT(varchar,atd_datetime,23)
union all
select WorkID,max(atd_datetime)atd_datetime,'下班'type from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A
group by WorkID,CONVERT(varchar,atd_datetime,23) order by WorkID,atd_datetime DESC得出如下结果atd_datetime Type workid
2012-03-10 08:38:00.000 上班 1111
2012-03-09 18:21:00.000 下班 1111
2012-03-09 07:56:00.000 上班 2222
2012-03-08 22:08:00.000 下班 2222
2012-03-08 07:55:00.000 上班 1111
2012-03-06 15:04:00.000 下班 1111 还有表Employee
Employee_Name SN
王静 1111
李明柯 2222如何得出下面结果
atd_datetime Type workid Re Name
2012-03-10 08:38:00.000 上班 1111 迟到 王静
2012-03-09 18:21:00.000 下班 1111 正常 王静
2012-03-09 07:56:00.000 上班 2222 正常 李明柯
2012-03-08 22:08:00.000 下班 2222 正常 李明柯
2012-03-08 07:55:00.000 上班 1111 正常 王静
2012-03-06 15:04:00.000 下班 1111 早退 王静
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A
group by WorkID,CONVERT(varchar,atd_datetime,23)
union all
select WorkID,max(atd_datetime)atd_datetime,'下班'type from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A
group by WorkID,CONVERT(varchar,atd_datetime,23) order by WorkID,atd_datetime DESC得出如下结果atd_datetime Type workid
2012-03-10 08:38:00.000 上班 1111
2012-03-09 18:21:00.000 下班 1111
2012-03-09 07:56:00.000 上班 2222
2012-03-08 22:08:00.000 下班 2222
2012-03-08 07:55:00.000 上班 1111
2012-03-06 15:04:00.000 下班 1111 还有表Employee
Employee_Name SN
王静 1111
李明柯 2222如何得出下面结果
atd_datetime Type workid Re Name
2012-03-10 08:38:00.000 上班 1111 迟到 王静
2012-03-09 18:21:00.000 下班 1111 正常 王静
2012-03-09 07:56:00.000 上班 2222 正常 李明柯
2012-03-08 22:08:00.000 下班 2222 正常 李明柯
2012-03-08 07:55:00.000 上班 1111 正常 王静
2012-03-06 15:04:00.000 下班 1111 早退 王静
select WorkID,MIN(atd_datetime)atd_datetime,'上班'type,case when datepart(hh,MIN(atd_datetime))> 8 then "迟到" else “正常" as re,Employee_Name from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A join Employee aa on workid=aa.sn
group by WorkID,CONVERT(varchar,atd_datetime,23),re,Employee_Name
union all
select WorkID,max(atd_datetime)atd_datetime,'下班'type,type,case when datepart(hh,Max(atd_datetime))>16 then "正常" else "早退" as re ,Employee_Name from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A join Employee aa on workid=aa.sn
group by WorkID,CONVERT(varchar,atd_datetime,23),re,Employee_Name order by WorkID,atd_datetime DESC
在关键字 'as' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 3
第 3 行: 'A' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 6
在关键字 'as' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 8
第 8 行: 'A' 附近有语法错误。
select WorkID,MIN(atd_datetime)atd_datetime,'上班'type,case when datepart(hh,MIN(atd_datetime))> 8 then '迟到' else '正常' end as re ,Employee_Name from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A join Employee aa on workid=aa.sn
group by WorkID,CONVERT(varchar,atd_datetime,23),re,Employee_Name
union all
select WorkID,max(atd_datetime)atd_datetime,'下班'type,type,case when (datepart(hh,Max(atd_datetime))>17 and datepart(mi,Max(atd_datetime))>30) then '正常' else '早退' end as re ,Employee_Name from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A join Employee aa on workid=aa.sn
group by WorkID,CONVERT(varchar,atd_datetime,23),re,Employee_Name order by WorkID,at
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A join Employee aa on workid=aa.sn
group by WorkID,CONVERT(varchar,atd_datetime,23),re,Employee_Name
union all
select WorkID,max(atd_datetime)atd_datetime,'下班'type,type,case when (datepart(hh,Max(atd_datetime))>17 and datepart(mi,Max(atd_datetime))>30) then '正常' else '早退' end as re ,Employee_Name from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A join Employee aa on workid=aa.sn
group by WorkID,CONVERT(varchar,atd_datetime,23),re,Employee_Name order by WorkID,atd_datetime DESC
服务器: 消息 207,级别 16,状态 3,行 1
列名 're' 无效。
服务器: 消息 207,级别 16,状态 1,行 1
列名 're' 无效。
服务器: 消息 207,级别 16,状态 1,行 1
列名 'at' 无效。
SELECT WorkID, MIN(atd_datetime) atd_datetime, '上班' type, CASE WHEN datepart(hh,
MIN(atd_datetime)) > 8 THEN '迟到' ELSE '正常' END AS re,
Employee_Name
FROM (SELECT workid, CONVERT(datetime, atd_datetime) atd_datetime
FROM Attendance ) 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 THEN '迟到' ELSE '正常' END AS re, Employee_Name
FROM (SELECT workid, CONVERT(datetime, atd_datetime) atd_datetime
FROM Attendance ) A JOIN
Employee aa ON workid = aa.sn
GROUP BY WorkID, CONVERT(varchar, atd_datetime, 23), Employee_Name
ORDER BY WorkID, atd_datetime DESC这样居然可以了。