麻烦各位高手与大版帮我看一下老问题.

Question

本帖最后由 chirea 于 2011-10-14 22:20:40 编辑

Answer 1

改改排班转换就行了use tempdb
go
set nocount on ;
if OBJECT_ID('PBCMTEST_NEW') is not null
drop table PBCMTEST_NEW
go
--测试数据
CREATE  TABLE PBCMTEST_NEW
(
员工编号 nvarchar(20),
日期  datetime,
OnStartTime1 datetime, --1班上班开始打卡时间
OnDuty1 datetime, 
OnENDTime1 datetime, --1班上班结束打卡时间OffStartTime1 datetime, --1班下班开始打卡时间
OffDuty1 datetime, 
OffENDTime1 datetime,--1班下班结束打卡时间
 
OnStartTime2 datetime, --2班上班开始打卡时间
OnDuty2 datetime, 
OnENDTime2 datetime,--2班上班结束打卡时间OffStartTime2 datetime, --2班下班开始打卡时间
OffDuty2 datetime,
OffENDTime2 datetime--2班下班结束打卡时间
)insert into PBCMTEST_NEW select
 '005','2011/10/01',
'1900-01-01 06:00:00.000',
'1900-01-01 08:00:00.000',
'1900-01-01 11:30:00.000','1900-01-01 08:30:00.000',
'1900-01-01 12:00:00.000',
'1900-01-01 13:00:00.000',
'1900-01-01 12:15:00.000',
'1900-01-01 13:30:00.000',
'1900-01-01 15:30:00.000','1900-01-01 14:00:00.000',
'1900-01-01 17:30:00.000',
'1900-01-01 22:00:00.000'insert into PBCMTEST_NEW select
 '005','2011/10/02',
'1900-01-01 06:00:00.000',
'1900-01-01 08:00:00.000',
'1900-01-01 11:30:00.000','1900-01-01 08:30:00.000',
'1900-01-01 12:00:00.000',
'1900-01-01 13:00:00.000',
'1900-01-01 12:15:00.000',
'1900-01-01 13:30:00.000',
'1900-01-01 15:30:00.000','1900-01-01 14:00:00.000',
'1900-01-01 17:30:00.000',
'1900-01-01 22:00:00.000'insert into PBCMTEST_NEW select
 '005','2011/10/03',
'1900-01-01 06:00:00.000',
'1900-01-01 08:00:00.000',
'1900-01-01 11:30:00.000','1900-01-01 08:30:00.000',
'1900-01-01 12:00:00.000',
'1900-01-01 13:00:00.000',
'1900-01-01 12:15:00.000',
'1900-01-01 13:30:00.000',
'1900-01-01 15:30:00.000','1900-01-01 14:00:00.000',
'1900-01-01 17:30:00.000',
'1900-01-01 22:00:00.000'insert into PBCMTEST_NEW select
 '005','2011/10/04',
'1900-01-01 06:00:00.000',
'1900-01-01 08:00:00.000',
'1900-01-01 11:30:00.000','1900-01-01 08:30:00.000',
'1900-01-01 12:00:00.000',
'1900-01-01 13:00:00.000',
'1900-01-01 12:15:00.000',
'1900-01-01 13:30:00.000',
'1900-01-01 15:30:00.000','1900-01-01 14:00:00.000',
'1900-01-01 17:30:00.000',
'1900-01-01 22:00:00.000'go
IF OBJECT_ID('DAT') IS NOT NULL
    DROP TABLE DAT
