表:
1 KQ_STATE A Y 上午迟到
2 KQ_STATE B Y 下午早退
3 KQ_STATE C Y 上午未考勤
4 KQ_STATE D Y 下午未考勤
5 KQ_STATE E Y 上午已考勤
6 KQ_STATE F Y 下午已考勤
要求:按账号把每个月各个账号的迟到,早退等情况汇总到一个表里,表自己建 (在oracle pl sql 下)
1 KQ_STATE A Y 上午迟到
2 KQ_STATE B Y 下午早退
3 KQ_STATE C Y 上午未考勤
4 KQ_STATE D Y 下午未考勤
5 KQ_STATE E Y 上午已考勤
6 KQ_STATE F Y 下午已考勤
要求:按账号把每个月各个账号的迟到,早退等情况汇总到一个表里,表自己建 (在oracle pl sql 下)
有两个表:人员表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-06 21:02: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(8000);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 ''∨'' '
SET @SQL=@SQL+' 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=@sql+' COUNT(DISTINCT CONVERT(VARCHAR(8),t2.logtime,112)) AS [出勤∨] 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);
PRINT(LEN(@sql));
EXEC(@SQL)END
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
---------------------------------------------------------------------------------------EXEC test_proc '200909'
-- 不过:这是SQL Server的,若要在Oracle下运行的话,你需要稍微改写!