下面的代码只是个示例,大概就是你的意思. 如果要通用,要做一些修改,原理就是这样.select d.sn WorkID,d.atd_date+' ' +case when d.type='上班' then convert(varchar(8),ISNULL(e.atd_datetime,'08:00:00'),108) when d.type='下班' then convert(varchar(8),ISNULL(e.atd_datetime,'17:30:00'),108) else '' end Atd_Datetime, d.type,isnull(e.re,'未刷卡') re,d.Employee_Name from ( select sn,'2013-06-'+right('0'+CAST(number as varchar(2)),2) atd_date,type,Employee_Name from ( select number from master..spt_values WHERE type ='P'and number between 1 and 30 ) as a, (select N'上班' [type] union all select N'下班') as b,employee as c) as d left join (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 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 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 re, 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 ) as e on d.sn=e.WorkID and d.type=e.type and day(atd_date)=day(e.Atd_Datetime)
如果要通用,要做一些修改,原理就是这样.select d.sn WorkID,d.atd_date+' '
+case when d.type='上班' then convert(varchar(8),ISNULL(e.atd_datetime,'08:00:00'),108)
when d.type='下班' then convert(varchar(8),ISNULL(e.atd_datetime,'17:30:00'),108) else '' end Atd_Datetime,
d.type,isnull(e.re,'未刷卡') re,d.Employee_Name
from ( select sn,'2013-06-'+right('0'+CAST(number as varchar(2)),2) atd_date,type,Employee_Name
from ( select
number from master..spt_values WHERE type ='P'and number between 1 and 30 ) as a,
(select N'上班' [type] union all select N'下班') as b,employee as c) as d left join
(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 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
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 re, 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 ) as e
on d.sn=e.WorkID and d.type=e.type and day(atd_date)=day(e.Atd_Datetime)