go
CREATE  TABLE DAT
(
员工编号 nvarchar(20),
日期  datetime,
时间1 datetime, 
时间2 datetime, 
时间3 datetime, 
时间4 datetime
)
insert into DAT select '005','2011/10/04','07:49:00.000','12:22:00.000','12:01:00.000','12:17:00.000'
insert into DAT select '005','2011/10/05','07:56:00.000','12:01:00.000','12:19:00.000','17:32:00.000'----测试代码结束
--以下是代码---
;with b
as
(
 select 员工编号,TIMEs=日期+ TIMEs,TIMEs2=TIMEs  FROM DAT UNPIVOT(TIMEs FOR Cols IN([时间1],[时间2],[时间3],[时间4])) AS DAT2 
)
,a
as
(
select  
    员工编号,
    日期,
    OnStartTime1,
    OnENDTime1=case when OnStartTime1>OnENDTime1 then OnENDTime1+1 else OnENDTime1 end,
    OffStartTime1=case when OnStartTime1>OffStartTime1 then OffStartTime1+1 else OffStartTime1 end,
    OffENDTime1=case when OnENDTime1>OffENDTime1 then OffENDTime1+1 else OffENDTime1 end,
    OnStartTime2=case when OffStartTime1>OnStartTime2 then OnStartTime2+1 else OnStartTime2 end,
    OnENDTime2=case when OffENDTime1>OnENDTime2 then OnENDTime2+1 else OnENDTime2 end,
    OffStartTime2=case when OnStartTime2>OffStartTime2 then OffStartTime2+1 else OffStartTime2 end,
    OffENDTime2=case when OnENDTime2>OffENDTime2 then OffENDTime2+1 else OffENDTime2 end
from PBCMTEST_NEW 
)
,C
as
(
SELECT 
    a.员工编号,
    a.日期,
    [一班上]=max(CASE WHEN b.TIMEs BETWEEN a.日期+a.OnStartTime1 AND  a.日期+case when a.OnStartTime1>a.OnENDTime1 then a.OnENDTime1+1 else a.OnENDTime1 end      THEN b.TIMEs2 END),
    [一班下]=max(CASE WHEN b.TIMEs BETWEEN a.日期+a.OffStartTime1 AND a.日期+case when a.OffStartTime1>a.OffENDTime1 then a.OffENDTime1+1 else a.OffENDTime1 end  THEN b.TIMEs2 END),                    
    [二班上]=max(Case WHEN b.TIMEs BETWEEN a.日期+a.OnStartTime2 AND  a.日期+case when a.OnStartTime2>a.OnENDTime2 then a.OnENDTime2+1 else a.OnENDTime2 end      THEN b.TIMEs2 END),    
    [二班下]=max(CASE WHEN b.TIMEs BETWEEN a.日期+a.OffStartTime2 AND a.日期+case when a.OffStartTime2>a.OffENDTime2 then a.OffENDTime2+1 else a.OffENDTime2 end  THEN b.TIMEs2 END)
FROM a
    inner  JOIN  b ON a.[员工编号]=b.[员工编号]
group by a.员工编号,a.日期
)
select * 
from C 
where coalesce([一班上],[一班下],[二班上],[二班下]) is not null
order by 日期,员工编号/*
DAT表里面都没有03的数据.
也没有跨零点的为什么会有03号的,且这个时间了不知哪来的.还得麻烦帮忙解决一下
*/
/*
员工编号 日期 一班上 一班下 二班上 二班下
005 2011-10-04 00:00:00.000 1900-01-01 07:49:00.000 1900-01-01 12:22:00.000 1900-01-01 12:22:00.000 NULL
*/

Answer 2

原贴没看到排班时间重叠，所以没考虑。有时间重叠用这个select  
    员工编号,
    日期,
    OnStartTime1,
    OnENDTime1=case when OnStartTime1>OnENDTime1 then OnENDTime1+1 else OnENDTime1 end,
    OffStartTime1=case when OnStartTime1>OffStartTime1 then OffStartTime1+1 else OffStartTime1 end,
    OffENDTime1=case when OnENDTime1>OffENDTime1 then OffENDTime1+1 else OffENDTime1 end,
    OnStartTime2=case when OffStartTime1>OnStartTime2 then OnStartTime2+1 else OnStartTime2 end,
    OnENDTime2=case when OffENDTime1>OnENDTime2 then OnENDTime2+1 else OnENDTime2 end,
    OffStartTime2=case when OnStartTime2>OffStartTime2 then OffStartTime2+1 else OffStartTime2 end,
    OffENDTime2=case when OnENDTime2>OffENDTime2 then OffENDTime2+1 else OffENDTime2 end
from PBCMTEST_NEW 

Answer 3

辛苦大版了....麻烦继续use tempdb
go
set nocount on ;
if OBJECT_ID('PBCMTEST_NEW') is not null
    drop table PBCMTEST_NEW
go
--测试数据
CREATE  TABLE PBCMTEST_NEW
(
员工编号 nvarchar(20),
日期  datetime,
OnStartTime1 datetime, --1班上班开始打卡时间
OnDuty1 datetime, 
OnENDTime1 datetime, --1班上班结束打卡时间OffStartTime1 datetime, --1班下班开始打卡时间
OffDuty1 datetime, 
OffENDTime1 datetime,--1班下班结束打卡时间
 
OnStartTime2 datetime, --2班上班开始打卡时间
OnDuty2 datetime, 
OnENDTime2 datetime,--2班上班结束打卡时间OffStartTime2 datetime, --2班下班开始打卡时间
OffDuty2 datetime,
OffENDTime2 datetime--2班下班结束打卡时间
)insert into PBCMTEST_NEW select
 '007','2011/10/01',
