有两个表:人员表T1,登录信息表T2
T1有两个栏位:EMPID(人员ID),EMPNAME(人员姓名)
T2有两个栏位:EMPID(人员ID),LOGTIME(登录时间)
按月份统计人员每天是否登录过系统,查询出来格式如下:
姓名 1,2,3,4……一直到31号(选择的月份有几天就是多少天),最后再加这个人当月的出勤,只要当天登录过就是出勤了!
详细格式请看下面的图片:
http://60.216.13.200/123.jpg
谢谢啊!!!
T1有两个栏位:EMPID(人员ID),EMPNAME(人员姓名)
T2有两个栏位:EMPID(人员ID),LOGTIME(登录时间)
按月份统计人员每天是否登录过系统,查询出来格式如下:
姓名 1,2,3,4……一直到31号(选择的月份有几天就是多少天),最后再加这个人当月的出勤,只要当天登录过就是出勤了!
详细格式请看下面的图片:
http://60.216.13.200/123.jpg
谢谢啊!!!
姓名
2009-10-1 Y
2009-10-2 Y
2009-10-3 N
......
2009-10-31 Y
然后行列转换最后一列汇总
呵呵提的一个思路
电脑快没电了~~~
-------------------------------------
-- 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
GOALTER procedure [dbo].[test_proc] @year_month varchar(6)=null
/*
exec test_proc '200909'
*/
as
begindeclare @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)+' and DatePart(w,'''+@year_month+''+right('0'+convert(varchar(2),@countdays),2)+''') not in (1,7) 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)+' and DatePart(w,'''+@year_month+''+right('0'+convert(varchar(2),@countdays),2)+''') in (1,7) 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;
endset @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);endGOSET 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 赵六 ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ∨ ∨ ∨ ○ ○ ∨ ∨ ∨
-------------------------------------
-- Author : Luoyoumou
-- Comment: 三月红梨
-- Date : 2009-11-04 06:37:17
---------------------------------------SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER procedure [dbo].[test_proc] @year_month varchar(6)=null
/*
exec test_proc '200909'
*/
as
begindeclare @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)+' and DatePart(w,'''+@year_month+''+right('0'+convert(varchar(2),@countdays),2)+''') not in (1,7) 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)+' and DatePart(w,'''+@year_month+''+right('0'+convert(varchar(2),@countdays),2)+''') in (1,7) 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;
endset @sql=substring(@sql,1,len(@sql)-2);
set @sql=@sql+' from t1 inner 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);endGOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
-----------修正版----------------------
-- 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
GOALTER procedure [dbo].[test_proc] @year_month varchar(6)=null
/*
exec test_proc '200909'
*/
as
begindeclare @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)+' and DatePart(w,'''+@year_month+''+right('0'+convert(varchar(2),@countdays),2)+''') not in (1,7) 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)+' and DatePart(w,'''+@year_month+''+right('0'+convert(varchar(2),@countdays),2)+''') in (1,7) 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;
endset @sql=substring(@sql,1,len(@sql)-2);
set @sql=@sql+' from t1 inner 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);endGOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
---------------------------------------------------------------------------------------exec test_proc '200909'
-----------修正优化版----------------------
-- Author : Luoyoumou
-- Comment: 三月红梨
-- Date : 2009-11-04 07:12: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
GOALTER procedure [dbo].[test_proc] @year_month varchar(6)=null
/*
exec test_proc '200909'
*/
as
begindeclare @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;
endset @sql=substring(@sql,1,len(@sql)-2);
set @sql=@sql+' from t1 inner 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);endGOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
---------------------------------------------------------------------------------------exec test_proc '200909'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER procedure [dbo].[test_proc] @year_month varchar(6)=null
/*
exec test_proc '200909'
*/
as
begindeclare @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;
endset @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);endGOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
---------------------------------------------------------------------------------------exec test_proc '200909'
老大你太谦虚了,,我直行的时候有点错误,你看看是怎么回事啊!SELECT t1.empid, t1.empname,[1]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=1 then 1 else 0 end)>0 and DatePart(w,'20090901') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=1 then 1 else 0 end)>0 and DatePart(w,'20090901') in (1,7) THEN '∥' ELSE '○' END), [2]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=2 then 1 else 0 end)>0 and DatePart(w,'20090902') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=2 then 1 else 0 end)>0 and DatePart(w,'20090902') in (1,7) THEN '∥' ELSE '○' END), [3]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=3 then 1 else 0 end)>0 and DatePart(w,'20090903') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=3 then 1 else 0 end)>0 and DatePart(w,'20090903') in (1,7) THEN '∥' ELSE '○' END), [4]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=4 then 1 else 0 end)>0 and DatePart(w,'20090904') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=4 then 1 else 0 end)>0 and DatePart(w,'20090904') in (1,7) THEN '∥' ELSE '○' END), [5]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=5 then 1 else 0 end)>0 and DatePart(w,'20090905') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=5 then 1 else 0 end)>0 and DatePart(w,'20090905') in (1,7) THEN '∥' ELSE '○' END), [6]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=6 then 1 else 0 end)>0 and DatePart(w,'20090906') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=6 then 1 else 0 end)>0 and DatePart(w,'20090906') in (1,7) THEN '∥' ELSE '○' END), [7]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=7 then 1 else 0 end)>0 and DatePart(w,'20090907') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=7 then 1 else 0 end)>0 and DatePart(w,'20090907') in (1,7) THEN '∥' ELSE '○' END), [8]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=8 then 1 else 0 end)>0 and DatePart(w,'20090908') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=8 then 1 else 0 end)>0 and DatePart(w,'20090908') in (1,7) THEN '∥' ELSE '○' END), [9]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=9 then 1 else 0 end)>0 and DatePart(w,'20090909') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=9 then 1 else 0 end)>0 and DatePart(w,'20090909') in (1,7) THEN '∥' ELSE '○' END), [10]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=10 then 1 else 0 end)>0 and DatePart(w,'20090910') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=10 then 1 else 0 end)>0 and DatePart(w,'20090910') in (1,7) THEN '∥' ELSE '○' END), [11]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=11 then 1 else 0 end)>0 and DatePart(w,'20090911') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=11 then 1 else 0 end)>0 and DatePart(w,'20090911') in (1,7) THEN '∥' ELSE '○' END), [12]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=12 then 1 else 0 end)>0 and DatePart(w,'20090912') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=12 then 1 else 0 end)>0 and DatePart(w,'20090912') in (1,7) THEN '∥' ELSE '○' END), [13]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=13 then 1 else 0 end)>0 and DatePart(w,'20090913') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=13 then 1 else 0 end)>0 and DatePart(w,'20090913') in (1,7) THEN '∥' ELSE '○' END), [14]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=14 then 1 else 0 end)>0 and DatePart(w,'20090914') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=14 then 1 else 0 end)>0 and DatePart(w,'20090914') in (1,7) THEN '∥' ELSE '○' END), [15]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=15 then 1 else 0 end)>0 and DatePart(w,'20090915') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=15 then 1 else 0 end)>0 and DatePart(w,'20090915') in (1,7) THEN '∥' ELSE '○' END), [16]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=16 then 1 else 0 end)>0 and DatePart(w,'20090916') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=16 then 1 else 0 end)>0 and DatePart(w,'20090916') in (1,7) THEN '∥' ELSE '○' END), [17]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=17 then 1 else 0 end)>0 and DatePart(w,'20090917') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=17 then 1 else 0 end)>0 and DatePart(w,'20090917') in (1,7) THEN '∥' ELSE '○' END), [18]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=18 then 1 else 0 end)>0 and DatePart(w,'20090918') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=18 then 1 else 0 end)>0 and DatePart(w,'20090918') in (1,7) THEN '∥' ELSE '○' END), [19]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=19 then 1 else 0 end)>0 and DatePart(w,'20090919') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=19 then 1 else 0 end)>0 and DatePart(w,'20090919') in (1,7) THEN '∥' ELSE '○' END), [20]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=20 then 1 else 0 end)>0 and DatePart(w,'20090920') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=20 then 1 else 0 end)>0 and DatePart(w,'20090920') in (1,7) THEN '∥' ELSE '○' END), [21]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=21 then 1 else 0 end)>0 and DatePart(w,'20090921') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=21 then 1 else 0 end)>0 and DatePart(w,'20090921') in (1,7) THEN '∥' ELSE '○' END), [22]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=22 then 1 else 0 end)>0 and DatePart(w,'20090922') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=22 then 1 else 0 end)>0 and DatePart(w,'20090922') in (1,7) THEN '∥' ELSE '○' END), [23]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=23 then 1 else 0 end)>0 and DatePart(w,'20090923') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=23 then 1 else 0 end)>0 and DatePart(w,'20090923') in (1,7) THEN '∥' ELSE '○' END), [24]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=24 then 1 else 0 end)>0 and DatePart(w,'20090924') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=24 then 1 else 0 end)>0 and DatePart(w,'20090924') in (1,7) THEN '∥' ELSE '○' END), [25]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=25 then 1 else 0 end)>0 and DatePart(w,'20090925') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=25 then 1 else 0 end)>0 and DatePart(w,'20090925') in (1,7) THEN '∥' ELSE '○' END), [26]=(CASE WHEN SUM(CASE WHEN day(t2.logtime)=26 then 1 else 0 end)>0 and DatePart(w,'20090926') not in (1,7) THEN '∨'
WHEN SUM(CASE WHEN day(t2.logtime)=26 then 1 else 0 end)>0 and DatePart(w,'20090926 fr
服务器: 消息 105,级别 15,状态 1,行 27
字符串 '20090926 fr' 之前有未闭合的引号。
服务器: 消息 170,级别 15,状态 1,行 27
第 27 行: '20090926 fr' 附近有语法错误。
服务器: 消息 105,级别 15,状态 1,行 27
字符串 '20090926 fr' 之前有未闭合的引号。
服务器: 消息 170,级别 15,状态 1,行 27
第 27 行: '20090926 fr' 附近有语法错误。右引号跑哪去了?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER procedure [dbo].[test_proc] @year_month varchar(6)=null
/*
exec test_proc '200909'
*/
as
begindeclare @maxdays int, @countdays int;
declare @SQL1 varchar(8000), @SQL2 VARCHAR(8000);set @SQL1='SELECT t1.empid, t1.empname,';
set @SQL2='';
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
if(@countdays<=15)
BEGIN
SET @SQL1=@SQL1+'['+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), '
END
ELSE
BEGIN
SET @SQL2=@SQL2+'['+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), ' END
SET @countdays=@countdays+1;
endset @sql2=substring(@sql2,1,len(@sql2)-1);
set @sql2=@sql2+' from t1 left join t2 on t1.empid=t2.empid and convert(varchar(6),t2.logtime,112)='''+@year_month+''' '
set @sql2=@sql2+' group by t1.empid, t1.empname, convert(varchar(6),t2.logtime,112) '
print (@sql1+@sql2);
exec(@SQL1+@SQL2);end
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
http://60.216.13.200/123.jpg
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER procedure [dbo].[test_proc] @year_month varchar(6)=null
/*
exec test_proc '200909'
*/
as
begindeclare @maxdays int, @countdays int;
declare @SQL1 varchar(8000), @SQL2 VARCHAR(8000);set @SQL1='SELECT t1.empid, t1.empname,';
set @SQL2='';
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
if(@countdays<=15)
BEGIN
SET @SQL1=@SQL1+'['+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), '
END
ELSE
BEGIN
SET @SQL2=@SQL2+'['+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), '
END
SET @countdays=@countdays+1;
endset @sql2=@sql2+' count(distinct logtime) as [出勤∨] from t1 left join t2 on t1.empid=t2.empid and convert(varchar(6),t2.logtime,112)='''+@year_month+''' '
set @sql2=@sql2+' group by t1.empid, t1.empname, convert(varchar(6),t2.logtime,112) '
print (@sql1+@sql2);
exec(@SQL1+@SQL2);end
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
老大!太感谢!你是我见过SQL最牛的人!最乐于助人的人!你这样的技术早晚都会成为微软的MPV的!!我永远顶你!!!!
撤退,交给luoyoumou