· 9 years ago · Nov 13, 2016, 05:42 PM
1
2INSERT INTO ilyenko_lab2.l.motion_in
3 (post_id, motion_time, direction, gos_number, gibdd_code)
4 VALUES
5 (1, '14:55:25:25', 'in', 'М001ММ', 77)
6 GO
7SELECT * From ilyenko_lab2.l.motion_in
8
9
10
11
12INSERT INTO ilyenko_lab2.l.motion_out
13 (post_id, motion_time_out, direction, gos_number, gibdd_code)
14 VALUES
15 (1, '16:55:30:05', 'out', 'М001ММ', 77)
16GO
17SELECT * From ilyenko_lab2.l.motion_out
18
19
20
21
22
23
24
25
26
27
28REATE FUNCTION check_time(@motion_time_in time, @gos_num_in nvarchar(20), @gibdd_code_in smallint)
29RETURNS int
30
31BEGIN
32 DECLARE @ret int;
33 if exists
34(
35 SELECT *
36 FROM ilyenko_lab2.l.motion_in, ilyenko_lab2.l.motion_out
37 WHERE abs(DATEDIFF(mi, @motion_time_in, ilyenko_lab2.l.motion_out.motion_time_out )) < 10
38 AND abs(DATEDIFF(HH, @motion_time_in, ilyenko_lab2.l.motion_out.motion_time_out )) = 0
39 AND ilyenko_lab2.l.motion_out.gos_number = @gos_num_in
40 AND ilyenko_lab2.l.motion_out.gibdd_code = @gibdd_code_in
41 AND ilyenko_lab2.l.motion_in.direction LIKE 'in'
42 AND ilyenko_lab2.l.motion_out.direction LIKE 'out'
43)
44 begin
45 SET @ret = 1;
46 end
47 else
48 begin
49 SET @ret = 0;
50 end
51 RETURN @ret;
52END;
53GO
54
55CREATE FUNCTION check_time_out(@motion_time_out time, @gos_num_out nvarchar(20), @gibdd_code_out smallint)
56RETURNS int
57
58BEGIN
59 DECLARE @ret int;
60 if exists
61(
62 SELECT *
63 FROM ilyenko_lab2.l.motion_in, ilyenko_lab2.l.motion_out
64 WHERE abs(DATEDIFF(mi, @motion_time_out, ilyenko_lab2.l.motion_out.motion_time )) < 10
65 AND abs(DATEDIFF(HH, @motion_time_out, ilyenko_lab2.l.motion_out.motion_time )) = 0
66 AND ilyenko_lab2.l.motion_out.gos_number = @gos_num_out
67 AND ilyenko_lab2.l.motion_out.gibdd_code = @gibdd_code_out
68 AND ilyenko_lab2.l.motion_in.direction LIKE 'in'
69 AND ilyenko_lab2.l.motion_out.direction LIKE 'out'
70)
71 begin
72 SET @ret = 1;
73 end
74 else
75 begin
76 SET @ret = 0;
77 end
78 RETURN @ret;
79END;
80GO
81
82
83
84
85
86
87
88
89
90
91
92
93IF OBJECT_ID('ilyenko_lab2.l.motion_in', 'U') IS NOT NULL
94 DROP TABLE ilyenko_lab2.l.motion_in
95GO
96CREATE TABLE ilyenko_lab2.l.motion_in
97(
98 post_id smallint NOT NULL,
99 motion_time time NOT NULL,
100 direction nvarchar(10) NOT NULL,
101 gos_number nvarchar(20) NOT NULL,
102 gibdd_code smallint NOT NULL,
103 --CHECK (CONVERT(INT, substring(gos_number, 2, 3)) > 0),
104 --CHECK (1<gibdd_code AND gibdd_code<299 OR 700<gibdd_code AND gibdd_code<799)
105 check(dbo.check_time(motion_time, gos_number, gibdd_code) = 1)
106)
107GO
108
109IF OBJECT_ID('ilyenko_lab2.l.motion_out', 'U') IS NOT NULL
110 DROP TABLE ilyenko_lab2.l.motion_out
111GO
112CREATE TABLE ilyenko_lab2.l.motion_out
113(
114 post_id smallint NOT NULL,
115 motion_time_out time NOT NULL,
116 direction nvarchar(10) NOT NULL,
117 gos_number nvarchar(20) NOT NULL,
118 gibdd_code smallint NOT NULL,
119 --CHECK (CONVERT(INT, substring(gos_number, 2, 3)) > 0),
120 --CHECK (1<gibdd_code AND gibdd_code<299 OR 700<gibdd_code AND gibdd_code<799)
121 check(dbo.check_time_out(motion_time_out, gos_number, gibdd_code) = 1)
122)
123GO