'1900-01-01 23:40:00.000',
'1900-01-01 23:50:00.000',
'1900-01-01 03:00:00.000','1900-01-01 00:30:00.000',
'1900-01-01 07:30:00.000',
'1900-01-01 09:30:00.000',
'1900-01-01 12:00:00.000',
'1900-01-01 13:30:00.000',
'1900-01-01 15:30:00.000','1900-01-01 14:00:00.000',
'1900-01-01 19:30:00.000',
'1900-01-01 21:30:00.000'insert into PBCMTEST_NEW select
 '007','2011/10/02',
'1900-01-01 07:10:00.000',
'1900-01-01 07:20:00.000',
'1900-01-01 11:20:00.000','1900-01-01 08:00:00.000',
'1900-01-01 19:30:00.000',
'1900-01-01 23:00:00.000',
null,null,null,
null,null,nullgo
IF OBJECT_ID('DAT') IS NOT NULL
    DROP TABLE DAT
go
CREATE  TABLE DAT
(
员工编号 nvarchar(20),
日期  datetime,
时间1 datetime, 
时间2 datetime, 
时间3 datetime, 
时间4 datetime
)
insert into DAT select '007','2011/10/01','1900-01-01 07:17:00.000','1900-01-01 13:30:00.000','1900-01-01 19:16:00.000',null
insert into DAT select '007','2011/10/02','1900-01-01 07:30:00.000','1900-01-01 19:15:00.000',null,null----测试代码结束
;with b
as
(
 select 员工编号,TIMEs=日期+ TIMEs,TIMEs2=TIMEs  FROM DAT UNPIVOT(TIMEs FOR Cols IN([时间1],[时间2],[时间3],[时间4])) AS DAT2 
)
,a
as
(select  
    员工编号,
    日期,
    OnStartTime1,
    OnENDTime1=case when OnStartTime1>OnENDTime1 then OnENDTime1+1 else OnENDTime1 end,
    OffStartTime1=case when OnStartTime1>OffStartTime1 then OffStartTime1+1 else OffStartTime1 end,
    OffENDTime1=case when OnENDTime1>OffENDTime1 then OffENDTime1+1 else OffENDTime1 end,
    OnStartTime2=case when OffStartTime1>OnStartTime2 then OnStartTime2+1 else OnStartTime2 end,
    OnENDTime2=case when OffENDTime1>OnENDTime2 then OnENDTime2+1 else OnENDTime2 end,
    OffStartTime2=case when OnStartTime2>OffStartTime2 then OffStartTime2+1 else OffStartTime2 end,
    OffENDTime2=case when OnENDTime2>OffENDTime2 then OffENDTime2+1 else OffENDTime2 end
from PBCMTEST_NEW 
)
,C
as
(
SELECT 
    a.员工编号,
    a.日期,
    [一班上]=max(CASE WHEN b.TIMEs BETWEEN a.日期+a.OnStartTime1 AND  a.日期+case when a.OnStartTime1>a.OnENDTime1 then a.OnENDTime1+1 else a.OnENDTime1 end      THEN b.TIMEs2 END),
    [一班下]=max(CASE WHEN b.TIMEs BETWEEN a.日期+a.OffStartTime1 AND a.日期+case when a.OffStartTime1>a.OffENDTime1 then a.OffENDTime1+1 else a.OffENDTime1 end  THEN b.TIMEs2 END),                    
    [二班上]=max(Case WHEN b.TIMEs BETWEEN a.日期+a.OnStartTime2 AND  a.日期+case when a.OnStartTime2>a.OnENDTime2 then a.OnENDTime2+1 else a.OnENDTime2 end      THEN b.TIMEs2 END),    
    [二班下]=max(CASE WHEN b.TIMEs BETWEEN a.日期+a.OffStartTime2 AND a.日期+case when a.OffStartTime2>a.OffENDTime2 then a.OffENDTime2+1 else a.OffENDTime2 end  THEN b.TIMEs2 END)
FROM a
    inner  JOIN  b ON a.[员工编号]=b.[员工编号]
group by a.员工编号,a.日期
)
select * 
from C 
where coalesce([一班上],[一班下],[二班上],[二班下]) is not null
order by 日期,员工编号
/*
改一下测试数据.再麻烦看一下,一班下为什么不是'1900-01-01 07:17:00.000'怎么办成了1900-01-01 07:30:00.000 
这里应该是表达式C里面的有问题吧.
*/
员工编号                 日期                      一班上                     一班下                     二班上                     二班下
-------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
007                  2011-10-01 00:00:00.000 NULL                    1900-01-01 07:30:00.000 1900-01-01 13:30:00.000 1900-01-01 19:16:00.000
007                  2011-10-02 00:00:00.000 1900-01-01 07:30:00.000 1900-01-01 19:15:00.000 NULL                    NULL
警告: 聚合或其他 SET 操作消除了空值。*/

