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

[chimeilong]

表结构如下：
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


在线等哇~谢谢各位大大了~！

[nianran520]

--> 测试数据：[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

[--小F--]

其实看不出来你的判断标准的

[--小F--]

----------------------------------------------------------------
-- 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 行受影响)
*/

[易十五]

rrr

[百年树人]

是否存在一天不止两个时间的？

[东那个升]

1楼正解

[luoyoumou]

-- 没有取数标准(考勤制度)，你取的数据一一点意义也没有!


-- 考勤数据，一般是要按照公司的考勤制度去取数据的!


-- 楼主扯卵蛋......

[chimeilong]

[Quote=引用 7 楼 luoyoumou 的回复:]
-- 没有取数标准(考勤制度)，你取的数据一一点意义也没有!


-- 考勤数据，一般是要按照公司的考勤制度去取数据的!


-- 楼主扯卵蛋......
[/Quote]

你才扯蛋呢~这个和考勤制度无关好吧，只是一个大概的考情统计而已，只是需要大概的显示一下这个人每天的记录而已~

[chimeilong]

[Quote=引用 5 楼 josy 的回复:]
是否存在一天不止两个时间的？
[/Quote]

存在！所以楼上的代码不能准确的统计出来~

[chimeilong]

[Quote=引用 1 楼 nianran520 的回复:]
SQL code--> 测试数据：[tb]ifobject_id('[tb]')isnotnulldroptable[tb]gocreatetable[tb]([ID]int,[ATTTIME]datetime,[UserID]int)insert[tb]select1,'2010-01-01 8:10:00',1unionallselect2,'2010-01-01 8:15:00',1unio?-
[/Quote]

如果一个人一天有多条记录怎么办？我需要得到最早的一早和最晚的一条，最早的一天超过12点，那么montime就为空，同样最晚的一条小于12点也为空，全天只有一条记录就需要判断是否超过12点

[东那个升]

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 CONVERT(char(18),[ATTTIME],100) like
  from tb

with cte as(
select [UserID],[ATTTIME],CONVERT(char(8),[ATTTIME],112) as date1
,case when CONVERT(char(18),[ATTTIME],100) like '%AM' then 'montime' else 'aftertime' end as AMORPM
  from tb)


select userid,date1,[montime],[aftertime]
from ( select userid,date1,AMORPM,case when AMORPM='montime' then MIN(atttime) else MAX(atttime) end as atttime
from cte 
group by userid,date1,AMORPM
) a
PIVOT
( min([ATTTIME]) 
for AMORPM  in([montime],[aftertime])) as pvt
order by userid

[东那个升]

注掉

select CONVERT(char(18),[ATTTIME],100) like
from tb