27,579
社区成员
发帖
与我相关
我的任务
分享
create table Bc_info(id int,Bc_info_Name varchar(5),Bc_info_Begin datetime,Bc_info_End datetime,Bc_info_early datetime,Bc_info_last datetime,Bc_info_KZore varchar(1))
insert into Bc_info
select 1,'早班','00:30','08:30','23:00','10:00','T' union all
select 2,'中班','08:30','17:30','07:00','18:00','F' union all
select 3,'晚班','16:30','00:30','15:00','02:00','T' union all
select 4,'夜班','20:30','08:30','19:00','10:00','T' union all
select 5,'长班1','16:30','07:30','15:30','10:30','T' union all
select 6,'长班2','08:30','08:30','07:30','10:00','T'
select * from Bc_info
go
create table PB_info (id int,Person_id int,Date1 varchar(4),date2 varchar(5),date3 varchar(4),date4 varchar(4),Date31 varchar(4),Month int)
insert into PB_info
select 1,101,'早班','早班','晚班','中班','中班',5 union all
select 2,102,'早班','晚班','晚班','中班','中班',5 union all
select 3,103,'早班','长班1','休息','中班','中班',5
select * from PB_info
go
create table SK_Record (SK_Record_id int,Person_id int,SK_Record_Data datetime,SK_Record_Time int,KQ_matchinge sql_variant)
insert into SK_Record
select 1,101,'2009-5-1 23:30',1,null union all
select 2,101,'2009-5-2 00:20',2,null union all
select 3,101,'2009-5-3 16:10',null,null union all
select 4,101,'2009-5-4 07:30',null,null union all
select 5,102,'2009-5-1 00:10',null,null union all
select 6,102,'2009-5-2 16:35',null,null union all
select 7,102,'2009-5-3 16:10',null,null union all
select 8,102,'2009-5-4 06:30',null,null union all
select 9,102,'2009-5-4 06:31',null,null union all
select 9,102,'2009-5-4 06:31',null,null union all
select 10,101,'2009-5-1 00:20',2,null union all
select 11,101,'2009-5-1 00:20',3,null union all
select 12,101,'2009-5-1 08:20',4,null union all
select 12,101,'2009-5-1 08:30',4,null union all
select 12,101,'2009-5-1 08:31',4,null union all
select 12,101,'2009-5-2 10:00',4,null union all
select 12,101,'2009-5-2 01:35',4,null
select * from SK_Record
--用绝对值取最近的时间?
abs(datediff(mi,时间,getdate()))