Answer 4

查看排班计划 
员工编号 日期 OnStartTime1
007 2011-10-01 00:00:00.000 1900-01-01 23:40:00.000第一个打卡记录是9月30日？

Answer 5

use tempdb
go
set nocount on ;
if OBJECT_ID('PBCMTEST_NEW') is not null
    drop table PBCMTEST_NEW
go
--测试数据
CREATE  TABLE PBCMTEST_NEW
(
员工编号 nvarchar(20),
日期  datetime,
OnStartTime1 datetime, --1班上班开始打卡时间
OnDuty1 datetime, 
OnENDTime1 datetime, --1班上班结束打卡时间OffStartTime1 datetime, --1班下班开始打卡时间
OffDuty1 datetime, 
OffENDTime1 datetime,--1班下班结束打卡时间
 
OnStartTime2 datetime, --2班上班开始打卡时间
OnDuty2 datetime, 
OnENDTime2 datetime,--2班上班结束打卡时间OffStartTime2 datetime, --2班下班开始打卡时间
OffDuty2 datetime,
OffENDTime2 datetime--2班下班结束打卡时间
)insert into PBCMTEST_NEW select
 '007','2011/10/01',
'1900-01-01 23:40:00.000',
'1900-01-01 23:50:00.000',
'1900-01-01 03:00:00.000','1900-01-01 00:30:00.000',
'1900-01-01 07:30:00.000',
'1900-01-01 09:30:00.000',
'1900-01-01 12:00:00.000',
'1900-01-01 13:30:00.000',
'1900-01-01 15:30:00.000','1900-01-01 14:00:00.000',
'1900-01-01 19:30:00.000',
'1900-01-01 21:30:00.000'insert into PBCMTEST_NEW select
 '007','2011/10/02',
'1900-01-01 07:10:00.000',
'1900-01-01 07:20:00.000',
'1900-01-01 11:20:00.000','1900-01-01 08:00:00.000',
'1900-01-01 19:30:00.000',
'1900-01-01 23:00:00.000',
null,null,null,
null,null,nullgo
IF OBJECT_ID('DAT') IS NOT NULL
    DROP TABLE DAT
go
CREATE  TABLE DAT
(
员工编号 nvarchar(20),
日期  datetime,
时间1 datetime, 
时间2 datetime, 
时间3 datetime, 
时间4 datetime
)
insert into DAT select '007','2011/10/01','1900-01-01 07:17:00.000','1900-01-01 13:30:00.000','1900-01-01 19:16:00.000',null
insert into DAT select '007','2011/10/02','1900-01-01 07:30:00.000','1900-01-01 19:15:00.000',null,null----测试代码结束
;with b
as
(
 select 员工编号,TIMEs=日期+ TIMEs,TIMEs2=TIMEs  FROM DAT UNPIVOT(TIMEs FOR Cols IN([时间1],[时间2],[时间3],[时间4])) AS DAT2 
)
,a
as
(  
select  
    员工编号,
    日期,
    OnStartTime1,
    OnENDTime1=case when OnStartTime1>OnENDTime1 then OnENDTime1+1 else OnENDTime1 end,
    OffStartTime1=case when OffStartTime1<OnStartTime1 then OffStartTime1+1 else OffStartTime1 end,
    OffENDTime1=case when  OffENDTime1<any(select OnStartTime1 union all  select OnENDTime1 ) then OffENDTime1+1 else OffENDTime1 end,
    OnStartTime2=case when OnStartTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 ) then OnStartTime2+1 else OnStartTime2 end,
    OnENDTime2=case when OnENDTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 union all select OffENDTime1 ) then OnENDTime2+1 else OnENDTime2 end,
    OffStartTime2=case when OffStartTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 union all select OffENDTime1 union all  select OnStartTime2 ) then OffStartTime2+1 else OffStartTime2 end,
    OffENDTime2=case when OffENDTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 union all select OffENDTime1 union all  select OnStartTime2 union all  select OnENDTime2  ) then OffENDTime2+1 else OffENDTime2 end
