補全未出勤日期

-- 建立测试环境
set  nocount  on
create   table  test(Name  varchar ( 20 ),Date  varchar ( 20 ),Time1  varchar ( 20 ),Time2  varchar ( 20 ))
insert   into  test  select   ' xxxx ' , ' 2008-2-1 ' , ' 08:00 ' , ' 17:00 '
insert   into  test  select   ' xxxx ' , ' 2008-2-9 ' , ' 08:00 ' , Null
insert   into  test  select   ' xxxx ' , ' 2008-2-28 ' , ' 08:00 ' , ' 17:00 '
go
-- 测试

select    case   when   isdate (date) > 0   then  name  else   ' - '   end  EmpName,
         case   when   isdate (date) > 0   then  date  else   ' - '   end  IODate,
        Time1,Time2
     from (
select  ( select   max (name)  from  test)name,
( select   cast ( year ( max ( cast (date  as   datetime )))  as   varchar ( 4 )) + ' - ' +
         cast ( month ( max ( cast (date  as   datetime )))  as   varchar ( 2 )) + ' - ' +
         cast (b.dd  as   varchar ( 2 ))  from  test)date,
         isnull (time1, ' - ' ) time1, isnull (time2, ' - ' ) time2
 
  from  test a
right   join ( select   1   as  dd  union   select   2   union   select   3   union   select   4   union   select   5   union
     select   6   union   select   7   union   select   8   union   select   9   union   select   10   union
     select   11   union   select   12   union   select   13   union   select   14   union   select   15   union
     select   16   union   select   17   union   select   18   union   select   19   union   select   20   union
     select   21   union   select   22   union   select   23   union   select   24   union   select   25   union
     select   26   union   select   27   union   select   28   union   select   29   union   select   30   union
     select   31 ) b
on   day ( cast (date  as   datetime )) = b.dd)a


-- 删除测试环境
drop   table  test
  set  nocount  off
/**/ /*
EmpName              IODate     Time1                Time2                
-------------------- ---------- -------------------- -------------------- 
xxxx                 2008-2-1   08:00                17:00
xxxx                 2008-2-2   -                    -
xxxx                 2008-2-3   -                    -
xxxx                 2008-2-4   -                    -
xxxx                 2008-2-5   -                    -
xxxx                 2008-2-6   -                    -
xxxx                 2008-2-7   -                    -
xxxx                 2008-2-8   -                    -
xxxx                 2008-2-9   08:00                -
xxxx                 2008-2-10  -                    -
xxxx                 2008-2-11  -                    -
xxxx                 2008-2-12  -                    -
xxxx                 2008-2-13  -                    -
xxxx                 2008-2-14  -                    -
xxxx                 2008-2-15  -                    -
xxxx                 2008-2-16  -                    -
xxxx                 2008-2-17  -                    -
xxxx                 2008-2-18  -                    -
xxxx                 2008-2-19  -                    -
xxxx                 2008-2-20  -                    -
xxxx                 2008-2-21  -                    -
xxxx                 2008-2-22  -                    -
xxxx                 2008-2-23  -                    -
xxxx                 2008-2-24  -                    -
xxxx                 2008-2-25  -                    -
xxxx                 2008-2-26  -                    -
xxxx                 2008-2-27  -                    -
xxxx                 2008-2-28  08:00                17:00
xxxx                 2008-2-29  -                    -
-                    -          -                    -
-                    -          -                    -
*/

水晶報表做法參考： http://www.cnblogs.com/babyt/archive/2008/05/11/1192770.html