优化一个考勤的SQL

最近在做一个生成考勤报表，对写的SQL语句速率感觉不满意。
表结构如下：

CREATE TABLE [Attendance]

(

[usercode] [dbo].[varchar](30) NOT NULL,

[logdate] [datetime] NOT NULL,

[mac] [dbo].[varchar](30),

[tag1] [char](1),

[tag2] [char](1),

[tag3] [char](1)

)

GO

CREATE TABLE [AttendanceTime]

(

[id] [int] NOT NULL IDENTITY(1, 1),

[ontime] [dbo].[varchar](30) NULL,

[offtime] [dbo].[varchar](30) NULL,

[iscurrent] [bit] NULL

)GO

AttendanceTime的数据：
id ontime offtime iscurrent
1 DOY 08:00:00 DOY 17:30:00 1
意思为上班时间08：00,下班时间17：30

Attendance是从文本文件BULK INSERT过来的。
文本文件原始数据为：
......
01992 20100825173022 05 1 0 2
01888 20100825173024 05 1 0 2
01888 20100825173024 05 1 0 2
20 05 1 0 2
02245 20100825173027 05 1 0 2
02285 20100825173028 05 1 0 2
......
文本文件修正后数据为：
......
01992 2010-08-25 17:30:22 05 1 0 2
01888 2010-08-25 17:30:24 05 1 0 2
01888 2010-08-25 17:30:24 05 1 0 2
02245 2010-08-25 17:30:27 05 1 0 2
02285 2010-08-25 17:30:28 05 1 0 2
......

开始插入语句：
BULK INSERT HrcmsDemo.dbo.Attendance
FROM 'C:\Att20100903.After.txt'
WITH (FIELDTERMINATOR ='\t')
GO
你可以从这里下载到此文件
我写的SQL：

WITH TotalAttendance AS(

	SELECT usercode ,ontime ,CASE WHEN ontime=offtime THEN NULL ELSE offtime END offtime,COUNT(1) AS 'Count'

	FROM (

		SELECT a.usercode ,

			(SELECT MIN(a1.logdate) FROM Attendance a1 WHERE a1.usercode=a.usercode 

				AND Year(a1.logdate)=Year(a.logdate) AND Month(a1.logdate)=Month(a.logdate) AND DAY(a1.logdate)=DAY(a.logdate)

			) ontime ,

			(SELECT MAX(a2.logdate) FROM Attendance a2 WHERE a2.usercode=a.usercode 

				AND Year(a2.logdate)=Year(a.logdate) AND Month(a2.logdate)=Month(a.logdate) AND DAY(a2.logdate)=DAY(a.logdate)

			) offtime

		FROM (SELECT usercode ,logdate FROM Attendance WHERE YEAR(logdate)='2010' and MONTH(logdate)='9' GROUP BY usercode ,logdate) AS a

	) tt

	GROUP BY usercode, ontime ,offtime

)

INSERT INTO ttt(usercode ,workday ,timeon ,timeoff ,ontime ,offtime)

SELECT usercode,

	CONVERT(VARCHAR(10),h.ontime,120) workday,

	CAST(REPLACE(a.ontime,'DOY',CONVERT(VARCHAR(10),h.ontime,120)) AS DATETIME) timeon ,

	CAST(REPLACE(a.offtime,'DOY',CONVERT(VARCHAR(10),h.ontime,120)) AS DATETIME) timeoff ,h.ontime,h.offtime 

FROM TotalAttendance h LEFT JOIN AttendanceTime a ON 1=1;



DECLARE @ont CHAR(5),@offt CHAR(5);

SET @ont='08:00'; SET @offt='17:30';

SELECT ''''+UserName UserName ,

''''+t.usercode usercode ,''''+workday workday,

''''+timeon timeon,''''+ontime ontime,

''''+timeoff timeoff,''''+offtime offtime ,

early ,late ,

CASE WHEN ontime IS NULL OR offtime IS NULL THEN '1' WHEN COALESCE(early,late,0) = 0 THEN '0' ELSE '1' END 'state'

FROM ( 

	SELECT usercode ,workday ,@ont timeon ,dbo.fn_ParseTime(ontime) ontime ,@offt timeoff ,dbo.fn_ParseTime(offtime) offtime ,early ,late FROM ttt 

) t 

JOIN UserInfo AS ui ON t.usercode = ui.UserCode

ORDER BY t.usercode ,t.workday ;