from (select [员工编号],[日期],[OnStartTime1]=[日期]+[OnStartTime1]+case when [OnStartTime1]>[OnDuty1] or [OnStartTime1]>[OnENDTime1] then -1 else 0 end,[OnENDTime1]=[日期]+[OnENDTime1],[OffStartTime1]=[日期]+[OffStartTime1],[OffDuty1]=[日期]+[OffDuty1],[OffENDTime1]=[日期]+[OffENDTime1],[OnStartTime2]=[日期]+[OnStartTime2],[OnENDTime2]=[日期]+[OnENDTime2],[OffStartTime2]=[日期]+[OffStartTime2],[OffENDTime2]=[日期]+[OffENDTime2] from PBCMTEST_NEW )x
)
,C
as
(
SELECT 
    a.员工编号,
    a.日期,
    [一班上]=max(CASE WHEN b.TIMEs BETWEEN a.OnStartTime1 AND   a.OnENDTime1    THEN b.TIMEs2 END),
    [一班下]=max(CASE WHEN b.TIMEs BETWEEN a.OffStartTime1 AND  a.OffENDTime1   THEN b.TIMEs2 END),                    
    [二班上]=max(Case WHEN b.TIMEs BETWEEN a.OnStartTime2 AND   a.OnENDTime2    THEN b.TIMEs2 END),    
    [二班下]=max(CASE WHEN b.TIMEs BETWEEN a.OffStartTime2 AND  a.OffENDTime2   THEN b.TIMEs2 END)
FROM a
    inner  JOIN  b ON a.[员工编号]=b.[员工编号]
group by a.员工编号,a.日期
)
select * 
from C 
where coalesce([一班上],[一班下],[二班上],[二班下]) is not null
order by 日期,员工编号/*
员工编号 日期 一班上 一班下 二班上 二班下
007 2011-10-01 00:00:00.000 NULL 1900-01-01 07:17:00.000 1900-01-01 13:30:00.000 1900-01-01 19:16:00.000
007 2011-10-02 00:00:00.000 1900-01-01 07:30:00.000 1900-01-01 19:15:00.000 NULL NULL
*/

Answer 6

OnStartTime1是OnDuty1的开始时间.所以他不是9月30日的,是10月01的
如果OnDuty1是23:40,OffDuty1是08:00 这样的跨天了.OnDuty1 是上班时间 OffDuty1 是下班时间

Answer 7

谢谢,大版
如果DAT表的列不确定的话,怎么处理呢?如有可能有十列.或更多列.这样的方法能行得通吗?

Answer 8

时间一行行排的，那样更简单，可参照
http://topic.csdn.net/u/20111021/09/c348365c-3d22-48b9-a47a-0a3108b00c95.html

Answer 9

这个朋友的,其实他不用写,可以直接用考勤机来做,标识更简单我的复杂些我的DAT的数据是在这段代码里面生存的##DAT表.---把刷卡记录转成员工一天打卡的次数
DECLARE @s NVARCHAR(max),@i NVARCHAR(max) --这个@i变量就是列.
SELECT TOP 1 @s='',@i=COUNT(*) FROM 
(select  Numbers, Card_ID ,CONVERT(varchar(100), SwipeDateTime, 111) 日期, CONVERT(varchar(100), SwipeDateTime, 108) 时间 from AttendanceTimeRecords
where SwipeDateTime between @startTime and @endTime
)subDat 
 GROUP BY Numbers,日期 ORDER BY COUNT(*) desc
IF OBJECT_ID('Tempdb..#')IS NOT NULL
    DROP TABLE #
    IF OBJECT_ID('Tempdb..##DAT')IS NOT NULL
    DROP TABLE ##DAT
SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM 
(select  Numbers, Card_ID ,cast(CONVERT(varchar(100), SwipeDateTime, 111) as datetime) 日期, cast(CONVERT(varchar(100), SwipeDateTime, 108)as datetime) 时间 from AttendanceTimeRecords
where SwipeDateTime between @startTime and @endTime
) AS a
 ORDER BY Numbers,日期,时间
WHILE @i>0
    SELECT @s=N',[时间'+@i+N']=max(case when row='+@i+N' then 时间 else null end)'+@s,@i=@i-1
EXEC('select * into ##DAT from (select Numbers AS 员工编号,日期'+@s+' from (SELECT *,row=(SELECT COUNT(*) FROM # WHERE Numbers=a.Numbers AND 日期=a.日期 AND ID<=a.ID) FROM # AS a)AS a group by Numbers,日期) as dat')
----end---

Answer 10

我有点不明白你的思路
 select 员工编号,TIMEs=日期+ TIMEs,TIMEs2=TIMEs  FROM DAT UNPIVOT(TIMEs FOR Cols IN([时间1],[时间2],[时间3],[时间4])) AS DAT2 这里我直接转行的吗,我不知道你这个里写成的列名,如果有时是8列,有时是4列.有时是十多列.所以我这里没法指定列名,in参数里面

