原始数据 Table1
No Date Time
001 20111230 19:15
001 20111231 03:15
生成表 Table2
No Date WorkS(上班) WorkE(下班) 班次
001 20111230 19:15 03:15 夜班通过No和Date把Table1的Time更新到Table2
如果班次是夜班 上班时间是当天符合条件(假设上班打卡是19:00-19:25)的最小Time
下班时间是次日符合条件(假设下班打卡是03:00-03:25)的最小Time
Table2的数据就是需要的结果
求解答
No Date Time
001 20111230 19:15
001 20111231 03:15
生成表 Table2
No Date WorkS(上班) WorkE(下班) 班次
001 20111230 19:15 03:15 夜班通过No和Date把Table1的Time更新到Table2
如果班次是夜班 上班时间是当天符合条件(假设上班打卡是19:00-19:25)的最小Time
下班时间是次日符合条件(假设下班打卡是03:00-03:25)的最小Time
Table2的数据就是需要的结果
求解答
where a.no = b.no and a.date < b.date
create table a([No] varchar(10),Date varchar(10),[Time] varchar(10))insert into a
select '001', '20111230', '19:15'
union all select
'001' ,'20111231', '03:15'
union all select
'001', '20111231', '03:16'
union all select
'001', '20111229' ,'19:16'
union all select
'001' ,'20111230', '03:10'select * from aselect isnull(a.No,b.No) No,isnull(a.Date,B.Date) Date,isnull(a.Time,'') WorkS,isnull(b.Time,'') WorkE,'夜班' 班次
from
(select No,Date,min(Time) Time from a where Time between '19:00' and '19:25' group by No,Date ) a full join
(select No,Date,min(Time) Time from a where Time between '03:00' and '03:25' group by No,Date ) b
on a.No=b.No and a.Date=dateadd(d,-1,b.Date)
--班次应该有一个排班表吧,这样的话就在上面的语句上加一个条件
--where exists(select 1 from 排班表 where 班次='夜班' and ( No=a.No or No=b.No )/*
001 20111229 19:16 03:10 夜班
001 20111230 19:15 03:15 夜班
*/
select '001', '20111230', '19:15'
union all select
'001' ,'20111231', '03:15'
union all select
'001', '20111231', '03:16'
union all select
'001', '20111229' ,'19:16'
union all select
'001' ,'20111230', '03:10'select * from aselect isnull(a.No,b.No) No,isnull(a.Date,B.Date) Date,isnull(a.Time,'') WorkS,isnull(b.Time,'') WorkE,'夜班' 班次
from
(select No,Date,min(Time) Time from a where Time between '19:00' and '19:25' group by No,Date ) a full join
(select No,Date,min(Time) Time from a where Time between '03:00' and '03:25' group by No,Date ) b
on a.No=b.No and a.Date=dateadd(d,-1,b.Date)
居然删我回复,不就是忘了贴代码么
=========================================================================================