;WITH AllDateCTE

AS

(

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

UNION ALL

SELECT Eid,EName,DATEADD(DD,-1,Date) AS Date,NULL AS SpendTime,

CASE

WHEN DATENAME(weekday,DATEADD(DD,-1,Date)) ='SUNDAY'

THEN CONVERT(VARCHAR(50),'OffDay' )

ELSE CONVERT(VARCHAR(50),'Absent' )

END 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'

)

SELECT

EID,

EName,

Late,

[HALFDAY],

Absent,

OffDay,

CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS SPENDTIME

FROM

(

SELECT

g.EID,

EName ,

sum(case when g.Remarks = 'Late' then 1 else 0 end) as LATE,

sum(case when g.Remarks = 'HALF DAY' then 1 else 0 end) as [HALFDAY],

sum(case when g.Remarks = 'Absent' then 1 else 0 end) as Absent,

sum(case when g.Remarks = 'OffDay' then 1 else 0 end) as OffDay,

DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedate

FROM AllDateCTE g

where g.eid=17074 and date >'20130101' and date <'20130131'

group by g.eid , EName

)t