• The result set from your second query already contains all of the columns in your requested output. What do you want to change?

    Your first query is quite inefficient. The table attend_log is read three times. Try eliminating two of those reads. Something like this should do the trick:

    SELECT

    l.eid,

    f.[Date],

    l.TimeIn,

    l.[TimeOut],

    l.shift

    FROM (

    SELECT

    eid,

    [Date],

    TimeIn,

    [TimeOut],

    shift,

    MinDate = MIN([Date]) OVER (PARTITION BY (SELECT NULL)),

    MaxDate = MAX([Date]) OVER (PARTITION BY (SELECT NULL))

    FROM attend_log

    ) l

    CROSS JOIN dbo.CalendarTable(l.MinDate,l.MaxDate,0,0) f

    WHERE f.[Date] = l.[Date] OR l.[Date] IS NULL

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden