You can calculate the absent details by the code below, then you can incorporate your code

CREATE TABLE Attend_log

(

Eid INT,

EName VARCHAR(50),

LogDate DATETIME,

SpendTime Datetime,

Remarks VARCHAR(50)

)

INSERT INTO Attend_log (Eid,EName,LogDate,SpendTime,Remarks)

SELECT '17074','ABCEmployee','2013-01-01 00:00:00.000','1900-01-01 09:25:00.000','null' UNION

SELECT '17074','ABCEmployee','2013-01-02 00:00:00.000','1900-01-01 08:50:00.000','null' UNION

SELECT '17074','ABCEmployee','2013-01-03 00:00:00.000','1900-01-01 09:15:00.000','late' UNION

SELECT '17074','ABCEmployee','2013-01-04 00:00:00.000','1900-01-01 04:45:00.000','halfday' UNION

SELECT '17074','ABCEmployee','2013-01-05 00:00:00.000','1900-01-01 09:25:00.000','late' UNION

SELECT '17074','ABCEmployee','2013-01-08 00:00:00.000','1900-01-01 09:14:00.000','null' UNION

SELECT '17074','ABCEmployee','2013-01-09 00:00:00.000','1900-01-01 09:55:00.000','late'

SELECT * FROM Attend_log

;WITH AllDateCTE

AS

(

SELECT Eid,EName,LogDate,SpendTime,Remarks FROM Attend_log

UNION ALL

SELECT Eid,EName,DATEADD(DD,-1,LogDate) AS LogDate,NULL AS SpendTime,CONVERT(VARCHAR(50),'Absent' )as Remarks

FROM AllDateCTE

WHERE DATEADD(DD,-1,LogDate) NOT IN ( SELECT LogDate FROM Attend_log)

AND DATEADD(DD,-1,LogDate) BETWEEN '2013-01-01 00:00:00.000' and '2013-01-09 00:00:00.000'

AND DATENAME(weekday,DATEADD(DD,-1,LogDate)) <> 'SUNDAY'

)

SELECT * FROM AllDateCTE

ORDER BY LogDate