gQ0n6nn5

· 8 years ago · Nov 16, 2017, 04:52 PM
1USE master 
2GO
3
4IF EXISTS (
5	SELECT name 
6	FROM sys.databases 
7	WHERE name = N'Nagaev' )
8ALTER DATABASE [Nagaev] set single_user with rollback immediate
9GO
10
11IF EXISTS (
12	SELECT name 
13	FROM sys.databases 
14	WHERE name = N'Nagaev' )
15DROP DATABASE [Nagaev]
16GO
17
18CREATE DATABASE [Nagaev]
19GO
20
21USE [Nagaev]
22GO
23
24CREATE TABLE region (
25	id TINYINT,
26	name NVARCHAR(30),
27	CONSTRAINT PK_region_id PRIMARY KEY (id)
28)
29GO
30
31CREATE TABLE rcode (
32	region_id tinyint,
33	code tinyint,
34	CONSTRAINT PK_region_code PRIMARY KEY (code),
35	CONSTRAINT FK_region_id FOREIGN KEY (region_id) REFERENCES region(id) ON UPDATE CASCADE,
36)
37GO
38
39CREATE TABLE color 
40(
41	id TINYINT,
42	name NVARCHAR(20),
43	CONSTRAINT PK_color_id PRIMARY KEY (id) 
44)
45GO
46
47CREATE TABLE post (
48	id tinyint NOT NULL,
49	name varchar(20) NULL,
50	CONSTRAINT PK_post_id PRIMARY KEY (id),
51)
52GO
53
54CREATE FUNCTION CorrectNumber (@num VARCHAR(30), @rcode tinyint)
55RETURNS int
56AS
57BEGIN
58	DECLARE @str_rcode tinyint
59	select @str_rcode = CONVERT(varchar, @rcode)
60	if (UPPER(@num) like '[ÐÐ’Ð•ÐšÐœÐÐžÐ Ð¡Ð¢Ð£Ð¥ABEKMHOPCTYX][0-9][0-9][0-9][ÐÐ’Ð•ÐšÐœÐÐžÐ Ð¡Ð¢Ð£Ð¥ABEKMHOPCTYX][ÐÐ’Ð•ÐšÐœÐÐžÐ Ð¡Ð¢Ð£Ð¥ABEKMHOPCTYX]' 
61		and @num not like '%000%' 
62		and (@str_rcode like '[0-9][0-9]' or @str_rcode like '[127][0-9][0-9]')) 
63		return 0
64	return -1
65END
66GO
67
68CREATE FUNCTION CorrectRecord (@date DATETIME, @dir VARCHAR(1), @auto_number nvarchar(7), @auto_rcode tinyint, @id tinyint)
69RETURNS int
70AS
71BEGIN
72	if NOT EXISTS(
73		SELECT * from record 
74	    WHERE record.id != @id and record.auto_number = @auto_number and 
75		      record.auto_rcode = @auto_rcode
76		)
77		return 0
78
79	if EXISTS(
80		SELECT * from record 
81		INNER JOIN
82			(SELECT MAX(catchtime) as max_date, auto_number FROM record
83				WHERE catchtime < @date and @auto_number = auto_number and @auto_rcode = auto_rcode
84				GROUP BY auto_number) r
85		ON record.catchtime = r.max_date and record.auto_number = r.auto_number
86		WHERE @dir != record.direction
87		)
88		return 0
89
90	return -1
91END
92GO
93
94CREATE TABLE auto (
95	color_id tinyint NOT NULL,
96	number NVARCHAR(7) NOT NULL,
97	rcode tinyint NOT NULL,
98	owner_name NVARCHAR(100) NOT NULL,
99	CONSTRAINT PK_number PRIMARY KEY CLUSTERED 
100    (
101        number ASC,
102        rcode ASC
103    ),
104	CONSTRAINT FK_region_code FOREIGN KEY (rcode) REFERENCES rcode(code) ON UPDATE CASCADE,
105	CONSTRAINT FK_color_id FOREIGN KEY (color_id) REFERENCES color(id) ON UPDATE CASCADE
106)
107GO
108
109CREATE TABLE record (
110	id tinyint NOT NULL,
111	post_id tinyint NOT NULL,
112	auto_number nvarchar(7) NOT NULL,
113	auto_rcode tinyint,
114	catchtime DATETIME NOT NULL,
115	direction CHAR NOT NULL,
116	CONSTRAINT CK_direction CHECK (direction = '>' or direction = '<'),
117	CONSTRAINT CK_record CHECK (dbo.CorrectRecord(catchtime, direction, auto_number, auto_rcode, id) = 0),
118	CONSTRAINT PK_record_id PRIMARY KEY (id), 
119	CONSTRAINT FK_post_id FOREIGN KEY (post_id) REFERENCES post(id) ON UPDATE CASCADE,
120	CONSTRAINT FK_auto_id FOREIGN KEY (auto_number, auto_rcode) REFERENCES auto(number, rcode) ON UPDATE CASCADE,
121)
122GO
123
124CREATE TRIGGER on_insert_auto ON auto FOR INSERT AS
125BEGIN
126	IF EXISTS (
127	SELECT *
128	FROM inserted WHERE dbo.CorrectNumber(number, rcode) = -1
129	)
130	BEGIN
131		PRINT 'ÐÐµÐºÐ¾Ñ€Ñ€ÐµÐºÑ‚Ð½Ð°Ñ Ð·Ð°Ð¿Ð¸ÑÑŒ'
132		ROLLBACK TRANSACTION
133	END
134END
135GO
136
137CREATE FUNCTION GetAutoType (
138	@fromPost tinyint, 
139	@toPost tinyint,
140	@fromRegion tinyint
141)
142RETURNS VARCHAR(30)
143AS
144BEGIN
145	if (@fromPost != @toPost AND @fromRegion != 1)
146		RETURN 'Ð¢Ñ€Ð°Ð½Ð·Ð¸Ñ‚Ð½Ñ‹Ð¹'
147	if (@fromPost = @toPost AND @fromRegion != 1)
148		RETURN 'Ð˜Ð½Ð¾Ð³Ð¾Ñ€Ð¾Ð´Ð½Ð¸Ð¹'
149	if (@fromRegion = 1)
150		RETURN 'ÐœÐµÑÑ‚Ð½Ñ‹Ð¹'
151	RETURN 'ÐŸÑ€Ð¾Ñ‡Ð¸Ð¹'
152END
153GO
154
155INSERT INTO region(id, name) VALUES
156(1, N'Ð¡Ð²ÐµÑ€Ð´Ð»Ð¾Ð²ÑÐºÐ°Ñ Ð¾Ð±Ð»Ð°ÑÑ‚ÑŒ'),
157(2, N'Ð¡Ð°Ð¼Ð°Ñ€ÑÐºÐ°Ñ Ð¾Ð±Ð»Ð°ÑÑ‚ÑŒ'),
158(3, N'ÐœÐ¾ÑÐºÐ¾Ð²ÑÐºÐ°Ñ Ð¾Ð±Ð»Ð°ÑÑ‚ÑŒ'),
159(4, N'Ð§ÐµÐ»ÑÐ±Ð¸Ð½ÑÐºÐ°Ñ Ð¾Ð±Ð»Ð°ÑÑ‚ÑŒ'),
160(5, N'ÐšÑ€Ð°ÑÐ½Ð¾Ð´Ð°Ñ€ÑÐºÐ¸Ð¹ ÐºÑ€Ð°Ð¹')
161GO
162
163INSERT INTO rcode(region_id, code) VALUES
164(1, 66), (1, 96), 
165(2, 63), (2, 163),
166(3, 50), (3, 150),
167(4, 74), (4, 174),
168(5, 23), (5, 93)
169GO
170
171INSERT INTO color(id, name) VALUES
172(1, N'ÐšÑ€Ð°ÑÐ½Ñ‹Ð¹'),
173(2, N'Ð‘ÐµÐ»Ñ‹Ð¹'),
174(3, N'Ð¡Ð¸Ð½Ð¸Ð¹'),
175(4, N'Ð§ÐµÑ€Ð½Ñ‹Ð¹'),
176(5, N'Ð¡ÐµÑ€Ñ‹Ð¹')
177GO
178
179INSERT INTO post(id, name) VALUES
180(1, 'Ð¡ÐµÐ²ÐµÑ€Ð½Ñ‹Ð¹')
181,(2, 'Ð—Ð°Ð¿Ð°Ð´Ð½Ñ‹Ð¹')
182,(3, 'Ð’Ð¾ÑÑ‚Ð¾Ð½Ñ‹Ð¹')
183,(4, 'Ð®Ð¶Ð½Ñ‹Ð¹')
184,(5, 'Ð ÐµÐ·ÐµÑ€Ð²Ð½Ñ‹Ð¹')
185GO
186
187INSERT INTO auto (color_id, number, rcode, owner_name) VALUES
188(1, 'Ð123ÐÐ', 66, N'ÐÑ€Ñ‚ÐµÐ¼Ð¾Ð²'),
189(1, 'Ð’224ÐÐ', 63, N'Ð‘Ð¾Ñ€Ð¾Ð²Ð¸ÐºÐ¾Ð²'),
190(1, 'Ð¡114ÐÐ', 50, N'Ð’Ð¾Ð»Ð³Ð¸Ð½'),
191(1, 'Ð213ÐÐ', 63, N'Ð“Ñ€ÑƒÐ·Ð´Ðµ'),
192(1, 'Ð’124ÐÐ', 163, N'Ð”Ð¾Ð¼Ð°ÑˆÐ½Ð¸Ñ…'),
193(2, 'Ð¡223ÐÐ', 50, N'Ð–Ð¸Ð³Ð°Ð»Ð¾Ð²'),
194(2, 'Ð113ÐÐ', 23, N'Ð—ÐµÐ¹Ð½Ð°Ð»Ð¾Ð²'),
195(2, 'Ð’214ÐÐ', 93, N'ÐšÐ»ÐµÐ¿Ð¸Ð½Ð¸Ð½'),
196(2, 'Ð¡123ÐÐ', 150, N'Ð›Ð¸Ð²Ð°Ð½Ð¾Ð²'),
197(3, 'Ð214ÐÐ', 50, N'ÐœÐ¸Ñ€Ð¾Ð½Ð¾Ð²'),
198(3, 'Ð’113ÐÐ', 63, N'ÐÐ°Ð¹Ð¼Ð¸Ð½'),
199(3, 'Ð¡224ÐÐ', 163, N'ÐŸÑ€Ð¾ÑÐºÑƒÑ€Ð½Ð¸Ð½'),
200(3, 'Ð124ÐÐ', 96, N'Ð ÐµÐ²Ð°Ð½Ð¾Ð²'),
201(3, 'Ð’213ÐÐ', 163, N'Ð¡Ð¸Ð²Ð¾Ð¹'),
202(4, 'Ð¡124ÐÐ', 66, N'Ð¢Ð°Ñ€Ð°Ð±Ð°Ð½ÐºÐ¸Ð½'),
203(4, 'Ð224ÐÐ', 174, N'Ð“Ð°Ð¹Ð½Ð°Ð½Ð¾Ð²'),
204(4, 'Ð114ÐÐ', 163, N'Ð¥Ð¾Ñ€Ð¾Ð½ÑÐºÐ¸Ð½'),
205(4, 'Ð¢223ÐÐ', 150, N'Ð§Ð°Ð»Ð³Ð°Ð½Ð¾Ð²'),
206(5, 'Ð¥123ÐÐ', 50, N'Ð’Ð°Ð¹Ñ'),
207(5, 'Ð213ÐÐ', 74, N'Ð•Ð¿Ð¸Ñ„Ð°Ð½Ð¾Ð²')
208GO
209
210INSERT INTO record (id, post_id, auto_number, auto_rcode, catchtime, direction) VALUES
211(1, 1, 'Ð123ÐÐ', 66, N'20120618 10:34:08', N'>')
212,(2, 2, 'Ð123ÐÐ', 66, N'20120618 10:54:09', N'<')
213,(3, 2, 'Ð’224ÐÐ', 63, N'20120618 10:10:00', N'>')
214,(4, 1, 'Ð’224ÐÐ', 63, N'20120618 10:34:09', N'<')
215,(5, 3, 'Ð¡114ÐÐ', 50, N'20120618 11:12:13', N'>')
216,(6, 4, 'Ð¡114ÐÐ', 50, N'20120618 11:59:12', N'<')
217,(7, 4, 'Ð213ÐÐ', 63, N'20120618 10:34:08', N'>')
218,(8, 3, 'Ð213ÐÐ', 63, N'20120618 13:37:15', N'<')
219,(9, 5, 'Ð’124ÐÐ', 163, N'20120618 09:01:05', N'>')
220,(10, 1, 'Ð’124ÐÐ', 163, N'20120618 10:13:49', N'<')
221,(11, 1, 'Ð¡223ÐÐ', 50, N'20120618 12:34:08', N'>')
222,(12, 1, 'Ð¡223ÐÐ', 50, N'20120618 12:44:09', N'<')
223,(13, 2, 'Ð113ÐÐ', 23, N'20120618 13:34:08', N'>')
224,(14, 2, 'Ð113ÐÐ', 23, N'20120618 15:21:09', N'<')
225,(15, 3, 'Ð’214ÐÐ', 93, N'20120618 10:11:18', N'>')
226,(16, 3, 'Ð’214ÐÐ', 93, N'20120618 21:56:21', N'<')
227,(17, 4, 'Ð¡123ÐÐ', 150, N'20120618 12:34:08', N'>')
228,(18, 5, 'Ð¡123ÐÐ', 150, N'20120618 13:34:09', N'<')
229,(19, 5, 'Ð214ÐÐ', 50, N'20120618 14:14:14', N'>')
230,(20, 1, 'Ð214ÐÐ', 50, N'20120618 14:34:14', N'<')
231,(21, 2, 'Ð’113ÐÐ', 63, N'20120618 15:34:08', N'>')
232,(22, 2, 'Ð’113ÐÐ', 63, N'20120618 16:37:38', N'<')
233,(23, 1, 'Ð¡224ÐÐ', 163, N'20120618 10:34:08', N'>')
234,(24, 2, 'Ð¡224ÐÐ', 163, N'20120618 11:36:09', N'<')
235,(25, 1, 'Ð124ÐÐ', 96, N'20120618 14:39:59', N'>')
236,(26, 3, 'Ð124ÐÐ', 96, N'20120618 15:34:59', N'<')
237,(27, 1, 'Ð’213ÐÐ', 163, N'20120618 17:34:08', N'>')
238,(28, 3, 'Ð’213ÐÐ', 163, N'20120618 23:34:09', N'<')
239,(29, 5, 'Ð¡124ÐÐ', 66, N'20120618 18:24:00', N'>')
240,(30, 1, 'Ð¡124ÐÐ', 66, N'20120618 19:34:03', N'<')
241,(31, 2, 'Ð224ÐÐ', 174, N'20120618 16:22:08', N'>')
242,(32, 1, 'Ð224ÐÐ', 174, N'20120618 20:51:09', N'<')
243,(33, 2, 'Ð114ÐÐ', 163, N'20120618 11:03:08', N'>')
244,(34, 1, 'Ð114ÐÐ', 163, N'20120618 19:33:09', N'<')
245,(35, 3, 'Ð¢223ÐÐ', 150, N'20120618 19:28:17', N'>')
246,(36, 3, 'Ð¢223ÐÐ', 150, N'20120618 19:54:49', N'<')
247,(37, 1, 'Ð¥123ÐÐ', 50, N'20120618 23:00:00', N'>')
248,(38, 4, 'Ð¥123ÐÐ', 50, N'20120618 23:59:59', N'<')
249,(39, 5, 'Ð213ÐÐ', 74, N'20120618 20:20:18', N'>')
250,(40, 4, 'Ð213ÐÐ', 74, N'20120618 22:28:41', N'<')
251--,(41, 5, 'Ð213ÐÐ', 74, N'20120618 10:34:20', N'<')
252GO
253
254DECLARE @Temp TABLE
255(
256	from_city tinyint,
257	from_post tinyint,
258	to_post tinyint
259);
260
261INSERT INTO 
262@Temp
263SELECT rcode.region_id, rr1.post_id, rr2.post_id FROM auto a
264	JOIN record rr1 ON rr1.auto_number = a.number and rr1.auto_rcode = a.rcode AND rr1.direction = N'>'
265	JOIN record rr2 ON rr2.auto_number = a.number and rr2.auto_rcode = a.rcode AND rr2.direction = N'<'
266	JOIN rcode ON rcode.code = a.rcode
267GROUP BY a.owner_name, a.number, rr1.post_id, rr2.post_id, rcode.region_id
268
269SELECT dbo.GetAutoType(from_post, to_post, from_city) as 'Ð¢Ð¸Ð¿', COUNT(*) as 'ÐšÐ¾Ð»Ð¸Ñ‡ÐµÑÑ‚Ð²Ð¾' FROM @Temp
270GROUP BY dbo.GetAutoType(from_post, to_post, from_city)
271GO
272
273DECLARE @Temp TABLE
274(
275	auto_number nvarchar(7),
276	auto_rcode tinyint,
277	from_city tinyint,
278	from_post tinyint,
279	to_post tinyint
280);
281
282INSERT INTO 
283@Temp
284SELECT a.number, a.rcode, rr3.region_id, rr1.post_id, rr2.post_id FROM auto a
285	JOIN record rr1 ON rr1.auto_number = a.number and rr1.auto_rcode = a.rcode AND rr1.direction = N'>'
286	JOIN record rr2 ON rr2.auto_number = a.number AND rr2.auto_rcode = a.rcode and rr2.direction = N'<'
287	JOIN rcode rr3 ON rr3.code = a.rcode
288GROUP BY a.owner_name, rr1.post_id, rr2.post_id, rr3.region_id, a.number, a.rcode
289
290SELECT
291	   a.owner_name as 'Ð’Ð»Ð°Ð´ÐµÐ»ÐµÑ†', 
292	   a.number + CAST(a.rcode AS VARCHAR) as 'ÐÐ¾Ð¼ÐµÑ€ Ð°Ð²Ñ‚Ð¾',
293	   FORMAT (r1.catchtime, 'HH\:mm\:ss', 'ru-RU' ) as 'Ð’Ñ€ÐµÐ¼Ñ Ð²ÑŠÐµÐ·Ð´Ð°', 
294       FORMAT (r2.catchtime, 'HH\:mm\:ss', 'ru-RU' ) as 'Ð’Ñ€ÐµÐ¼Ñ Ð²Ñ‹ÐµÐ·Ð´Ð°',
295	   dbo.GetAutoType(from_post, to_post, from_city) as 'Ð¢Ð¸Ð¿',	   
296	   region.name as 'Ð Ð¾Ð´Ð½Ð¾Ð¹ Ñ€ÐµÐ³Ð¸Ð¾Ð½'
297FROM @Temp t
298	JOIN auto a ON a.number = t.auto_number and a.rcode  = t.auto_rcode
299	JOIN record r1 ON r1.direction = '>' and r1.auto_number = t.auto_number and r1.auto_rcode = t.auto_rcode
300	JOIN record r2 ON r2.direction = '<' and r2.auto_number = t.auto_number and r2.auto_rcode = t.auto_rcode 
301	JOIN rcode rcode ON rcode.code = a.rcode
302	JOIN region ON rcode.region_id = region.id
303WHERE dbo.GetAutoType(from_post, to_post, from_city) like '%' 
304GROUP BY a.owner_name, r1.catchtime,  r2.catchtime, from_post, to_post,
305		 from_city, region.name, a.number, a.rcode
306ORDER BY a.owner_name 
307GO
308
309
310
311
312USE [Nagaev]
313GO
314
315INSERT INTO auto (color_id, number, rcode, owner_name) VALUES
316(3, 'Ð21ÐÐ', 74, N'Ð¡Ð±Ð¸Ð²Ð°Ð½Ð¾Ð²')
317GO
318
319INSERT INTO auto (color_id, number, rcode, owner_name) VALUES
320(3, 'Ð212ÐG', 74, N'Ð¡Ð±Ð¸Ð²Ð°Ð½Ð¾Ð²')
321GO
322
323INSERT INTO auto (color_id, number, rcode, owner_name) VALUES
324(3, 'Ð000ÐA', 74, N'Ð¡Ð±Ð¸Ð²Ð°Ð½Ð¾Ð²')
325GO
326
327INSERT INTO auto (color_id, number, rcode, owner_name) VALUES
328(3, 'Ð100ÐA', 215, N'Ð¡Ð±Ð¸Ð²Ð°Ð½Ð¾Ð²')
329GO
330
331INSERT INTO record (id, post_id, auto_number, auto_rcode, catchtime, direction) VALUES
332 (41, 5, 'Ð213BC', 74, N'20120618 20:34:10', N'<')
333,(42, 5, 'Ð213BC', 74, N'20120618 20:35:20', N'<')
334GO
335
336INSERT INTO record (id, post_id, auto_number, auto_rcode, catchtime, direction) VALUES
337 (41, 5, 'Ð213BC', 74, N'20120618 20:34:10', N'>')
338,(42, 5, 'Ð213BC', 74, N'20120618 20:35:20', N'>')
339GO