按月份统计人员每天是否登录过系统，查询出来格式如下

有两个表：人员表T1，登录信息表T2
T1有两个栏位：EMPID（人员ID），EMPNAME（人员姓名）
T2有两个栏位：EMPID（人员ID），LOGTIME（登录时间）
按月份统计人员每天是否登录过系统，查询出来格式如下：
姓名 1,2,3,4……一直到31号（选择的月份有几天就是多少天），最后再加这个人当月的出勤，只要当天登录过就是出勤了！
详细格式请看下面的图片：
http://60.216.13.200/123.jpg
谢谢啊！！！

 

 

--
----------------------------------- 


--
  Author : Luoyoumou 


--
  Comment: 三月红梨 


--
  Date  : 2009-11-04 06:37:17 


--
-------------------------------------



create
 
table
 t1(empid 
varchar
(
10
), empname 
varchar
(
20
))


insert
 
into
 t1(empid, empname)


select



'
0001
'
,
'
张三
'
 
union
 
all
 
select



'
0002
'
,
'
李四
'
 
union
 
all
 
select



'
0003
'
,
'
王五
'
 
union
 
all
 
select



'
0004
'
,
'
赵六
'
;




create
 
table
 t2(empid 
varchar
(
10
), logtime 
datetime
)




insert
 
into
 t2(empid, logtime)


select



'
0001
'
,
'
2009-09-05
'
 
union
 
all
 
select



'
0001
'
,
'
2009-01-11
'
 
union
 
all
 
select



'
0001
'
,
'
2009-01-14
'
 
union
 
all
 
select



'
0001
'
,
'
2009-01-23
'
 
union
 
all
 
select



'
0001
'
,
'
2009-01-01
'
 
union
 
all
 
select



'
0001
'
,
'
2009-02-05
'
 
union
 
all
 
select



'
0001
'
,
'
2009-06-04
'
 
union
 
all
 
select



'
0001
'
,
'
2009-07-11
'
 
union
 
all
 
select



'
0001
'
,
'
2009-07-11
'
 
union
 
all
 
select



'
0001
'
,
'
2009-05-11
'
 
union
 
all
 
select



'
0001
'
,
'
2009-03-11
'
 
union
 
all
 
select



'
0001
'
,
'
2009-03-11
'
 
union
 
all
 
select



'
0001
'
,
'
2009-03-11
'
 
union
 
all
 
select



'
0001
'
,
'
2009-02-02
'
 
union
 
all
 
select



'
0001
'
,
'
2009-02-03
'
 
union
 
all
 
select



'
0001
'
,
'
2009-02-04
'
 
union
 
all
 
select



'
0001
'
,
'
2009-02-06
'
 
union
 
all
 
select



'
0001
'
,
'
2009-03-11
'
 
union
 
all
 
select



'
0001
'
,
'
2009-08-02
'
 
union
 
all
 
select



'
0001
'
,
'
2009-08-03
'
 
union
 
all
 
select



'
0001
'
,
'
2009-08-04
'
 
union
 
all
 
select



'
0001
'
,
'
2009-09-16
'
 
union
 
all
 
select



'
0001
'
,
'
2009-09-02
'
 
union
 
all
 
select



'
0001
'
,
'
2009-09-03
'
 
union
 
all
 
select



'
0001
'
,
'
2009-09-04
'
 
union
 
all
 
select



'
0001
'
,
'
2009-09-16
'
 
union
 
all
 
select



'
0002
'
,
'
2009-01-11
'
 
union
 
all
 
select



'
0002
'
,
'
2009-01-14
'
 
union
 
all
 
select



'
0002
'
,
'
2009-01-23
'
 
union
 
all
 
select



'
0002
'
,
'
2009-01-01
'
 
union
 
all
 
select



'
0002
'
,
'
2009-02-05
'
 
union
 
all
 
select



'
0002
'
,
'
2009-06-04
'
 
union
 
all
 
select



'
0002
'
,
'
2009-07-11
'
 
union
 
all
 
select



'
0002
'
,
'
2009-07-11
'
 
union
 
all
 
select



'
0002
'
,
'
2009-05-11
'
 
union
 
all
 
select



'
0002
'
,
'
2009-03-11
'
 
union
 
all
 
select



'
0002
'
,
'
2009-03-11
'
 
union
 
all
 
select



'
0002
'
,
'
2009-03-11
'
 
union
 
all
 
select



'
0002
'
,
'
2009-02-02
'
 
union
 
all
 
select



'
0002
'
,
'
2009-02-03
'
 
union
 
all
 
select



'
0002
'
,
'
2009-02-04
'
 
union
 
all
 
select



'
0002
'
,
'
2009-02-06
'
 
union
 
all
 
select



'
0002
'
,
'
2009-03-11
'
 
union
 
all
 
select



'
0002
'
,
'
2009-08-02
'
 
union
 
all
 
select



'
0002
'
,
'
2009-08-03
'
 
union
 
all
 
select



'
0002
'
,
'
2009-08-04
'
 
union
 
all
 
select



'
0002
'
,
'
2009-09-16
'
 
union
 
all
 
select



'
0002
'
,
'
2009-09-02
'
 
union
 
all
 
select



'
0002
'
,
'
2009-09-03
'
 
union
 
all
 
select



'
0002
'
,
'
2009-09-04
'
 
union
 
all
 
select



'
0002
'
,
'
2009-09-16
'
 
union
 
all
 
select



'
0003
'
,
'
2009-01-11
'
 
