求考勤查询SQL查询语句或过程

chimeilong 2010-02-04 08:04:05
表结构如下:
ID ATTTIME UserID
1 2010-01-01 8:10:00 1
2 2010-01-01 8:15:00 1
3 2010-01-01 17:10:00 2
4 2010-01-02 8:05:00 1
5 2010-01-02 17:00:00 1


我想得到结果如下:

userid date montime aftertime
1 2010-01-01 8:10:00 null
1 2010-01-02 8:05:00 17:00:00
2 2010-01-01 null 17:10:00


在线等哇~谢谢各位大大了~!
...全文
144 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
Q315054403 2010-02-06
  • 打赏
  • 举报
回复
考勤肯定不是三两条SQL指令就解决问题的,呵
fa_ge 2010-02-05
  • 打赏
  • 举报
回复


--借楼上数据一用
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[ATTTIME] datetime,[UserID] int)
insert [tb]
select 1,'2010-01-01 8:10:00',1 union all
select 2,'2010-01-01 8:15:00',1 union all
select 3,'2010-01-01 17:10:00',2 union all
select 4,'2010-01-02 8:05:00',1 union all
select 5,'2010-01-02 17:00:00',1


SELECT
UserId,[Date],
(SELECT TOP 1 RIGHT(CONVERT(VARCHAR(19),[ATTTIME],120),8) FROM tb WHERE UserID=A.UserID AND DATEPART(HH,[ATTTIME])=8 AND DATEDIFF(DAY,[ATTTIME],A.[Date])=0 ORDER BY [ATTTIME])montime,
(SELECT TOP 1 RIGHT(CONVERT(VARCHAR(19),[ATTTIME],120),8) FROM tb WHERE UserID=A.UserID AND DATEPART(HH,[ATTTIME])=17 AND DATEDIFF(DAY,[ATTTIME],A.[Date])=0 ORDER BY [ATTTIME])aftertime

FROM
(
SELECT DISTINCT UserID,CONVERT(VARCHAR(10),[ATTTIME],120)[Date] FROM tb
)A

/*
UserId Date montime aftertime
----------- ---------- ---------------- ----------------
1 2010-01-01 08:10:00 NULL
1 2010-01-02 08:05:00 17:00:00
2 2010-01-01 NULL 17:10:00

(所影响的行数为 3 行)
*/
duanxugang 2010-02-05
  • 打赏
  • 举报
回复
帮顶
bychgh 2010-02-05
  • 打赏
  • 举报
回复
学习,帮顶
nianran520 2010-02-05
  • 打赏
  • 举报
回复

--只有一记录的怎么取?
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[ATTTIME] datetime,[UserID] int)
insert [tb]
select 1,'2010-01-01 8:10:00',1 union all
select 2,'2010-01-01 8:15:00',1 union all
select 3,'2010-01-01 17:10:00',2 union all
select 4,'2010-01-02 8:05:00',1 union all
select 5,'2010-01-02 17:00:00',1


select userid,
convert(varchar(10),[ATTTIME],120) as date,
case when min(convert(varchar(8),[ATTTIME],108)) >= '12:00:00' then null else min(convert(varchar(8),[ATTTIME],108)) end as montime,
case when max(convert(varchar(8),[ATTTIME],108)) <= '12:00:00' then null else max(convert(varchar(8),[ATTTIME],108)) end as aftertime
from [tb]
group by userid,convert(varchar(10),[ATTTIME],120)
order by userid

----------------------------------
1 2010-01-01 08:10:00 NULL
1 2010-01-02 08:05:00 17:00:00
2 2010-01-01 NULL 17:10:00

fa_ge 2010-02-05
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 s_111111 的回复:]
要考慮早上7點或者晚上18點的吧  有的時候早來或者晚打卡
[/Quote]

考勤处理肯定不是楼主这样一张表的,需要班次表,才能判断出他打卡时间
cailee 2010-02-05
  • 打赏
  • 举报
