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