union
 
all
 
select



'
0003
'
,
'
2009-01-14
'
 
union
 
all
 
select



'
0003
'
,
'
2009-01-23
'
 
union
 
all
 
select



'
0003
'
,
'
2009-01-01
'
 
union
 
all
 
select



'
0003
'
,
'
2009-02-05
'
 
union
 
all
 
select



'
0003
'
,
'
2009-06-04
'
 
union
 
all
 
select



'
0003
'
,
'
2009-07-11
'
 
union
 
all
 
select



'
0003
'
,
'
2009-07-11
'
 
union
 
all
 
select



'
0003
'
,
'
2009-05-11
'
 
union
 
all
 
select



'
0003
'
,
'
2009-03-11
'
 
union
 
all
 
select



'
0003
'
,
'
2009-03-11
'
 
union
 
all
 
select



'
0003
'
,
'
2009-03-11
'
 
union
 
all
 
select



'
0003
'
,
'
2009-02-02
'
 
union
 
all
 
select



'
0003
'
,
'
2009-02-03
'
 
union
 
all
 
select



'
0003
'
,
'
2009-02-04
'
 
union
 
all
 
select



'
0003
'
,
'
2009-02-06
'
 
union
 
all
 
select



'
0003
'
,
'
2009-03-11
'
 
union
 
all
 
select



'
0003
'
,
'
2009-08-02
'
 
union
 
all
 
select



'
0003
'
,
'
2009-08-03
'
 
union
 
all
 
select



'
0003
'
,
'
2009-08-04
'
 
union
 
all
 
select



'
0003
'
,
'
2009-09-16
'
 
union
 
all
 
select



'
0003
'
,
'
2009-09-02
'
 
union
 
all
 
select



'
0003
'
,
'
2009-09-03
'
 
union
 
all
 
select



'
0003
'
,
'
2009-09-04
'
 
union
 
all
 
select



'
0003
'
,
'
2009-09-16
'
;


--
------------------------------------------






--
--备注：  ∨：正常上班


--
          ∥：加班（周六、周日在上班）


--
          ○：休息

 

 

 

 

SET
 ANSI_NULLS 
ON



GO



SET
 QUOTED_IDENTIFIER 
ON



GO





ALTER
 
procedure
 
[
dbo
]
.
[
test_proc
]
 
@year_month
 
varchar
(
6
)
=
null



/*


exec test_proc '200909'


*/



as



begin





declare
 
@maxdays
 
int
, 
@countdays
 
int
;


declare
 
@SQL
 
varchar
(
max
);




set
 
@SQL
=
'
SELECT t1.empid, t1.empname,
'
;




set
 
@countdays
=
1
;




--
如果输入参数为空,则取当前年、月的考勤记录情况



if
(
isnull
(
@year_month
,
''
)
=
''
)

    
SET
 
@year_month
=
CONVERT
(
varchar
(
6
),
getdate
(),
112
);




--
取本月最后一天



select
 
@maxdays
=
day
(
dateadd
(
month
,
1
,
convert
(
datetime
,
@year_month
+
'
01
'
))
-
1
);




while
(
@countdays
<=
@maxdays
)


begin


    
SET
 
@SQL
=
@SQL
+
'
[
'
+
CONVERT
(
VARCHAR
(
2
),
@countdays
)
+
'
]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=
'
+
CONVERT
(
VARCHAR
(
2
),
@countdays
)
+
'
 then 1 else 0 end)>0 and DatePart(w,
'''
+
@year_month
+
''
+right
(
'
0
'
+
convert
(
varchar
(
2
),
@countdays
),
2
)
+
'''
) not in (1,7) THEN 
''
∨
''
 

                                                                  WHEN SUM(CASE WHEN day(t2.logtime)=
'
+
CONVERT
(
VARCHAR
(
2
),
@countdays
)
+
'
 then 1 else 0 end)>0 and DatePart(w,
'''
+
@year_month
+
''
+right
(
'
0
'
+
convert
(
varchar
(
2
),
@countdays
),
2
)
+
'''
) in (1,7) THEN 
''
∥
''
 ELSE 
''
○
''
 END), 
'


    
SET
 
@countdays
=
@countdays
+
1
;


end





set
 
@sql
=
substring
(
@sql
,
1
,
len
(
@sql
)
-
2
);


set
 
@sql
=
@sql
+
'
 from t1 left join t2 on t1.empid=t2.empid and convert(varchar(6),t2.logtime,112)=
'''
+
@year_month
+
'''
 
'



set
 
@sql
=
@sql
+
'
 group by t1.empid, t1.empname, convert(varchar(6),t2.logtime,112) 
'



print
 (
@sql
);


exec
(
@SQL
);




end





GO





SET
 ANSI_NULLS 
OFF



GO



SET
 QUOTED_IDENTIFIER 
OFF



GO



--
-------------------------------------------------------------------------------------






exec
 test_proc 
'
200909
'

-- -------------------------------------------------------------------------------------------------- empid empname 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 0001 张三 ∨ ○ ○ ○ ∥ ○ ∨ ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ○ ∨ ∨ ○ ○ ∨ ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ∨ 0002 李四 ∨ ○ ○ ○ ○ ○ ∨ ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ○ ∨ ∨ ○ ○ ∨ ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ∨ 0003 王五 ∨ ○ ○ ○ ○ ○ ∨ ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ○ ∨ ∨ ○ ○ ∨ ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ∨ 0004 赵六 ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ∨