回复
declare @t table(id int identity(1,1),ATTTIME datetime,userid int)
insert @t(ATTTIME,userid) select '2010-01-01 8:10:00',1
union all select '2010-01-01 8:15:00',1
union all select '2010-01-01 17:10:00',2
union all select '2010-01-02 8:05:00',1
union all select '2010-01-02 17:00:00',1

--示例数据
select * FROM @T

--查询
select userid
,convert(char(10),atttime,120) as date
,montime=min(case when datepart(hour,atttime)<=12 then convert(char(8),atttime,108) else null end)
,aftertime=max(case when datepart(hour,atttime)>12 then convert(char(8),atttime,108) else null end)
from @t
group by userid,convert(char(10),atttime,120)
order by userid
/*

(5 行受影响)
id ATTTIME userid
----------- ----------------------- -----------
1 2010-01-01 08:10:00.000 1
2 2010-01-01 08:15:00.000 1
3 2010-01-01 17:10:00.000 2
4 2010-01-02 08:05:00.000 1
5 2010-01-02 17:00:00.000 1

(5 行受影响)

userid date montime aftertime
----------- ---------- -------- ---------
1 2010-01-01 08:10:00 NULL
1 2010-01-02 08:05:00 17:00:00
2 2010-01-01 NULL 17:10:00
警告: 聚合或其他 SET 操作消除了空值。

(3 行受影响)

*/
s_111111 2010-02-05
  • 打赏
  • 举报
回复
要考慮早上7點或者晚上18點的吧 有的時候早來或者晚打卡
--小F-- 2010-02-04
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-02-04 20:11:42
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[ATTTIME] datetime,[UserID] int)
insert [tb]
select 1,'2010-01-01 8:10:00',1 union all
select 2,'2010-01-01 8:15:00',1 union all
select 3,'2010-01-01 17:10:00',2 union all
select 4,'2010-01-02 8:05:00',1 union all
select 5,'2010-01-02 17:00:00',1
--------------开始查询--------------------------
select
userid,
convert(varchar(10),[ATTTIME],120) as date,
case datepart(hour,min([ATTTIME])) when 8 then convert(varchar(8),min([ATTTIME]),108) else null end as montime,
case datepart(hour,max([ATTTIME])) when 17 then convert(varchar(8),max([ATTTIME]),108) else null end as aftertime
from
[tb]
group by
userid,convert(varchar(10),[ATTTIME],120)
order by
userid

----------------结果----------------------------
/* userid date montime aftertime
----------- ---------- -------- ---------
1 2010-01-01 08:10:00 NULL
1 2010-01-02 08:05:00 17:00:00
2 2010-01-01 NULL 17:10:00

(3 行受影响)
*/
jwdream2008 2010-02-04
  • 打赏
  • 举报
回复
Up!
nianran520 2010-02-04
  • 打赏
  • 举报
回复
--还发两个?
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[ATTTIME] datetime,[UserID] int)
insert [tb]
select 1,'2010-01-01 8:10:00',1 union all
select 2,'2010-01-01 8:15:00',1 union all
select 3,'2010-01-01 17:10:00',2 union all
select 4,'2010-01-02 8:05:00',1 union all
select 5,'2010-01-02 17:00:00',1

select * from [tb]
/*
userid date montime aftertime
1 2010-01-01 8:10:00 null
1 2010-01-02 8:05:00 17:00:00
2 2010-01-01 null 17:10:00
*/

select userid,
convert(varchar(10),[ATTTIME],120) as date,
case datepart(hour,min([ATTTIME])) when 8 then convert(varchar(8),min([ATTTIME]),108) else null end as montime,
case datepart(hour,max([ATTTIME])) when 17 then convert(varchar(8),max([ATTTIME]),108) else null end as aftertime
from [tb]
group by userid,convert(varchar(10),[ATTTIME],120)
order by userid
----------------------------------
1 2010-01-01 08:10:00 NULL
1 2010-01-02 08:05:00 17:00:00
2 2010-01-01 NULL 17:10:00

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