27,579
社区成员
发帖
与我相关
我的任务
分享
create table employee(id int,ICcardID varchar(10),name nvarchar(10))
insert into employee select 1,'001','aaa'
insert into employee select 2,'002','bbb'
create table KQ_RECORD(id int identity(1,1),ICcardID varchar(10),time datetime)
insert into KQ_RECORD select '001','2010-07-01 08:56'
insert into KQ_RECORD select '001','2010-07-01 18:02'
insert into KQ_RECORD select '001','2010-07-02 08:58' --3号是周六
go
create procedure sp_KQ_RECORD
(@yymm varchar(7)) --xxxx-xx 年-月
as
begin
insert into KQ_RECORD(ICcardID,time)
select a.ICcardID,CONVERT(varchar(100), a.time, 23)+' 18:30:00' from KQ_RECORD a
where convert(varchar(7),time,120)=@yymm
and not exists
(select 1 from KQ_RECORD b
where a.ICcardID=b.ICcardID
and CONVERT(varchar(100), a.time, 23)=CONVERT(varchar(100), b.time, 23)
and CONVERT(varchar(100),b.time, 8) between '17:30:00' and '18:30:00')
end
go
exec sp_KQ_RECORD '2010-07'
select * from KQ_RECORD
/*
id ICcardID time
----------- ---------- -----------------------
1 001 2010-07-01 08:56:00.000
2 001 2010-07-01 18:02:00.000
3 001 2010-07-02 08:58:00.000
4 001 2010-07-02 18:30:00.000
(4 行受影响)
*/
create table employee(id int,ICcardID varchar(10),name nvarchar(10))
insert into employee select 1,'001','aaa'
insert into employee select 2,'002','bbb'
create table KQ_RECORD(id int identity(1,1),ICcardID varchar(10),time datetime)
insert into KQ_RECORD select '001','2010-07-01 08:56'
insert into KQ_RECORD select '001','2010-07-01 18:02'
insert into KQ_RECORD select '001','2010-07-02 08:58' --3号是周六
--create table KQ_RECORDSING(id int,ICcardID varchar(10),time datetime)
go
create procedure insertrecord
(@dt varchar(7)) --xxxx-xx 年-月
as
begin
insert into KQ_RECORD
select a.ICcardID,dateadd(d,number,@dt+'-01 09:00:00')
from employee a,master..spt_values b
where b.type='p' and datepart(dw,dateadd(d,number,@dt+'-01')) between 2 and 6
and dateadd(d,number,@dt+'-01')<dateadd(m,1,@dt+'-01')
and not exists(select 1 from KQ_RECORD where ICcardID=a.ICcardID and time between dateadd(d,number,@dt+'-01 08:30:00') and dateadd(d,number,@dt+'-01 09:30:00'))
union all
select a.ICcardID,dateadd(d,number,@dt+'-01 18:00:00')
from employee a,master..spt_values b
where b.type='p' and datepart(dw,dateadd(d,number,@dt+'-01')) between 2 and 6
and dateadd(d,number,@dt+'-01')<dateadd(m,1,@dt+'-01')
and not exists(select 1 from KQ_RECORD where ICcardID=a.ICcardID and time between dateadd(d,number,@dt+'-01 17:30:00') and dateadd(d,number,@dt+'-01 18:30:00'))
order by 2,iccardid
end
go
exec insertrecord '2010-07'
select * from KQ_RECORD
/*
id ICcardID time
----------- ---------- -----------------------
1 001 2010-07-01 08:56:00.000
2 001 2010-07-01 18:02:00.000
3 001 2010-07-02 08:58:00.000
4 002 2010-07-01 09:00:00.000
5 002 2010-07-01 18:00:00.000
6 002 2010-07-02 09:00:00.000
7 001 2010-07-02 18:00:00.000
8 002 2010-07-02 18:00:00.000
9 001 2010-07-05 09:00:00.000
10 002 2010-07-05 09:00:00.000
11 001 2010-07-05 18:00:00.000
12 002 2010-07-05 18:00:00.000
13 001 2010-07-06 09:00:00.000
14 002 2010-07-06 09:00:00.000
15 001 2010-07-06 18:00:00.000
16 002 2010-07-06 18:00:00.000
17 001 2010-07-07 09:00:00.000
18 002 2010-07-07 09:00:00.000
19 001 2010-07-07 18:00:00.000
20 002 2010-07-07 18:00:00.000
21 001 2010-07-08 09:00:00.000
22 002 2010-07-08 09:00:00.000
23 001 2010-07-08 18:00:00.000
24 002 2010-07-08 18:00:00.000
25 001 2010-07-09 09:00:00.000
26 002 2010-07-09 09:00:00.000
27 001 2010-07-09 18:00:00.000
28 002 2010-07-09 18:00:00.000
29 001 2010-07-12 09:00:00.000
30 002 2010-07-12 09:00:00.000
31 001 2010-07-12 18:00:00.000
32 002 2010-07-12 18:00:00.000
33 001 2010-07-13 09:00:00.000
34 002 2010-07-13 09:00:00.000
35 001 2010-07-13 18:00:00.000
36 002 2010-07-13 18:00:00.000
37 001 2010-07-14 09:00:00.000
38 002 2010-07-14 09:00:00.000
39 001 2010-07-14 18:00:00.000
40 002 2010-07-14 18:00:00.000
41 001 2010-07-15 09:00:00.000
42 002 2010-07-15 09:00:00.000
43 001 2010-07-15 18:00:00.000
44 002 2010-07-15 18:00:00.000
45 001 2010-07-16 09:00:00.000
46 002 2010-07-16 09:00:00.000
47 001 2010-07-16 18:00:00.000
48 002 2010-07-16 18:00:00.000
49 001 2010-07-19 09:00:00.000
50 002 2010-07-19 09:00:00.000
51 001 2010-07-19 18:00:00.000
52 002 2010-07-19 18:00:00.000
53 001 2010-07-20 09:00:00.000
54 002 2010-07-20 09:00:00.000
55 001 2010-07-20 18:00:00.000
56 002 2010-07-20 18:00:00.000
57 001 2010-07-21 09:00:00.000
58 002 2010-07-21 09:00:00.000
59 001 2010-07-21 18:00:00.000
60 002 2010-07-21 18:00:00.000
61 001 2010-07-22 09:00:00.000
62 002 2010-07-22 09:00:00.000
63 001 2010-07-22 18:00:00.000
64 002 2010-07-22 18:00:00.000
65 001 2010-07-23 09:00:00.000
66 002 2010-07-23 09:00:00.000
67 001 2010-07-23 18:00:00.000
68 002 2010-07-23 18:00:00.000
69 001 2010-07-26 09:00:00.000
70 002 2010-07-26 09:00:00.000
71 001 2010-07-26 18:00:00.000
72 002 2010-07-26 18:00:00.000
73 001 2010-07-27 09:00:00.000
74 002 2010-07-27 09:00:00.000
75 001 2010-07-27 18:00:00.000
76 002 2010-07-27 18:00:00.000
77 001 2010-07-28 09:00:00.000
78 002 2010-07-28 09:00:00.000
79 001 2010-07-28 18:00:00.000
80 002 2010-07-28 18:00:00.000
81 001 2010-07-29 09:00:00.000
82 002 2010-07-29 09:00:00.000
83 001 2010-07-29 18:00:00.000
84 002 2010-07-29 18:00:00.000
85 001 2010-07-30 09:00:00.000
86 002 2010-07-30 09:00:00.000
87 001 2010-07-30 18:00:00.000
88 002 2010-07-30 18:00:00.000
(88 行受影响)
*/
go
drop procedure insertrecord
drop table employee,KQ_RECORD