sir this is my whole data

eid-------date---------------timein------------------timeout--------------spendtime-----------------excessshort--------remarks

17074--2013-01-01-------08:39:00.000-------18:04:00.000---------------09:25:00----------------00:25:00-------Late

17074--2013-01-02-------08:42:00.000-------09:56:00.000---------------08:50:00---------------10:00.000--------null

17074--2013-01-02-------10:09:00.000-------12:23:00.000---------------null---------------------null--------------null

17074--2013-01-02-------12:32:00.000--------3:14:00.000---------------null---------------------null--------------null

17074--2013-01-02-------13:30:00.000-------18:10:00.000---------------null---------------------null--------------null

17074--2013-01-03-------08:40:00.000-------17:55:00.000---------------09:15:00.000----------00:15:00.000------null

17074--2013-01-04-------08:32:00.000-------10:53:00.000---------------08:45:00.000----------00:15:00.000------null

17074--2013-01-04-------11:09:00.000-------12:57:00.000---------------null----------------------null-------------null

17074--2013-01-04-------13:06:00.000-------13:18:00.000---------------null----------------------null-------------null

17074--2013-01-04-------13:45:00.000-------18:09:00.000---------------null----------------------null-------------null

17074--2013-01-05-------08:47:00.000-------18:01:00.000------------ ---09:14:00.000------------00:14:00.000---halfday

17074--2013-01-08-------08:36:00.000-------10:25:00.000------------ ---09:55:00.000------------00:55:00.000-----null

17074--2013-01-08-------10:49:00.000-------13:02:00.000---------------null-----------------------null-------------null

17074--2013-01-08-------13:35:00.000-------15:24:00.000---------------null-----------------------null-------------null

17074--2013-01-08-------15:42:00.000-------19:46:00.000---------------null-----------------------null-----------------null

this is your query

;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,

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-08 00:00:00.000'

)

SELECT

EID,

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,

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

group by g.eid

)t

and this query gives me this result

eid--------late--halfday---absent--offday----spendtime

17074------1------1-------0-------0--------204:35