EshgDFH6

1CREATE FUNCTION check_time()  
2RETURNS int 
3
4BEGIN  
5    DECLARE @ret int;  
6	if exists
7	(
8		SELECT *
9		FROM ilyenko_lab2.l.motion_in, ilyenko_lab2.l.motion_out
10		WHERE abs(DATEDIFF(mi, ilyenko_lab2.l.motion_in.motion_time , ilyenko_lab2.l.motion_out.motion_time_out )) < 10 
11			AND abs(DATEDIFF(HH, ilyenko_lab2.l.motion_in.motion_time , ilyenko_lab2.l.motion_out.motion_time_out )) = 0   
12			AND ilyenko_lab2.l.motion_in.gos_number = ilyenko_lab2.l.motion_out.gos_number
13			AND ilyenko_lab2.l.motion_in.gibdd_code = ilyenko_lab2.l.motion_out.gibdd_code
14			AND ilyenko_lab2.l.motion_in.direction = 'in'
15			AND ilyenko_lab2.l.motion_out.direction = 'out'
16	)
17		begin
18			SET @ret = 1;
19		end
20	else
21		begin
22			SET @ret = 0;
23		end
24    RETURN @ret;  
25END;  
26GO  
27
28IF OBJECT_ID('ilyenko_lab2.l.motion_in', 'U') IS NOT NULL
29  DROP TABLE  ilyenko_lab2.l.motion_in
30GO
31
32CREATE TABLE ilyenko_lab2.l.motion_in
33(
34	post_id smallint NOT NULL, 
35	motion_time time NOT NULL,
36	direction nvarchar(10) NOT NULL,
37	gos_number nvarchar(20) NOT NULL,
38	gibdd_code smallint NOT NULL,
39	check(dbo.check_time() = 1)
40)
41GO
42
43
44
45
46INSERT INTO ilyenko_lab2.l.motion_in
47 (post_id, motion_time, direction, gos_number, gibdd_code)
48 VALUES 
49 (1, '11:55:25:25', 'in', 'М001ММ', 77)
50 GO
51SELECT * From ilyenko_lab2.l.motion_in
52
53
54
55
56INSERT INTO ilyenko_lab2.l.motion_out
57 (post_id, motion_time_out, direction, gos_number, gibdd_code)
58 VALUES 
59 (1, '14:55:30:05', 'out', 'М001ММ', 77)
60GO
61SELECT * From ilyenko_lab2.l.motion_out