SIR ITS GIVING ME THE SAME RESULT I DID SOME CHANGES

;WITH AllDateCTE

AS

(

SELECT Eid,Date,SpendTime,Remarks FROM ATTEND_LOG WHERE EID=17074

UNION ALL

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

FROM AllDateCTE

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

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

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

)

SELECT * FROM AllDateCTE

ORDER BY Date