考勤夜班卡问题
原始数据 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的数据就是需要的结果
求解答


[解决办法]

SQL code

create table a([No] varchar(10),Date varchar(10),[Time] varchar(10))insert into aselect '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 ) bon 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    夜班*/