呵呵 并不是设置那个指纹鉴定器作者: ecust 时间: 2007-1-29 11:16
最晚记录可以这么写：
SELECT MAX(日期时间) AS Expr1, 姓名
FROM 表名
WHERE (日期时间 BETWEEN '2005-8-1 00:00:00' AND '2005-8-1 23:59:59') AND
(姓名 = 'ecust')
GROUP BY 姓名
WHERE 条件里怎么就固定了姓名了呢?作者: sunxy5291 时间: 2007-1-29 11:56
最早：
select * from table1 where not exists (select id from table1 where id = a.id and 日期时间 < a.日期时间) as a
最晚：
select * from table1 where not exists(select * from table1 where id = a.id and 日期时间 > a.日期时间) as a作者: sunxy5291 时间: 2007-1-29 12:04 标题: 好像不对了，对于某一个人id不唯一，不能关联！应该这样写： 最早时间：
select * from table1 where not exists (select id from table1 where 姓名 = a.姓名 and 日期时间 < a.日期时间) as a
最晚时间：
select * from table1 where not exists(select * from table1 where 姓名 = a.姓名 and 日期时间 > a.日期时间) as a作者: sunxy5291 时间: 2007-1-29 12:06
以上是1的回答，请楼主检查
2、3我认为还比较难，等吃完饭回来研究！作者: roadxizi 时间: 2007-1-29 12:35
好的,谢谢作者: ecust 时间: 2007-1-29 13:02
到是可以按照姓名分组,所有员工某天的最早和最晚记录都出来作者: sunxy5291 时间: 2007-1-29 14:00
2、3我是不会了
不过我帮你问问别的高手！作者: ecust 时间: 2007-1-29 15:03
纯SQL写是一个很痛苦的事情，我感觉。。。
干吗要这种方式列。。。作者: sunxy5291 时间: 2007-1-29 15:04 标题: 看这个答案可以吗，我也不会做，别人帮忙做的 2.
迟到分钟：
select 姓名,DATEDIFF(minute,cast((convert(char(10),日期时间,120) + ' 09:00:00') as datetime),日期时间) as 迟到分钟 from table1 where not exists (select id from table1 where 姓名 = a.姓名 and 日期时间 < a.日期时间) as a
早退分钟：
select 姓名,DATEDIFF(minute,日期时间,cast((convert(char(10),日期时间,120) + ' 18:00:00') as datetime),) as 迟到分钟 from table1 where not exists(select * from table1 where 姓名 = a.姓名 and 日期时间 > a.日期时间) as a作者: roadxizi 时间: 2007-1-30 14:42
修改以后对了,谢谢了作者: sunxy5291 时间: 2007-1-30 16:49
对了？
那你都修改什么啦
发出来作者: roadxizi 时间: 2007-1-30 18:15
select 姓名,DATEDIFF(minute,cast((convert(char(10),日期时间,120) + ' 09:00:00') as datetime),日期时间) as 迟到分钟
from [考勤报表] a
where not exists
(
select id from [考勤报表]
where 姓名 = a.姓名
and 日期时间 < a.日期时间
)作者: sunxy5291 时间: 2007-1-31 09:11
把所有的都类出来，这里是bbs你要让大家都学习啊！作者: roadxizi 时间: 2007-1-31 09:36
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[get_yuangong_chidao_xx_cdzt]
(
@name varchar(100),
@date datetime,
@type varchar(100)
)
AS
declare @Year int,@Month int,@Day int,@Minute int,@Minute2 int
set @Year=year(@date)
set @Month=month(@date)
set @Minute=9*60+3
set @Minute2=18*60+3
if @type='迟到统计'
begin
select [姓名],[编号],[日期时间],[type],[type2],DATEDIFF(minute,cast((convert(char(10),[日期时间],120) + ' 09:00:00') as datetime),日期时间) as '分钟数'
from
(
select *,'迟到' as [type],day(DATEADD("Day",1,[日期时间]))-day(DATEADD("Minute", 0-@Minute, DATEADD("Day",1,[日期时间]))) as [type2]
from [考勤报表] a
where
ID =
(
select top 1 ID
from [考勤报表] b
where year(b.[日期时间])=@Year and month(b.[日期时间])=@Month and day(b.[日期时间])=day(a.[日期时间]) and b.[编号]=a.[编号]
order by b.[日期时间] asc
)
)c
where [type2]=0
and [姓名]=@name
and year([日期时间])=year(@date)
and month([日期时间])=month(@date)
order by [日期时间]
end
else
begin
select [姓名],[编号],[日期时间],[type],[type2],DATEDIFF(minute,[日期时间],cast((convert(char(10),[日期时间],120) + ' 18:00:00') as datetime)) as '分钟数'
from
(
select *,'早退' as [type],1-(day(DATEADD("Day",1,[日期时间]))-day(DATEADD("Minute", 0-@Minute2, DATEADD("Day",1,[日期时间])))) as [type2]
from [考勤报表] a
where ID =
(
select top 1 ID
from [考勤报表] b
where year(b.[日期时间])=@Year and month(b.[日期时间])=@Month and day(b.[日期时间])=day(a.[日期时间]) and b.[编号]=a.[编号]
order by b.[日期时间] desc
)
)c
--where [编号]=208
--and day(a.[日期时间])=1
--order by [编号],[日期时间],[type]
都是上面的代码作者: roadxizi 时间: 2007-1-31 10:54
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[get_yuangong_chidao]
(
@date datetime
)
AS
declare @Year int,@Month int,@Day int,@Minute int,@Minute2 int
set @Year=year(@date)
set @Month=month(@date)
set @Minute=9*60
set @Minute2=18*60
select [姓名],[编号],count([type2]) as '迟到早退次数'
from
(
select *,'i' as [type],day(DATEADD("Day",1,[日期时间]))-day(DATEADD("Minute", 0-@Minute, DATEADD("Day",1,[日期时间]))) as [type2]
from [考勤报表] a
where
ID =
(
select top 1 ID
from [考勤报表] b
where year(b.[日期时间])=@Year and month(b.[日期时间])=@Month and day(b.[日期时间])=day(a.[日期时间]) and b.[编号]=a.[编号]
order by b.[日期时间] asc
)
union all
select *,'o' as [type],1-(day(DATEADD("Day",1,[日期时间]))-day(DATEADD("Minute", 0-@Minute2, DATEADD("Day",1,[日期时间])))) as [type2]
from [考勤报表] a
where ID =
(
select top 1 ID
from [考勤报表] b
where year(b.[日期时间])=@Year and month(b.[日期时间])=@Month and day(b.[日期时间])=day(a.[日期时间]) and b.[编号]=a.[编号]
order by b.[日期时间] desc
)
)c
--where [编号]=208
--and day(a.[日期时间])=1
--order by [编号],[日期时间],[type]
where [type2]=0
and year([日期时间])=year(@date)
and month([日期时间])=month(@date)
group by [姓名],[编号]作者: rainyday32 时间: 2007-1-31 11:02
还在学习SQL，上面的这些还需要努力才能看明白阿~作者: keibi 时间: 2007-2-6 19:17
用临时表很容易处理了作者: roadxizi 时间: 2007-2-7 09:44
怎么个处理法？