Answer 11

create table AttendanceTimeRecords (
   ID                   int                  identity,
   Numbers              varchar(50)          null, --员工编号
   DeviceNumbers        int                  null,
   flag                 int                  null,
   SwipeDateTime        datetime             null,--刷卡记录
                    tinyint              null,
   Card_ID              nvarchar(16)         null,
   constraint PK_ATTENDANCETIMERECORDS primary key (ID)
)
SwipeDateTime 值是2009-09-25 09:14:00.000这种格式.
我用13楼给出的方法的日的是以员工编号及刷卡记录的日期与时间分开出来.也就是得知当天日期员工给了多少次记录.因为有些人一天会打很多次卡.报以那##DAT表的列可能会有很多列,以前和你讨论的DAT表只有4列,
 

Answer 12

with a
as
(     
select  
    员工编号,
    日期,
    OnStartTime1,
    OnENDTime1=case when OnStartTime1>OnENDTime1 then OnENDTime1+1 else OnENDTime1 end,
    OffStartTime1=case when OffStartTime1<OnStartTime1 then OffStartTime1+1 else OffStartTime1 end,
    OffENDTime1=case when  OffENDTime1<any(select OnStartTime1 union all  select OnENDTime1 ) then OffENDTime1+1 else OffENDTime1 end,
    OnStartTime2=case when OnStartTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 ) then OnStartTime2+1 else OnStartTime2 end,
    OnENDTime2=case when OnENDTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 union all select OffENDTime1 ) then OnENDTime2+1 else OnENDTime2 end,
    OffStartTime2=case when OffStartTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 union all select OffENDTime1 union all  select OnStartTime2 ) then OffStartTime2+1 else OffStartTime2 end,
    OffENDTime2=case when OffENDTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 union all select OffENDTime1 union all  select OnStartTime2 union all  select OnENDTime2  ) then OffENDTime2+1 else OffENDTime2 end
from (select [员工编号],[日期],[OnStartTime1]=[日期]+[OnStartTime1]+case when [OnStartTime1]>[OnDuty1] or [OnStartTime1]>[OnENDTime1] then -1 else 0 end,[OnENDTime1]=[日期]+[OnENDTime1],[OffStartTime1]=[日期]+[OffStartTime1],[OffDuty1]=[日期]+[OffDuty1],[OffENDTime1]=[日期]+[OffENDTime1],[OnStartTime2]=[日期]+[OnStartTime2],[OnENDTime2]=[日期]+[OnENDTime2],[OffStartTime2]=[日期]+[OffStartTime2],[OffENDTime2]=[日期]+[OffENDTime2] from PBCMTEST_NEW )x
),C
as
(
SELECT 
    a.员工编号,
    a.日期,
    [一班上]=min(CASE WHEN b.SwipeDateTime BETWEEN a.OnStartTime1 AND   a.OnENDTime1    THEN b.SwipeDateTime END),
    [一班下]=max(CASE WHEN b.SwipeDateTime BETWEEN a.OffStartTime1 AND  a.OffENDTime1   THEN b.SwipeDateTime END),                    
    [二班上]=min(Case WHEN b.SwipeDateTime BETWEEN a.OnStartTime2 AND   a.OnENDTime2    THEN b.SwipeDateTime END),    
    [二班下]=max(CASE WHEN b.SwipeDateTime BETWEEN a.OffStartTime2 AND  a.OffENDTime2   THEN b.SwipeDateTime END)
FROM a
    inner  JOIN   AttendanceTimeRecords as b ON a.[员工编号]=b.Numbers
where  
(b.SwipeDateTime BETWEEN a.OnStartTime1 AND   a.OnENDTime1 ) 
or(b.SwipeDateTime BETWEEN a.OffStartTime1 AND  a.OffENDTime1 )
or(b.SwipeDateTime BETWEEN a.OnStartTime2 AND   a.OnENDTime2)
or( b.SwipeDateTime BETWEEN a.OffStartTime2 AND  a.OffENDTime2)
group by a.员工编号,a.日期
)
select 
员工编号,
日期,
[一班上]=CONVERT(varchar(5),[一班上],8),
[一班下]=CONVERT(varchar(5),[一班下],8),
[二班上]=CONVERT(varchar(5),[二班上],8),
[二班下]=CONVERT(varchar(5),[二班下],8)
from C 
where coalesce([一班上],[一班下],[二班上],[二班下]) is not null
order by 日期,员工编号
你测测以上结果,是否正确

