--生成测试数据 CREATE TABLE TF_KQ(YG_NO varchar(20),BAN_NO varchar(4),TRS_DD datetime) INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 07:23:00.000' INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 07:32:00.000' INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 11:31:00.000' INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 12:04:00.000' INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 12:29:00.000' INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 13:45:00.000' INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 13:51:00.000' INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 14:12:00.000' INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 17:33:00.000' INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 17:45:00.000' INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 18:15:00.000' INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 19:22:00.000' INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 19:25:00.000' INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 20:22:00.000' INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 02:25:00.000'create table tf_ban( BAN_NO varchar(10), RNG_ITM int, STR_TIME1 varchar(10), STR_TIME2 varchar(10), STR_TIME3 varchar(10), STR_TIME4 varchar(10), STR_TIME5 varchar(10), STR_TIME6 varchar(10), STR_TIME7 varchar(10), RNG_NAME varchar(10), IO_ID int, OVER_MIN int, KQ_ID varchar(10), CARD_ID varchar(10), VLD_MIN int) insert into tf_ban select '01',1,'08:00','08:00','08:00','08:00','08:00','08:00','08:00','上班入',1,2,'T','T',30 insert into tf_ban select '01',2,'12:00','12:00','12:00','12:00','12:00','12:00','12:00','用餐出',2,2,'T','T',30 insert into tf_ban select '01',3,'14:00','14:00','14:00','14:00','14:00','14:00','14:00','用餐進',1,2,'T','T',30 insert into tf_ban select '01',4,'18:00','18:00','18:00','18:00','18:00','18:00','18:00','下班出',2,2,'T','T',30 insert into tf_ban select '01',5,'19:00','19:00','19:00','19:00','19:00','19:00','19:00','加班入',3,2,'T','T',30 insert into tf_ban select '01',6,'06:00','06:00','06:00','06:00','06:00','06:00','06:00','加班出',4,2,'T','T',30 insert into tf_ban select '02',1,'07:30','07:30','07:30','07:30','07:30','07:30','07:30','上班 ',1,2,'T','T',30 insert into tf_ban select '02',2,'11:30','11:30','11:30','11:30','11:30','11:30','11:30','用餐出',2,2,'T','T',30 insert into tf_ban select '02',3,'13:30','13:30','13:30','13:30','13:30','13:30','13:30','用餐入',1,2,'T','T',30 insert into tf_ban select '02',4,'17:30','17:30','17:30','17:30','17:30','17:30','17:30','下班 ',2,2,'T','T',30 insert into tf_ban select '02',5,'18:30','18:30','18:30','18:30','18:30','18:30','18:30','加班入',3,2,'T','T',30 insert into tf_ban select '02',6,'07:00','07:00','07:00','07:00','07:00','07:00','07:00','加班出',4,2,'T','T',30 GO --创建删除冗余数据的存储过程 create procedure sp_deletedata() AS begin select BAN_NO, RNG_ITM, DW = 1, STR_TIME1 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi,-OVER_MIN,cast(STR_TIME1 as datetime)) else dateadd(mi,-VLD_MIN ,cast(STR_TIME1 as datetime)) end),108), STR_TIME2 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi, VLD_MIN ,cast(STR_TIME1 as datetime)) else dateadd(mi,OVER_MIN,cast(STR_TIME1 as datetime)) end),108) into #T from tf_ban union all select BAN_NO, RNG_ITM, DW = 2, STR_TIME1 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi,-OVER_MIN,cast(STR_TIME2 as datetime)) else dateadd(mi,-VLD_MIN ,cast(STR_TIME2 as datetime)) end),108), STR_TIME2 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi, VLD_MIN ,cast(STR_TIME2 as datetime)) else dateadd(mi,OVER_MIN,cast(STR_TIME2 as datetime)) end),108) from tf_ban union all select BAN_NO, RNG_ITM, DW = 3, STR_TIME1 = convert(char(5),(case IO_ID%2 when 1 then dateadd(mi,-OVER_MIN,cast(STR_TIME3 as datetime)) else dateadd(mi,-VLD_MIN ,cast(STR_TIME3 as datetime)) end),108), STR_TIME2 = convert(char(5),(case IO_ID%2 when 1 then dateadd(mi, VLD_MIN ,cast(STR_TIME3 as datetime)) else dateadd(mi,OVER_MIN,cast(STR_TIME3 as datetime)) end),108) from tf_ban union all select BAN_NO, RNG_ITM, DW = 4, STR_TIME1 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi,-OVER_MIN,cast(STR_TIME4 as datetime)) else dateadd(mi,VLD_MIN ,cast(STR_TIME4 as datetime)) end),108), STR_TIME2 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi, VLD_MIN ,cast(STR_TIME4 as datetime)) else dateadd(mi,OVER_MIN,cast(STR_TIME4 as datetime)) end),108) from tf_ban union all select BAN_NO, RNG_ITM, DW = 5, STR_TIME1 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi,-OVER_MIN,cast(STR_TIME5 as datetime)) else dateadd(mi,-VLD_MIN ,cast(STR_TIME5 as datetime)) end),108), STR_TIME2 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi, VLD_MIN ,cast(STR_TIME5 as datetime)) else dateadd(mi,OVER_MIN,cast(STR_TIME5 as datetime)) end),108) from tf_ban union all select BAN_NO, RNG_ITM, DW = 6, STR_TIME1 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi,-OVER_MIN,cast(STR_TIME6 as datetime)) else dateadd(mi,-VLD_MIN ,cast(STR_TIME6 as datetime)) end),108), STR_TIME2 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi, VLD_MIN ,cast(STR_TIME6 as datetime)) else dateadd(mi,OVER_MIN,cast(STR_TIME6 as datetime)) end),108) from tf_ban union all select BAN_NO, RNG_ITM, DW = 7, STR_TIME1 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi,-OVER_MIN,cast(STR_TIME7 as datetime)) else dateadd(mi,-VLD_MIN ,cast(STR_TIME7 as datetime)) end),108), STR_TIME2 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi, VLD_MIN ,cast(STR_TIME7 as datetime)) else dateadd(mi,OVER_MIN,cast(STR_TIME7 as datetime)) end),108) from tf_ban
--删除正常打卡时间范围外的数据 delete a from TF_KQ a where not exists(select 1 from #T where BAN_NO=a.BAN_NO and datepart(dw,a.TRS_DD)=DW and convert(char(5),a.TRS_DD,108) between STR_TIME1 and STR_TIME2)
--删除正常打卡时间范围内非最后一次的数据 delete a from TF_KQ a, #T b where a.BAN_NO = b.BAN_NO and datepart(dw,a.TRS_DD) = b.DW and convert(char(5),a.TRS_DD,108) between b.STR_TIME1 and b.STR_TIME2 and exists(select 1 from TF_KQ c where a.BAN_NO = c.BAN_NO and datediff(dd,a.TRS_DD,c.TRS_DD) = 0 and a.TRS_DD < c.TRS_DD and convert(char(5),c.TRS_DD,108) between b.STR_TIME1 and b.STR_TIME2)END GO--执行存储过程 EXEC sp_deletedata GO --查看执行结果 select * from TF_KQ /* A002 01 2005-10-08 07:32:00.000 A002 01 2005-10-08 12:29:00.000 A002 01 2005-10-08 13:51:00.000 A002 01 2005-10-08 18:15:00.000 */--删除测试环境 drop procedure sp_deletedata drop table TF_KQ,tf_ban
以上的刚才看了把最后一个有"加班出"打卡的也都删掉了;如上面的设定的排班从第一天加班18:30到第二天的7:00点钟打卡有效: 02 5 18:30 18:30 18:30 18:30 18:30 18:30 18:30 加班入 3 2 T T 30 02 6 07:00 07:00 07:00 07:00 07:00 07:00 07:00 加班出 4 2 T T 30上面的语句是把18:00加班入的考虑进去了;但是把从18:31至下一天的07:30 加班出的都删掉了,在这之内的只取把最后一次打卡的作为加班出的资料,其它都删掉;请再帮我改一下,谢谢了!
CREATE TABLE TF_KQ(YG_NO varchar(20),BAN_NO varchar(4),TRS_DD datetime)
INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 07:23:00.000'
INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 07:32:00.000'
INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 11:31:00.000'
INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 12:04:00.000'
INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 12:29:00.000'
INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 13:45:00.000'
INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 13:51:00.000'
INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 14:12:00.000'
INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 17:33:00.000'
INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 17:45:00.000'
INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 18:15:00.000'
INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 19:22:00.000'
INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 19:25:00.000'
INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 20:22:00.000'
INSERT INTO TF_KQ SELECT 'A002','01','2005-10-08 02:25:00.000'create table tf_ban(
BAN_NO varchar(10),
RNG_ITM int,
STR_TIME1 varchar(10),
STR_TIME2 varchar(10),
STR_TIME3 varchar(10),
STR_TIME4 varchar(10),
STR_TIME5 varchar(10),
STR_TIME6 varchar(10),
STR_TIME7 varchar(10),
RNG_NAME varchar(10),
IO_ID int,
OVER_MIN int,
KQ_ID varchar(10),
CARD_ID varchar(10),
VLD_MIN int)
insert into tf_ban select '01',1,'08:00','08:00','08:00','08:00','08:00','08:00','08:00','上班入',1,2,'T','T',30
insert into tf_ban select '01',2,'12:00','12:00','12:00','12:00','12:00','12:00','12:00','用餐出',2,2,'T','T',30
insert into tf_ban select '01',3,'14:00','14:00','14:00','14:00','14:00','14:00','14:00','用餐進',1,2,'T','T',30
insert into tf_ban select '01',4,'18:00','18:00','18:00','18:00','18:00','18:00','18:00','下班出',2,2,'T','T',30
insert into tf_ban select '01',5,'19:00','19:00','19:00','19:00','19:00','19:00','19:00','加班入',3,2,'T','T',30
insert into tf_ban select '01',6,'06:00','06:00','06:00','06:00','06:00','06:00','06:00','加班出',4,2,'T','T',30
insert into tf_ban select '02',1,'07:30','07:30','07:30','07:30','07:30','07:30','07:30','上班 ',1,2,'T','T',30
insert into tf_ban select '02',2,'11:30','11:30','11:30','11:30','11:30','11:30','11:30','用餐出',2,2,'T','T',30
insert into tf_ban select '02',3,'13:30','13:30','13:30','13:30','13:30','13:30','13:30','用餐入',1,2,'T','T',30
insert into tf_ban select '02',4,'17:30','17:30','17:30','17:30','17:30','17:30','17:30','下班 ',2,2,'T','T',30
insert into tf_ban select '02',5,'18:30','18:30','18:30','18:30','18:30','18:30','18:30','加班入',3,2,'T','T',30
insert into tf_ban select '02',6,'07:00','07:00','07:00','07:00','07:00','07:00','07:00','加班出',4,2,'T','T',30
GO
--创建删除冗余数据的存储过程
create procedure sp_deletedata()
AS
begin
select
BAN_NO,
RNG_ITM,
DW = 1,
STR_TIME1 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi,-OVER_MIN,cast(STR_TIME1 as datetime)) else dateadd(mi,-VLD_MIN ,cast(STR_TIME1 as datetime)) end),108),
STR_TIME2 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi, VLD_MIN ,cast(STR_TIME1 as datetime)) else dateadd(mi,OVER_MIN,cast(STR_TIME1 as datetime)) end),108)
into #T
from
tf_ban
union all
select
BAN_NO,
RNG_ITM,
DW = 2,
STR_TIME1 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi,-OVER_MIN,cast(STR_TIME2 as datetime)) else dateadd(mi,-VLD_MIN ,cast(STR_TIME2 as datetime)) end),108),
STR_TIME2 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi, VLD_MIN ,cast(STR_TIME2 as datetime)) else dateadd(mi,OVER_MIN,cast(STR_TIME2 as datetime)) end),108)
from
tf_ban
union all
select
BAN_NO,
RNG_ITM,
DW = 3,
STR_TIME1 = convert(char(5),(case IO_ID%2 when 1 then dateadd(mi,-OVER_MIN,cast(STR_TIME3 as datetime)) else dateadd(mi,-VLD_MIN ,cast(STR_TIME3 as datetime)) end),108),
STR_TIME2 = convert(char(5),(case IO_ID%2 when 1 then dateadd(mi, VLD_MIN ,cast(STR_TIME3 as datetime)) else dateadd(mi,OVER_MIN,cast(STR_TIME3 as datetime)) end),108)
from
tf_ban
union all
select
BAN_NO,
RNG_ITM,
DW = 4,
STR_TIME1 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi,-OVER_MIN,cast(STR_TIME4 as datetime)) else dateadd(mi,VLD_MIN ,cast(STR_TIME4 as datetime)) end),108),
STR_TIME2 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi, VLD_MIN ,cast(STR_TIME4 as datetime)) else dateadd(mi,OVER_MIN,cast(STR_TIME4 as datetime)) end),108)
from
tf_ban
union all
select
BAN_NO,
RNG_ITM,
DW = 5,
STR_TIME1 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi,-OVER_MIN,cast(STR_TIME5 as datetime)) else dateadd(mi,-VLD_MIN ,cast(STR_TIME5 as datetime)) end),108),
STR_TIME2 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi, VLD_MIN ,cast(STR_TIME5 as datetime)) else dateadd(mi,OVER_MIN,cast(STR_TIME5 as datetime)) end),108)
from
tf_ban
union all
select
BAN_NO,
RNG_ITM,
DW = 6,
STR_TIME1 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi,-OVER_MIN,cast(STR_TIME6 as datetime)) else dateadd(mi,-VLD_MIN ,cast(STR_TIME6 as datetime)) end),108),
STR_TIME2 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi, VLD_MIN ,cast(STR_TIME6 as datetime)) else dateadd(mi,OVER_MIN,cast(STR_TIME6 as datetime)) end),108)
from
tf_ban
union all
select
BAN_NO,
RNG_ITM,
DW = 7,
STR_TIME1 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi,-OVER_MIN,cast(STR_TIME7 as datetime)) else dateadd(mi,-VLD_MIN ,cast(STR_TIME7 as datetime)) end),108),
STR_TIME2 = convert(char(5),(case IO_ID%2 when 0 then dateadd(mi, VLD_MIN ,cast(STR_TIME7 as datetime)) else dateadd(mi,OVER_MIN,cast(STR_TIME7 as datetime)) end),108)
from
tf_ban
--删除正常打卡时间范围外的数据
delete a
from
TF_KQ a
where
not exists(select
1
from
#T
where
BAN_NO=a.BAN_NO
and
datepart(dw,a.TRS_DD)=DW
and
convert(char(5),a.TRS_DD,108) between STR_TIME1 and STR_TIME2)
--删除正常打卡时间范围内非最后一次的数据
delete a
from
TF_KQ a,
#T b
where
a.BAN_NO = b.BAN_NO
and
datepart(dw,a.TRS_DD) = b.DW
and
convert(char(5),a.TRS_DD,108) between b.STR_TIME1 and b.STR_TIME2
and
exists(select
1
from
TF_KQ c
where
a.BAN_NO = c.BAN_NO
and
datediff(dd,a.TRS_DD,c.TRS_DD) = 0
and
a.TRS_DD < c.TRS_DD
and
convert(char(5),c.TRS_DD,108) between b.STR_TIME1 and b.STR_TIME2)END
GO--执行存储过程
EXEC sp_deletedata
GO
--查看执行结果
select * from TF_KQ
/*
A002 01 2005-10-08 07:32:00.000
A002 01 2005-10-08 12:29:00.000
A002 01 2005-10-08 13:51:00.000
A002 01 2005-10-08 18:15:00.000
*/--删除测试环境
drop procedure sp_deletedata
drop table TF_KQ,tf_ban
02 5 18:30 18:30 18:30 18:30 18:30 18:30 18:30 加班入 3 2 T T 30
02 6 07:00 07:00 07:00 07:00 07:00 07:00 07:00 加班出 4 2 T T 30上面的语句是把18:00加班入的考虑进去了;但是把从18:31至下一天的07:30 加班出的都删掉了,在这之内的只取把最后一次打卡的作为加班出的资料,其它都删掉;请再帮我改一下,谢谢了!
17002114 01 2005-08-01 08:00:39.000
17002114 01 2005-08-01 08:01:15.000
17002114 01 2005-08-01 08:40:10.000
17002114 01 2005-08-01 08:41:01.000
17002114 01 2005-08-01 09:00:28.000
17002114 01 2005-08-01 12:07:40.000
17002114 01 2005-08-01 13:59:34.000
17002114 01 2005-08-01 14:00:11.000
17002114 01 2005-08-01 18:04:54.000
17002114 01 2005-08-01 19:01:52.000
17002114 01 2005-08-01 19:02:24.000
17002114 01 2005-08-01 20:37:22.000
17002114 01 2005-08-01 21:03:59.000
执行上面的语句后:
17002114 01 2005-08-01 08:01:15.000
17002114 01 2005-08-01 12:07:40.000
17002114 01 2005-08-01 14:00:11.000
17002114 01 2005-08-01 18:04:54.000
17002114 01 2005-08-01 19:02:24.000 这樣把把TF_KQ里原有的
17002114 01 2005-08-01 20:37:22.000
17002114 01 2005-08-01 21:03:59.000
加班出的都刪掉了
而没有取
把从18:31至下一天的07:30 加班出的都删掉了,在这之内的只取把最后一次打卡的作为加班出的资料,其它都删掉;
应是这样的,加班入是18:30
加班出在班別里設定是第二天的07:00加班出取18:30+VLD_MIN=17:00到tf_ban里设定的加班出07:00的时间范围的最后一次打卡;
02 5 18:30 18:30 18:30 18:30 18:30 18:30 18:30 加班入 3 2 T T 30
02 6 07:00 07:00 07:00 07:00 07:00 07:00 07:00 加班出 4 2 T T 30}
的从第一天18:30到第二天07:00的范围