Why would "2013-05-04" be an off day AND 'G'?

Why does your query have SpendTime, excesshorttime when the example of what you want doesn't have it?

You also give no info about the data -_ I thought eid = employee id but then it doesn't make sense why you look for the minimum timein (since an employee would only timein once per day?). You also have no "shift" column so I assumed you want a case. The solution below may work although it won't be the best.

with t1 as (select [date], min([Timein]) as First_Record,

sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes

from Atend where eid = 26446

group by [date]),

t2 as (select

t.[date],t.eid,t.[Timein] as timein,t.[Timeout] as timeout,

CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,

case when (540 - Time_Minutes) > 0 Then '- ' else '+ ' end

+CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime

FROM Atend t inner join t1 t1 on t.[date] = t1.[date] and t.[Timein] = t1.First_Record

where eid = 26446)

select c.date, isnull(t2.eid,26446) eid,

case when t2.timein is null then 'O'

else 'G' end as Shift, t2.timein, t2.timeout

from calendar c left outer join t2 t2 on c.date= t2.date

order by c.[date], t2.[timein]