Answer 13

谢谢大版--这样的测试数据不对,
insert into PBCMTEST_NEW select
 '054','2011/10/08',
'1900-01-01 06:00:00.000',
'1900-01-01 08:00:00.000',
'1900-01-01 11:30:00.000','1900-01-01 08:30:00.000',
'1900-01-01 12:00:00.000',
'1900-01-01 13:00:00.000',
'1900-01-01 12:15:00.000',
'1900-01-01 13:30:00.000',
'1900-01-01 15:30:00.000','1900-01-01 14:00:00.000',
'1900-01-01 17:30:00.000',
'1900-01-01 22:00:00.000'insert into AttendanceTimeRecords(Numbers,SwipeDatetIME) Values('054','2011-10-08 07:56:00.000')
insert into AttendanceTimeRecords(Numbers,SwipeDatetIME) Values('054','2011-10-08 14:20:00.000')这样结果会多一个数据,二班上二班下是同一个时间数据

Answer 14

这样改改，再看看结果
with a
as
(     
select  
    员工编号,
    日期,
    OnStartTime1,
    OnENDTime1=case when OnStartTime1>OnENDTime1 then OnENDTime1+1 else OnENDTime1 end,
    OffStartTime1=case when OffStartTime1<OnStartTime1 then OffStartTime1+1 else OffStartTime1 end,
    OffENDTime1=case when  OffENDTime1<any(select OnStartTime1 union all  select OnENDTime1 ) then OffENDTime1+1 else OffENDTime1 end,
    OnStartTime2=case when OnStartTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 ) then OnStartTime2+1 else OnStartTime2 end,
    OnENDTime2=case when OnENDTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 union all select OffENDTime1 ) then OnENDTime2+1 else OnENDTime2 end,
    OffStartTime2=case when OffStartTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 union all select OffENDTime1 union all  select OnStartTime2 ) then OffStartTime2+1 else OffStartTime2 end,
    OffENDTime2=case when OffENDTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 union all select OffENDTime1 union all  select OnStartTime2 union all  select OnENDTime2  ) then OffENDTime2+1 else OffENDTime2 end
from (select [员工编号],[日期],[OnStartTime1]=[日期]+[OnStartTime1]+case when [OnStartTime1]>[OnDuty1] or [OnStartTime1]>[OnENDTime1] then -1 else 0 end,[OnENDTime1]=[日期]+[OnENDTime1],[OffStartTime1]=[日期]+[OffStartTime1],[OffDuty1]=[日期]+[OffDuty1],[OffENDTime1]=[日期]+[OffENDTime1],[OnStartTime2]=[日期]+[OnStartTime2],[OnENDTime2]=[日期]+[OnENDTime2],[OffStartTime2]=[日期]+[OffStartTime2],[OffENDTime2]=[日期]+[OffENDTime2] from PBCMTEST_NEW )x
),C
as
(
SELECT 
    a.员工编号,
    a.日期,
    [一班上]=min(CASE WHEN b.SwipeDateTime BETWEEN a.OnStartTime1 AND   a.OnENDTime1    THEN b.SwipeDateTime END),
    [一班下]=max(CASE WHEN b.SwipeDateTime BETWEEN a.OffStartTime1 AND  a.OffENDTime1   THEN b.SwipeDateTime END),                    
    [二班上]=min(Case WHEN b.SwipeDateTime BETWEEN a.OnStartTime2 AND   a.OnENDTime2    THEN b.SwipeDateTime END),    
    [二班下]=max(CASE WHEN b.SwipeDateTime BETWEEN a.OffStartTime2 AND  a.OffENDTime2   THEN b.SwipeDateTime END)
FROM a
    inner  JOIN   AttendanceTimeRecords as b ON a.[员工编号]=b.Numbers
where  
(b.SwipeDateTime BETWEEN a.OnStartTime1 AND   a.OnENDTime1 ) 
or(b.SwipeDateTime BETWEEN a.OffStartTime1 AND  a.OffENDTime1 )
or(b.SwipeDateTime BETWEEN a.OnStartTime2 AND   a.OnENDTime2)
or( b.SwipeDateTime BETWEEN a.OffStartTime2 AND  a.OffENDTime2)
group by a.员工编号,a.日期
)
select 
员工编号,
日期,
[一班上]=CONVERT(varchar(5),[一班上],8),
[一班下]=case when [一班下]>[一班上] then CONVERT(varchar(5),[一班下],8) else '' end,
[二班上]=case when [二班上]>[一班下] then CONVERT(varchar(5),[二班上],8) else '' end,
[二班下]=case when [二班下]>[二班上] then CONVERT(varchar(5),[二班下],8) else '' end
from C 
order by 日期,员工编号

