SQL Server select missing Dates in result set -

i have 1 table containing employee daily attendance punchtime in space separated form.

employeepunch

empid    empname    date          time  1        abc        2014-12-01    10:00 18:00 1        abc        2014-12-02    09:50 17:50 1        abc        2014-12-04    09:30 17:30 1        abc        2014-12-07    10:00 18:00 1        abc        2014-12-08    09:50 17:50 1        abc        2014-12-10    09:30 17:30 

now want write query following output

empid    empname    date          time  1        abc        2014-12-01    10:00 18:00 1        abc        2014-12-02    09:50 17:50 1        abc        2014-12-03    absent 1        abc        2014-12-04    09:30 17:30 1        abc        2014-12-05    absent 1        abc        2014-12-06    absent 1        abc        2014-12-07    10:00 18:00 1        abc        2014-12-08    09:50 17:50 1        abc        2014-12-09    absent 1        abc        2014-12-10    09:30 17:30 

first define cte generate missing records:

with dates ( select distinct empid, empname, '2014-12-01' date, 'absent' time employeepunch union select empid, empname, dateadd(day, 1, date), 'absent' dates date < dateadd(day, -1, dateadd(month, 1, '2014-12-01'))) select * dates 

in next step replace last line with:

select * employeepunch union select d.* dates d left join employeepunch e on e.empid = d.empid , e.date = d.date e.time null 

the missing rows outerjoined ones.