有两个表:人员表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
赵六 ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ∨