Answer 15

/*
员工编号    日期    一班上    一班下    二班上    二班下
054    2011-10-08 00:00:00.000  1900-01-01  07:56:00.000  NULL  1900-01-01 14:20:00.000    1900-01-01 14:20:00.000*/结果还是这样..应该是没有二班下的时间才对.因为打卡记录只有两次,这里确有三次.
其它都没问题了.

Answer 16

use tempdb
go
with a
as
(     
select  
    员工编号,
    日期,
    OnStartTime1,
    OnENDTime1=case when OnStartTime1>OnENDTime1 then OnENDTime1+1 else OnENDTime1 end,
    OffStartTime1=case when OffStartTime1<OnStartTime1 then OffStartTime1+1 else OffStartTime1 end,
    OffENDTime1=case when  OffENDTime1<any(select OnStartTime1 union all  select OnENDTime1 ) then OffENDTime1+1 else OffENDTime1 end,
    OnStartTime2=case when OnStartTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 ) then OnStartTime2+1 else OnStartTime2 end,
    OnENDTime2=case when OnENDTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 union all select OffENDTime1 ) then OnENDTime2+1 else OnENDTime2 end,
    OffStartTime2=case when OffStartTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 union all select OffENDTime1 union all  select OnStartTime2 ) then OffStartTime2+1 else OffStartTime2 end,
    OffENDTime2=case when OffENDTime2<any(select OnStartTime1 union all  select OnENDTime1 union all  select OffStartTime1 union all select OffENDTime1 union all  select OnStartTime2 union all  select OnENDTime2  ) then OffENDTime2+1 else OffENDTime2 end
from (select [员工编号],[日期],[OnStartTime1]=[日期]+[OnStartTime1]+case when [OnStartTime1]>[OnDuty1] or [OnStartTime1]>[OnENDTime1] then -1 else 0 end,[OnENDTime1]=[日期]+[OnENDTime1],[OffStartTime1]=[日期]+[OffStartTime1],[OffDuty1]=[日期]+[OffDuty1],[OffENDTime1]=[日期]+[OffENDTime1],[OnStartTime2]=[日期]+[OnStartTime2],[OnENDTime2]=[日期]+[OnENDTime2],[OffStartTime2]=[日期]+[OffStartTime2],[OffENDTime2]=[日期]+[OffENDTime2] from PBCMTEST_NEW )x
),C
as
(
SELECT 
    a.员工编号,
    a.日期,
    [一班上]=min(CASE WHEN b.SwipeDateTime BETWEEN a.OnStartTime1 AND   a.OnENDTime1    THEN b.SwipeDateTime END),
    [一班下]=max(CASE WHEN b.SwipeDateTime BETWEEN a.OffStartTime1 AND  a.OffENDTime1   THEN b.SwipeDateTime END),                    
    [二班上]=min(Case WHEN b.SwipeDateTime BETWEEN a.OnStartTime2 AND   a.OnENDTime2    THEN b.SwipeDateTime END),    
    [二班下]=max(CASE WHEN b.SwipeDateTime BETWEEN a.OffStartTime2 AND  a.OffENDTime2   THEN b.SwipeDateTime END)
FROM a
    inner  JOIN   AttendanceTimeRecords as b ON a.[员工编号]=b.Numbers
where  
(b.SwipeDateTime BETWEEN a.OnStartTime1 AND   a.OnENDTime1 ) 
or(b.SwipeDateTime BETWEEN a.OffStartTime1 AND  a.OffENDTime1 )
or(b.SwipeDateTime BETWEEN a.OnStartTime2 AND   a.OnENDTime2)
or( b.SwipeDateTime BETWEEN a.OffStartTime2 AND  a.OffENDTime2)
group by a.员工编号,a.日期
)
select 
员工编号,
日期,
[一班上]=CONVERT(varchar(5),[一班上],8),
[一班下]=case when [一班下]>isnull([一班上],'') then CONVERT(varchar(5),[一班下],8) else '' end,
[二班上]=case when [二班上]>isnull([一班下],'') then CONVERT(varchar(5),[二班上],8) else '' end,
[二班下]=case when [二班下]>isnull([二班上],'') then CONVERT(varchar(5),[二班下],8) else '' end
from C 
order by 日期,员工编号/*
员工编号 日期 一班上 一班下 二班上 二班下
054 2011-10-08 00:00:00.000 07:56 14:20
*/