WHgCJeFk

1USE master
2GO
3
4IF  EXISTS (
5	SELECT name 
6		FROM sys.databases 
7		WHERE name = N'KN_303_shvedov'
8)
9ALTER DATABASE KN_303_shvedov set single_user with rollback immediate
10GO
11
12IF  EXISTS (
13	SELECT name 
14		FROM sys.databases 
15		WHERE name = N'KN_303_shvedov'
16)
17DROP DATABASE KN_303_shvedov
18GO
19
20CREATE DATABASE KN_303_shvedov
21GO
22
23USE KN_303_shvedov
24GO
25
26IF EXISTS(
27  SELECT *
28    FROM sys.schemas
29   WHERE name = N'shvedov'
30) 
31DROP SCHEMA shvedov
32GO
33
34CREATE SCHEMA shvedov
35GO
36
37IF OBJECT_ID (N'KN_303_shvedov.shvedov.isStateNumberCorrect ', N'FN') IS NOT NULL  
38   DROP FUNCTION shvedov.isStateNumberCorrect; 
39GO
40
41CREATE FUNCTION shvedov.isStateNumberCorrect(@stateNumber VARCHAR(9))  
42RETURNS BIT
43AS
44BEGIN
45    IF (@stateNumber like '[АВЕКМНОРСТУХавекмнорстух][1-9][0-9][0-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух][1,2,7][0-9][0-9]' OR
46	    @stateNumber like '[АВЕКМНОРСТУХ][1-9][0-9][0-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух][0-9][0-9]' OR
47
48		@stateNumber like '[АВЕКМНОРСТУХавекмнорстух]0[1-9][0-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух][1,2,7][0-9][0-9]' OR
49	    @stateNumber like '[АВЕКМНОРСТУХавекмнорстух]0[1-9][0-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух][0-9][0-9]' OR
50
51		@stateNumber like '[АВЕКМНОРСТУХавекмнорстух]00[1-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух][1,2,7][0-9][0-9]' OR
52	    @stateNumber like '[АВЕКМНОРСТУХавекмнорстух]00[1-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух][0-9][0-9]')
53		BEGIN
54			RETURN 1;
55		END
56	RETURN 0;
57END; 
58GO
59
60IF OBJECT_ID (N'KN_303_shvedov.shvedov.getRegionNumber', N'FN') IS NOT NULL  
61   DROP FUNCTION shvedov.getRegionNumber; 
62GO
63
64CREATE FUNCTION shvedov.getRegionNumber(@stateNumber VARCHAR(9))  
65RETURNS int
66AS   
67BEGIN
68	DECLARE @regionNumber int = CAST(SUBSTRING(@stateNumber, LEN(@stateNumber) - 1, LEN(@stateNumber)) AS INT)
69	IF ((SELECT COUNT(*) FROM KN_303_shvedov.shvedov.Regions WHERE regionNumber = @regionNumber) = 1)
70		RETURN @regionNumber
71	RETURN null
72END;
73GO
74
75CREATE TABLE KN_303_shvedov.shvedov.TravelInformationForInsert
76(
77	stateNumber varchar(9) CHECK (shvedov.isStateNumberCorrect(stateNumber) = 1) NOT NULL,
78	isInside int NOT NULL,
79	postId tinyint NOT NULL,
80	crossingTime time NOT NULL
81)
82
83
84CREATE TABLE KN_303_shvedov.shvedov.TravelInformation
85(
86	stateNumberId int NOT NULL,
87	isInside bit NOT NULL,
88	postId tinyint NOT NULL,
89	crossingTime time NOT NULL
90)
91GO
92
93CREATE TABLE KN_303_shvedov.shvedov.Post
94(
95	postId tinyint NOT NULL,
96	postName varchar(40) NOT NULL
97	CONSTRAINT PK_postId PRIMARY KEY (postId)
98)
99
100CREATE TABLE KN_303_shvedov.shvedov.StateNumberInformation
101(
102	id int NOT NULL IDENTITY PRIMARY KEY,
103	stateNumber varchar(9) NOT NULL,
104	regionNumber AS shvedov.getRegionNumber(stateNumber),
105)
106
107CREATE INDEX stateNumberIndex ON KN_303_shvedov.shvedov.StateNumberInformation (stateNumber);
108
109
110CREATE TABLE KN_303_shvedov.shvedov.Regions
111(
112	regionNumber tinyint NOT NULL,
113	regionName varchar(60) NOT NULL
114	CONSTRAINT PK_regionNumber PRIMARY KEY (regionNumber)
115)
116GO
117
118ALTER TABLE KN_303_shvedov.shvedov.TravelInformation
119WITH CHECK ADD CONSTRAINT FK_postId FOREIGN KEY (postId)
120REFERENCES KN_303_shvedov.shvedov.Post (postId)
121ON UPDATE CASCADE
122ON DELETE CASCADE
123GO
124
125ALTER TABLE KN_303_shvedov.shvedov.TravelInformation
126WITH CHECK ADD CONSTRAINT stateNumberId FOREIGN KEY (stateNumberId)
127REFERENCES KN_303_shvedov.shvedov.StateNumberInformation (id)
128ON UPDATE CASCADE
129ON DELETE CASCADE
130GO
131
132INSERT INTO KN_303_shvedov.shvedov.Post (postId, postName) VALUES
133(0, 'Юг')
134,(1, 'Север')
135,(2, 'Запад')
136,(3, 'Восток')
137GO
138
139INSERT INTO KN_303_shvedov.shvedov.Regions (regionNumber, regionName) VALUES
140(1, 'Республика Адыгея')
141,(2, 'Республика Башкортостан')
142,(3, 'Республика Бурятия')
143,(4, 'Республика Алтай')
144,(5, 'Республика Дагестан')
145,(6, 'Республика Ингушетия')
146,(7, 'Кабардино-Балкарская Республика')
147,(8, 'Республика Калмыкия')
148,(9, 'Карачаево-Черкесская Республика')
149,(10, 'Республика Карелия')
150,(11, 'Республика Коми')
151,(12, 'Республика Марий Эл')
152,(13, 'Республика Мордовия')
153,(14, 'Республика Саха (Якутия)')
154,(15, 'Республика Северная Осетия - Алания')
155,(16, 'Республика Татарстан (Татарстан)')
156,(17, 'Республика Тыва')
157,(18, 'Удмуртская Республика')
158,(19, 'Республика Хакасия')
159,(20, 'Чеченская Республика')
160,(21, 'Чувашская Республика - Чувашия')
161,(22, 'Алтайский край')
162,(23, 'Краснодарский край')
163,(24, 'Красноярский край')
164,(25, 'Приморский край')
165,(26, 'Ставропольский край')
166,(27, 'Хабаровский край')
167,(28, 'Амурская область')
168,(29, 'Архангельская область')
169,(30, 'Астраханская область')
170,(31, 'Белгородская область')
171,(32, 'Брянская область')
172,(33, 'Владимирская область')
173,(34, 'Волгоградская область')
174,(35, 'Вологодская область')
175,(36, 'Воронежская область')
176,(37, 'Ивановская область')
177,(38, 'Иркутская область')
178,(39, 'Калининградская область')
179,(40, 'Калужская область')
180,(41, 'Камчатский край')
181,(42, 'Кемеровская область')
182,(43, 'Кировская область')
183,(44, 'Костромская область')
184,(45, 'Курганская область')
185,(46, 'Курская область')
186,(47, 'Ленинградская область')
187,(48, 'Липецкая область')
188,(49, 'Магаданская область')
189,(50, 'Московская область')
190,(51, 'Мурманская область')
191,(52, 'Нижегородская область')
192,(53, 'Новгородская область')
193,(54, 'Новосибирская область')
194,(55, 'Омская область')
195,(56, 'Оренбургская область')
196,(57, 'Орловская область')
197,(58, 'Пензенская область')
198,(59, 'Пермский край')
199,(60, 'Псковская область')
200,(61, 'Ростовская область')
201,(62, 'Рязанская область')
202,(63, 'Самарская область')
203,(64, 'Саратовская область')
204,(65, 'Сахалинская область')
205,(66, 'Свердловская область')
206,(67, 'Смоленская область')
207,(68, 'Тамбовская область')
208,(69, 'Тверская область')
209,(70, 'Томская область')
210,(71, 'Тульская область')
211,(72, 'Тюменская область')
212,(73, 'Ульяновская область')
213,(74, 'Челябинская область')
214,(75, 'Забайкальский край')
215,(76, 'Ярославская область')
216,(77, 'г. Москва')
217,(78, 'Санкт-Петербург')
218,(79, 'Еврейская автономная область')
219,(83, 'Ненецкий автономный округ')
220,(86, 'Ханты-Мансийский автономный округ - Югра')
221,(87, 'Чукотский автономный округ')
222,(89, 'Ямало-Ненецкий автономный округ')
223,(91, 'Республика Крым')
224,(92, 'Севастополь')
225,(99, 'Иные территории включая город и космодром Байконур')
226GO
227
228
229IF OBJECT_ID (N'KN_303_shvedov.shvedov.getStateNumber', N'FN') IS NOT NULL  
230   DROP FUNCTION shvedov.getStateNumber; 
231GO
232
233CREATE FUNCTION shvedov.getStateNumber(@stateNumberId int)  
234RETURNS VARCHAR(9)
235AS
236BEGIN
237	DECLARE getStateNumberCursor CURSOR FOR
238	SELECT stateNumber
239	FROM KN_303_shvedov.shvedov.StateNumberInformation
240	WHERE UPPER(@stateNumberId) = id
241
242	OPEN getStateNumberCursor
243	DECLARE @result VARCHAR(9)
244	FETCH NEXT FROM getStateNumberCursor INTO @result
245	CLOSE getStateNumberCursor
246	DEALLOCATE getStateNumberCursor
247	RETURN @result
248END;
249GO
250
251IF OBJECT_ID (N'KN_303_shvedov.shvedov.getStateNumberId', N'FN') IS NOT NULL  
252   DROP FUNCTION shvedov.getStateNumberId; 
253GO
254
255CREATE FUNCTION shvedov.getStateNumberId(@stateNumber varchar(9))  
256RETURNS int
257AS
258BEGIN
259	DECLARE getStateNumberIdCursor CURSOR FOR
260	SELECT id
261	FROM KN_303_shvedov.shvedov.StateNumberInformation
262	WHERE UPPER(@stateNumber) = stateNumber
263
264	OPEN getStateNumberIdCursor
265	DECLARE @result int
266	FETCH NEXT FROM getStateNumberIdCursor INTO @result
267	CLOSE getStateNumberIdCursor 
268	DEALLOCATE getStateNumberIdCursor 
269	RETURN @result
270END;
271GO
272
273IF OBJECT_ID (N'KN_303_shvedov.shvedov.formatDirection', N'FN') IS NOT NULL  
274   DROP FUNCTION shvedov.formatDirection; 
275GO
276
277CREATE FUNCTION shvedov.formatDirection(@direction bit)  
278RETURNS varchar(10)
279AS
280BEGIN
281	IF (@direction = 1)
282		RETURN 'В город'
283	IF (@direction = 0)
284		RETURN 'Из города'
285	RETURN null
286END;
287GO
288
289
290IF OBJECT_ID (N'KN_303_shvedov.shvedov.isCarInCity', N'FN') IS NOT NULL  
291   DROP FUNCTION shvedov.isCarInCity; 
292GO
293
294CREATE FUNCTION shvedov.isCarInCity(@stateNumber VARCHAR(9))  
295RETURNS int
296AS   
297BEGIN
298	DECLARE isCarInCityCursor CURSOR FOR
299	SELECT isInside
300	FROM KN_303_shvedov.shvedov.TravelInformation
301	WHERE UPPER(@stateNumber) = shvedov.getstateNumber(stateNumberId)
302
303	OPEN isCarInCityCursor
304	DECLARE @result bit
305	FETCH NEXT FROM isCarInCityCursor INTO @result
306	CLOSE isCarInCityCursor
307	DEALLOCATE isCarInCityCursor
308	RETURN @result
309END;
310GO
311
312IF OBJECT_ID (N'KN_303_shvedov.shvedov.getLastTravelTime', N'FN') IS NOT NULL  
313   DROP FUNCTION shvedov.getLastTravelTime; 
314GO
315
316CREATE FUNCTION shvedov.getLastTravelTime(@stateNumber VARCHAR(9))  
317RETURNS time
318AS   
319BEGIN
320	DECLARE lastTravelTimeCursor CURSOR FOR
321	SELECT crossingTime
322	FROM KN_303_shvedov.shvedov.TravelInformation
323	WHERE UPPER(@stateNumber) = shvedov.getstateNumber(stateNumberId)
324
325	OPEN lastTravelTimeCursor
326	DECLARE @result time
327	FETCH NEXT FROM lastTravelTimeCursor INTO @result
328	CLOSE lastTravelTimeCursor
329	DEALLOCATE lastTravelTimeCursor
330	RETURN @result
331END;
332GO
333
334CREATE TRIGGER shvedov.AddTravelInformation
335    ON KN_303_shvedov.shvedov.TravelInformationForInsert
336    INSTEAD OF INSERT
337AS BEGIN
338	DECLARE @stateNumber VARCHAR(9)
339	DECLARE @isInside INT
340	DECLARE @postId TINYINT
341	DECLARE @crossingTime DATETIME
342
343	DECLARE travleCursor CURSOR FOR
344	SELECT stateNumber, isInside, postId, crossingTime
345	FROM inserted
346
347	OPEN travleCursor
348	FETCH NEXT FROM travleCursor INTO @stateNumber, @isInside, @postId, @crossingTime
349
350	WHILE @@FETCH_STATUS = 0
351	BEGIN
352			IF NOT EXISTS(SELECT id
353				FROM KN_303_shvedov.shvedov.StateNumberInformation
354				WHERE stateNumber = UPPER(@stateNumber))
355				BEGIN
356					INSERT INTO KN_303_shvedov.shvedov.StateNumberInformation (stateNumber) VALUES
357					(UPPER(@stateNumber))
358				END
359		
360			DECLARE tmpTableCursor CURSOR FOR 
361				SELECT TOP 1 id
362				FROM KN_303_shvedov.shvedov.StateNumberInformation
363				WHERE stateNumber = UPPER(@stateNumber)
364			OPEN tmpTableCursor
365			DECLARE @stateNumberId INT
366			FETCH NEXT FROM tmpTableCursor INTO @stateNumberId
367			CLOSE tmpTableCursor
368			DEALLOCATE tmpTableCursor
369			IF (shvedov.isCarInCity(@stateNumber) IS NULL OR shvedov.isCarInCity(@stateNumber) <> @isInside
370				AND datediff(mi, shvedov.getLastTravelTime(@stateNumber), @crossingTime) >= 5)
371			BEGIN
372				INSERT INTO KN_303_shvedov.shvedov.TravelInformation (stateNumberId, isInside, postId, crossingTime) VALUES
373					(@stateNumberId, @isInside, @postId, @crossingTime)
374			END
375		FETCH NEXT FROM travleCursor INTO @stateNumber, @isInside, @postId, @crossingTime
376	END
377
378	CLOSE travleCursor
379    DEALLOCATE travleCursor  
380END
381GO
382
383
384
385
386IF OBJECT_ID (N'KN_303_shvedov.shvedov.getInsidePostId', N'FN') IS NOT NULL  
387   DROP FUNCTION shvedov.getInsidePostId; 
388GO
389
390CREATE FUNCTION shvedov.getInsidePostId(@stateNumber varchar(9))  
391RETURNS tinyint
392AS
393BEGIN
394	DECLARE getInsidePostIdCursor CURSOR FOR
395	SELECT postId
396	FROM KN_303_shvedov.shvedov.TravelInformation
397	WHERE stateNumberId = shvedov.getStateNumberId(@stateNumber) AND isInside = 1
398
399	OPEN getInsidePostIdCursor
400	DECLARE @result tinyint
401	FETCH NEXT FROM getInsidePostIdCursor INTO @result
402	CLOSE getInsidePostIdCursor
403	DEALLOCATE getInsidePostIdCursor
404	RETURN @result
405END;
406GO
407
408IF OBJECT_ID (N'KN_303_shvedov.shvedov.getPostName', N'FN') IS NOT NULL  
409   DROP FUNCTION shvedov.getPostName; 
410GO
411
412CREATE FUNCTION shvedov.getPostName(@postId tinyint)
413RETURNS varchar(30)
414AS
415BEGIN
416	DECLARE getInsidePostIdCursor CURSOR FOR
417	SELECT postName
418	FROM KN_303_shvedov.shvedov.Post
419	WHERE postId = @postId
420
421	OPEN getInsidePostIdCursor
422	DECLARE @result varchar(30)
423	FETCH NEXT FROM getInsidePostIdCursor INTO @result
424	CLOSE getInsidePostIdCursor
425	DEALLOCATE getInsidePostIdCursor
426	RETURN @result
427END;
428GO
429
430
431IF OBJECT_ID (N'KN_303_shvedov.shvedov.getOutsidePostId', N'FN') IS NOT NULL  
432   DROP FUNCTION shvedov.getOutsidePostId; 
433GO
434
435CREATE FUNCTION shvedov.getOutsidePostId(@stateNumber varchar(9))  
436RETURNS tinyint
437AS
438BEGIN
439	DECLARE getOutsidePostIdCursor CURSOR FOR
440	SELECT postId
441	FROM KN_303_shvedov.shvedov.TravelInformation
442	WHERE stateNumberId = shvedov.getStateNumberId(@stateNumber) AND isInside = 0
443
444	OPEN getOutsidePostIdCursor
445	DECLARE @result tinyint
446	FETCH NEXT FROM getOutsidePostIdCursor INTO @result
447	CLOSE getOutsidePostIdCursor
448	DEALLOCATE getOutsidePostIdCursor
449	RETURN @result
450END;
451GO
452
453IF OBJECT_ID (N'KN_303_shvedov.shvedov.getOutsideData', N'FN') IS NOT NULL  
454   DROP FUNCTION shvedov.getOutsidePostId; 
455GO
456
457CREATE FUNCTION shvedov.getOutsideData(@stateNumber varchar(9))  
458RETURNS time
459AS
460BEGIN
461	DECLARE getOutsidePostIdCursor CURSOR FOR
462	SELECT crossingTime
463	FROM KN_303_shvedov.shvedov.TravelInformation
464	WHERE stateNumberId = shvedov.getStateNumberId(@stateNumber) AND isInside = 0
465
466	OPEN getOutsidePostIdCursor
467	DECLARE @result time
468	FETCH NEXT FROM getOutsidePostIdCursor INTO @result
469	CLOSE getOutsidePostIdCursor
470	DEALLOCATE getOutsidePostIdCursor
471	RETURN @result
472END;
473GO
474
475IF OBJECT_ID (N'KN_303_shvedov.shvedov.getInsideData', N'FN') IS NOT NULL  
476   DROP FUNCTION shvedov.getOutsidePostId; 
477GO
478
479CREATE FUNCTION shvedov.getInsideData(@stateNumber varchar(9))  
480RETURNS time
481AS
482BEGIN
483	DECLARE getOutsidePostIdCursor CURSOR FOR
484	SELECT crossingTime
485	FROM KN_303_shvedov.shvedov.TravelInformation
486	WHERE stateNumberId = shvedov.getStateNumberId(@stateNumber) AND isInside = 1
487
488	OPEN getOutsidePostIdCursor
489	DECLARE @result time
490	FETCH NEXT FROM getOutsidePostIdCursor INTO @result
491	CLOSE getOutsidePostIdCursor
492	DEALLOCATE getOutsidePostIdCursor
493	RETURN @result
494END;
495GO
496
497IF OBJECT_ID (N'KN_303_shvedov.shvedov.isResident', N'FN') IS NOT NULL  
498   DROP FUNCTION shvedov.isResident; 
499GO
500
501CREATE FUNCTION shvedov.isResident(@stateNumberId int)  
502RETURNS bit
503AS
504BEGIN
505	if (shvedov.getRegionNumber(shvedov.getStateNumber(@stateNumberId)) = 66)
506		RETURN 1
507	RETURN 0
508END;
509GO
510
511IF OBJECT_ID (N'KN_303_shvedov.shvedov.isTransit', N'FN') IS NOT NULL  
512   DROP FUNCTION shvedov.isTransit; 
513GO
514
515CREATE FUNCTION shvedov.isTransit(@stateNumberId int)  
516RETURNS bit
517AS
518BEGIN
519	IF (shvedov.isResident(@stateNumberId) = 0)
520	BEGIN
521		DECLARE @insidePostId tinyint = shvedov.getInsidePostId(shvedov.getStateNumber(@stateNumberId))
522		DECLARE @outsidePostId tinyint = shvedov.getOutsidePostId(shvedov.getStateNumber(@stateNumberId))
523		if (@insidePostId != @outsidePostId)
524			RETURN 1 
525	END
526	RETURN 0
527END;
528GO
529
530IF OBJECT_ID (N'KN_303_shvedov.shvedov.isNonresident', N'FN') IS NOT NULL  
531   DROP FUNCTION shvedov.isNonresident; 
532GO
533
534CREATE FUNCTION shvedov.isNonresident(@stateNumberId int)  
535RETURNS bit
536AS
537BEGIN
538	IF (shvedov.isResident(@stateNumberId) = 0)
539	BEGIN
540		DECLARE @insidePostId tinyint = shvedov.getInsidePostId(shvedov.getStateNumber(@stateNumberId))
541		DECLARE @outsidePostId tinyint = shvedov.getOutsidePostId(shvedov.getStateNumber(@stateNumberId))
542		if (@insidePostId = @outsidePostId)
543			RETURN 1 
544	END
545	RETURN 0
546END;
547GO
548
549INSERT INTO KN_303_shvedov.shvedov.TravelInformationForInsert (stateNumber, isInside, postId, crossingTime) VALUES
550	('В725ХТ777', 1, 3, CONVERT(time, '0:5'))
551,('У130ЕА777', 0, 3, CONVERT(time, '0:10'))
552,('В771КВ777', 0, 1, CONVERT(time, '0:19'))
553,('Х424АХ66', 1, 1, CONVERT(time, '0:22'))
554,('Е700МВ77', 1, 1, CONVERT(time, '0:30'))
555,('Н677СО102', 1, 2, CONVERT(time, '0:39'))
556,('М642АА777', 1, 1, CONVERT(time, '0:41'))
557,('М233ХЕ66', 1, 2, CONVERT(time, '0:49'))
558,('Н649ХТ777', 1, 2, CONVERT(time, '0:50'))
559,('У671НО777', 0, 0, CONVERT(time, '0:59'))
560,('А587КЕ77', 0, 0, CONVERT(time, '1:1'))
561,('С886НЕ77', 1, 0, CONVERT(time, '1:10'))
562,('В771КВ777', 1, 1, CONVERT(time, '1:12'))
563,('У544ВН777', 0, 0, CONVERT(time, '1:17'))
564,('Е700МВ77', 1, 2, CONVERT(time, '1:22'))
565,('Н322ХЕ77', 1, 0, CONVERT(time, '1:23'))
566,('Е700МВ77', 1, 2, CONVERT(time, '1:26'))
567,('У730СВ177', 1, 3, CONVERT(time, '1:32'))
568,('О683РЕ66', 1, 0, CONVERT(time, '1:32'))
569,('С253УО66', 1, 0, CONVERT(time, '1:39'))
570,('Х891МУ102', 1, 3, CONVERT(time, '1:45'))
571,('М722СР66', 1, 0, CONVERT(time, '1:50'))
572,('М070РР77', 0, 2, CONVERT(time, '1:53'))
573,('К186ХМ66', 0, 2, CONVERT(time, '1:56'))
574,('М117ВР777', 1, 2, CONVERT(time, '2:3'))
575,('Н227СН02', 1, 2, CONVERT(time, '2:10'))
576,('Х329СВ66', 1, 1, CONVERT(time, '2:17'))
577,('М305ТА102', 1, 3, CONVERT(time, '2:20'))
578,('В771КВ777', 1, 2, CONVERT(time, '2:29'))
579,('С444РО77', 0, 1, CONVERT(time, '2:33'))
580,('С842ВМ66', 0, 3, CONVERT(time, '2:35'))
581,('Х891МУ102', 0, 0, CONVERT(time, '2:36'))
582,('У544ВН777', 1, 2, CONVERT(time, '2:43'))
583,('Х424АХ66', 0, 0, CONVERT(time, '2:46'))
584,('У544ВН777', 1, 1, CONVERT(time, '2:50'))
585,('В416МХ177', 0, 3, CONVERT(time, '2:53'))
586,('Х976РО77', 0, 3, CONVERT(time, '2:54'))
587,('Т732ЕС102', 1, 1, CONVERT(time, '2:59'))
588,('К787ОХ102', 0, 0, CONVERT(time, '3:4'))
589,('Е253ХХ66', 1, 0, CONVERT(time, '3:12'))
590,('О683РЕ66', 0, 1, CONVERT(time, '3:13'))
591,('О721СВ02', 0, 1, CONVERT(time, '3:22'))
592,('А631КН777', 1, 2, CONVERT(time, '3:23'))
593,('А593ЕХ66', 0, 1, CONVERT(time, '3:23'))
594,('Н044ВС66', 0, 0, CONVERT(time, '3:32'))
595,('О409ХК102', 1, 0, CONVERT(time, '3:36'))
596,('К485ОС177', 0, 3, CONVERT(time, '3:42'))
597,('Е665АТ177', 1, 1, CONVERT(time, '3:45'))
598,('О409ХК102', 0, 1, CONVERT(time, '3:49'))
599,('А393СЕ777', 0, 1, CONVERT(time, '3:55'))
600,('О409ХК102', 0, 3, CONVERT(time, '4:3'))
601,('Х329СВ66', 1, 2, CONVERT(time, '4:9'))
602,('О407УХ777', 0, 3, CONVERT(time, '4:15'))
603,('О601ВУ02', 1, 1, CONVERT(time, '4:21'))
604,('Т732ЕС102', 1, 1, CONVERT(time, '4:25'))
605,('Н544КТ77', 0, 2, CONVERT(time, '4:33'))
606,('Т034КВ02', 1, 2, CONVERT(time, '4:35'))
607,('М913ХН177', 0, 0, CONVERT(time, '4:38'))
608,('Т732ЕС102', 0, 3, CONVERT(time, '4:39'))
609,('Х329СВ66', 0, 0, CONVERT(time, '4:48'))
610,('Н592ХМ777', 1, 2, CONVERT(time, '4:54'))
611,('Е076ЕВ177', 1, 2, CONVERT(time, '5:3'))
612,('М913ХН177', 0, 0, CONVERT(time, '5:8'))
613,('В561ТН177', 1, 0, CONVERT(time, '5:16'))
614,('К361ВН102', 1, 2, CONVERT(time, '5:22'))
615,('Н544КТ77', 0, 1, CONVERT(time, '5:25'))
616,('Х891МУ102', 1, 0, CONVERT(time, '5:33'))
617,('Р292ХО66', 0, 1, CONVERT(time, '5:34'))
618,('У209СМ777', 1, 0, CONVERT(time, '5:38'))
619,('М225УУ777', 1, 1, CONVERT(time, '5:40'))
620,('Х329СВ66', 0, 2, CONVERT(time, '5:49'))
621,('К958МО66', 1, 1, CONVERT(time, '5:55'))
622,('Н649ХТ777', 1, 3, CONVERT(time, '5:57'))
623,('С416ТА102', 0, 3, CONVERT(time, '6:4'))
624,('А436ХО777', 0, 2, CONVERT(time, '6:5'))
625,('С444РО77', 0, 1, CONVERT(time, '6:7'))
626,('А593ЕХ66', 1, 1, CONVERT(time, '6:14'))
627,('К958МО66', 0, 2, CONVERT(time, '6:14'))
628,('К583НР02', 0, 2, CONVERT(time, '6:17'))
629,('А785ТН02', 0, 2, CONVERT(time, '6:20'))
630,('Х012РЕ102', 0, 2, CONVERT(time, '6:24'))
631,('А631КН777', 1, 0, CONVERT(time, '6:27'))
632,('С416ТА102', 1, 3, CONVERT(time, '6:36'))
633,('А587КЕ77', 1, 1, CONVERT(time, '6:42'))
634,('Н322ХЕ77', 1, 1, CONVERT(time, '6:49'))
635,('О906МВ777', 0, 2, CONVERT(time, '6:49'))
636,('Р983НМ777', 0, 2, CONVERT(time, '6:55'))
637,('Н255КК102', 0, 2, CONVERT(time, '6:55'))
638,('Р983НМ777', 0, 0, CONVERT(time, '7:3'))
639,('С842ВМ66', 1, 2, CONVERT(time, '7:10'))
640,('О135ТО177', 1, 1, CONVERT(time, '7:11'))
641,('С253УО66', 0, 1, CONVERT(time, '7:17'))
642,('О601ВУ02', 1, 0, CONVERT(time, '7:23'))
643,('Е700МВ77', 1, 1, CONVERT(time, '7:27'))
644,('С253УО66', 0, 1, CONVERT(time, '7:36'))
645,('Р544ОС777', 1, 1, CONVERT(time, '7:45'))
646,('С886НЕ77', 1, 0, CONVERT(time, '7:49'))
647,('В771КВ777', 0, 2, CONVERT(time, '7:55'))
648,('К485ОС177', 0, 1, CONVERT(time, '7:55'))
649,('К583НР02', 1, 3, CONVERT(time, '7:59'))
650,('В416МХ177', 0, 0, CONVERT(time, '8:6'))
651,('С416ТА102', 0, 0, CONVERT(time, '8:8'))
652,('У181АН66', 0, 0, CONVERT(time, '8:10'))
653,('Х703РН77', 0, 0, CONVERT(time, '8:12'))
654,('А194ОС66', 1, 1, CONVERT(time, '8:14'))
655,('К800МУ177', 0, 2, CONVERT(time, '8:15'))
656,('К485ОС177', 1, 0, CONVERT(time, '8:16'))
657,('Е253ХХ66', 0, 1, CONVERT(time, '8:16'))
658,('Х329СВ66', 0, 2, CONVERT(time, '8:21'))
659,('М786СС177', 1, 3, CONVERT(time, '8:28'))
660,('К186ХМ66', 0, 0, CONVERT(time, '8:37'))
661,('К485ОС177', 0, 2, CONVERT(time, '8:44'))
662,('М437МУ777', 1, 3, CONVERT(time, '8:46'))
663,('К186ХМ66', 0, 1, CONVERT(time, '8:50'))
664,('Х621ЕО66', 0, 0, CONVERT(time, '8:55'))
665,('Р931КВ177', 0, 2, CONVERT(time, '8:56'))
666,('К958МО66', 0, 2, CONVERT(time, '8:59'))
667,('У097МЕ102', 1, 1, CONVERT(time, '9:6'))
668,('К800МУ177', 1, 1, CONVERT(time, '9:9'))
669,('Е076ЕВ177', 0, 2, CONVERT(time, '9:12'))
670,('Х424АХ66', 0, 2, CONVERT(time, '9:20'))
671,('В561ТН177', 1, 2, CONVERT(time, '9:20'))
672,('А631КН777', 0, 0, CONVERT(time, '9:22'))
673,('Х621ЕО66', 1, 1, CONVERT(time, '9:29'))
674,('О601ВУ02', 0, 2, CONVERT(time, '9:36'))
675,('Х329СВ66', 0, 0, CONVERT(time, '9:40'))
676,('К223УС777', 1, 2, CONVERT(time, '9:46'))
677,('Т034КВ02', 0, 3, CONVERT(time, '9:47'))
678,('К787ОХ102', 1, 0, CONVERT(time, '9:47'))
679,('Н649ХТ777', 1, 0, CONVERT(time, '9:48'))
680,('У097МЕ102', 1, 3, CONVERT(time, '9:49'))
681,('Н544КТ77', 1, 3, CONVERT(time, '9:56'))
682,('Н771КН177', 1, 1, CONVERT(time, '10:5'))
683,('Х928УУ177', 1, 1, CONVERT(time, '10:13'))
684,('М722СР66', 0, 2, CONVERT(time, '10:14'))
685,('К800МУ177', 0, 1, CONVERT(time, '10:22'))
686,('В876ТЕ77', 1, 3, CONVERT(time, '10:31'))
687,('А371КМ02', 1, 3, CONVERT(time, '10:34'))
688,('С030ХЕ66', 1, 2, CONVERT(time, '10:41'))
689,('Е076ЕВ177', 1, 1, CONVERT(time, '10:50'))
690,('У671НО777', 0, 0, CONVERT(time, '10:54'))
691,('М070РР77', 1, 2, CONVERT(time, '10:59'))
692,('С886НЕ77', 1, 2, CONVERT(time, '11:4'))
693,('К583НР02', 0, 3, CONVERT(time, '11:6'))
694,('В416МХ177', 1, 0, CONVERT(time, '11:7'))
695,('Н028АЕ777', 0, 1, CONVERT(time, '11:7'))
696,('К787ОХ102', 1, 0, CONVERT(time, '11:16'))
697,('Н482НР177', 0, 3, CONVERT(time, '11:25'))
698,('М913ХН177', 1, 2, CONVERT(time, '11:33'))
699,('А936ТО66', 1, 3, CONVERT(time, '11:36'))
700,('А936ТО66', 1, 3, CONVERT(time, '11:41'))
701,('Х891МУ102', 1, 2, CONVERT(time, '11:43'))
702,('В416МХ177', 0, 0, CONVERT(time, '11:50'))
703,('Н482НР177', 0, 3, CONVERT(time, '11:53'))
704,('К485ОС177', 1, 0, CONVERT(time, '11:58'))
705,('Н482НР177', 0, 1, CONVERT(time, '12:2'))
706,('Н649ХТ777', 0, 1, CONVERT(time, '12:7'))
707,('Х891МУ102', 1, 1, CONVERT(time, '12:11'))
708,('М642АА777', 0, 3, CONVERT(time, '12:12'))
709,('С253УО66', 0, 0, CONVERT(time, '12:13'))
710,('Р292ХО66', 0, 0, CONVERT(time, '12:18'))
711,('А416ТТ102', 0, 2, CONVERT(time, '12:22'))
712,('Р983НМ777', 0, 1, CONVERT(time, '12:29'))
713,('У730СВ177', 0, 1, CONVERT(time, '12:32'))
714,('О683РЕ66', 0, 0, CONVERT(time, '12:35'))
715,('О721СВ02', 0, 2, CONVERT(time, '12:38'))
716,('В416МХ177', 0, 0, CONVERT(time, '12:46'))
717,('Н227СН02', 1, 0, CONVERT(time, '12:52'))
718,('Н592ХМ777', 1, 3, CONVERT(time, '12:58'))
719,('М225УУ777', 0, 0, CONVERT(time, '12:59'))
720,('М225УУ777', 0, 2, CONVERT(time, '13:8'))
721,('Х928УУ177', 1, 3, CONVERT(time, '13:9'))
722,('О601ВУ02', 0, 3, CONVERT(time, '13:18'))
723,('Х928УУ177', 0, 3, CONVERT(time, '13:23'))
724,('С253УО66', 1, 1, CONVERT(time, '13:32'))
725,('У097МЕ102', 1, 1, CONVERT(time, '13:38'))
726,('Н322ХЕ77', 1, 2, CONVERT(time, '13:44'))
727,('Н028АЕ777', 1, 1, CONVERT(time, '13:46'))
728,('Т391ВН02', 0, 3, CONVERT(time, '13:46'))
729,('В561ТН177', 1, 0, CONVERT(time, '13:53'))
730,('Х239АУ77', 1, 3, CONVERT(time, '14:2'))
731,('А194ОС66', 1, 3, CONVERT(time, '14:3'))
732,('В789АО77', 0, 0, CONVERT(time, '14:10'))
733,('А631КН777', 1, 1, CONVERT(time, '14:17'))
734,('Р931КВ177', 0, 2, CONVERT(time, '14:20'))
735,('Н592ХМ777', 1, 3, CONVERT(time, '14:23'))
736,('Х424АХ66', 0, 2, CONVERT(time, '14:23'))
737,('А785ТН02', 1, 3, CONVERT(time, '14:28'))
738,('Р544ОС777', 1, 3, CONVERT(time, '14:31'))
739,('М411СМ66', 1, 0, CONVERT(time, '14:34'))
740,('Т391ВН02', 1, 0, CONVERT(time, '14:36'))
741,('С253УО66', 1, 3, CONVERT(time, '14:40'))
742,('О407УХ777', 1, 1, CONVERT(time, '14:42'))
743,('К787ОХ102', 0, 0, CONVERT(time, '14:47'))
744,('К361ВН102', 0, 3, CONVERT(time, '14:51'))
745,('Е700МВ77', 0, 0, CONVERT(time, '14:51'))
746,('Р983НМ777', 1, 1, CONVERT(time, '15:0'))
747,('Х012РЕ102', 1, 3, CONVERT(time, '15:3'))
748,('В725ХТ777', 0, 1, CONVERT(time, '15:11'))
749,('О792НМ02', 1, 3, CONVERT(time, '15:13'))
750,('Н677СО102', 1, 1, CONVERT(time, '15:21'))
751,('О528ТХ66', 0, 0, CONVERT(time, '15:27'))
752,('Е253ХХ66', 0, 1, CONVERT(time, '15:30'))
753,('Н649ХТ777', 1, 0, CONVERT(time, '15:31'))
754,('Т391ВН02', 1, 1, CONVERT(time, '15:40'))
755,('Х424АХ66', 0, 1, CONVERT(time, '15:49'))
756,('А194ОС66', 0, 2, CONVERT(time, '15:57'))
757,('О528ТХ66', 0, 3, CONVERT(time, '16:2'))
758,('У130ЕА777', 1, 2, CONVERT(time, '16:5'))
759,('Н677СО102', 1, 2, CONVERT(time, '16:12'))
760,('Н592СЕ777', 0, 2, CONVERT(time, '16:16'))
761,('А436ХО777', 0, 3, CONVERT(time, '16:19'))
762,('У544ВН777', 1, 3, CONVERT(time, '16:25'))
763,('М722СР66', 0, 2, CONVERT(time, '16:26'))
764,('О288УВ102', 0, 2, CONVERT(time, '16:27'))
765,('М437МУ777', 0, 1, CONVERT(time, '16:35'))
766,('К787ОХ102', 0, 0, CONVERT(time, '16:43'))
767,('У544ВН777', 0, 1, CONVERT(time, '16:50'))
768,('М233ХЕ66', 1, 2, CONVERT(time, '16:56'))
769,('О135ТО177', 1, 0, CONVERT(time, '17:0'))
770,('О906МВ777', 0, 2, CONVERT(time, '17:1'))
771,('С842ВМ66', 1, 3, CONVERT(time, '17:1'))
772,('К361ВН102', 1, 2, CONVERT(time, '17:7'))
773,('К186ХМ66', 0, 0, CONVERT(time, '17:7'))
774,('С842ВМ66', 0, 0, CONVERT(time, '17:14'))
775,('Н255КК102', 0, 2, CONVERT(time, '17:23'))
776,('Т391ВН02', 1, 2, CONVERT(time, '17:28'))
777,('А785ТН02', 1, 3, CONVERT(time, '17:37'))
778,('С416ТА102', 0, 1, CONVERT(time, '17:43'))
779,('Т732ЕС102', 0, 0, CONVERT(time, '17:47'))
780,('У130ЕА777', 1, 3, CONVERT(time, '17:51'))
781,('М786СС177', 0, 2, CONVERT(time, '17:54'))
782,('А371КМ02', 1, 2, CONVERT(time, '18:2'))
783,('Т194АК777', 0, 2, CONVERT(time, '18:8'))
784,('К583НР02', 1, 2, CONVERT(time, '18:9'))
785,('М117ВР777', 1, 1, CONVERT(time, '18:17'))
786,('М411СМ66', 0, 2, CONVERT(time, '18:18'))
787,('М913ХН177', 0, 2, CONVERT(time, '18:22'))
788,('У097МЕ102', 1, 3, CONVERT(time, '18:31'))
789,('Н771КН177', 1, 0, CONVERT(time, '18:34'))
790,('А593ЕХ66', 0, 2, CONVERT(time, '18:42'))
791,('М786СС177', 1, 2, CONVERT(time, '18:43'))
792,('С842ВМ66', 0, 2, CONVERT(time, '18:47'))
793,('Т034КВ02', 0, 1, CONVERT(time, '18:47'))
794,('М722СР66', 1, 3, CONVERT(time, '18:47'))
795,('М642АА777', 0, 1, CONVERT(time, '18:48'))
796,('Р983НМ777', 1, 0, CONVERT(time, '18:48'))
797,('Х703РН77', 1, 0, CONVERT(time, '18:54'))
798,('М437МУ777', 0, 0, CONVERT(time, '18:55'))
799,('А371КМ02', 0, 3, CONVERT(time, '19:1'))
800,('А936ТО66', 1, 3, CONVERT(time, '19:10'))
801,('А936ТО66', 0, 3, CONVERT(time, '19:15'))
802,('Н028АЕ777', 0, 0, CONVERT(time, '19:22'))
803,('Х239АУ77', 1, 3, CONVERT(time, '19:25'))
804,('М233ХЕ66', 1, 3, CONVERT(time, '19:26'))
805,('В725ХТ777', 1, 1, CONVERT(time, '19:35'))
806,('А936ТО66', 0, 3, CONVERT(time, '19:39'))
807,('К958МО66', 0, 3, CONVERT(time, '19:39'))
808,('А785ТН02', 0, 0, CONVERT(time, '19:42'))
809,('Х703РН77', 0, 2, CONVERT(time, '19:51'))
810,('У770СС66', 1, 1, CONVERT(time, '19:58'))
811,('М642АА777', 1, 0, CONVERT(time, '20:2'))
812,('В771КВ777', 0, 2, CONVERT(time, '20:10'))
813,('Р544ОС777', 0, 1, CONVERT(time, '20:15'))
814,('О407УХ777', 0, 2, CONVERT(time, '20:23'))
815,('О906МВ777', 1, 0, CONVERT(time, '20:23'))
816,('О528ТХ66', 0, 2, CONVERT(time, '20:23'))
817,('Р931КВ177', 1, 3, CONVERT(time, '20:26'))
818,('К485ОС177', 1, 1, CONVERT(time, '20:28'))
819,('Р931КВ177', 0, 3, CONVERT(time, '20:31'))
820,('М411СМ66', 0, 3, CONVERT(time, '20:37'))
821,('О456ТО66', 1, 2, CONVERT(time, '20:37'))
822,('У209СМ777', 0, 3, CONVERT(time, '20:41'))
823,('О906МВ777', 1, 3, CONVERT(time, '20:49'))
824,('У544ВН777', 1, 3, CONVERT(time, '20:49'))
825,('Р931КВ177', 0, 1, CONVERT(time, '20:50'))
826,('Т034КВ02', 1, 2, CONVERT(time, '20:58'))
827,('Е950ЕР777', 0, 0, CONVERT(time, '21:2'))
828,('Р931КВ177', 1, 2, CONVERT(time, '21:4'))
829,('О792НМ02', 1, 3, CONVERT(time, '21:10'))
830,('О683РЕ66', 1, 3, CONVERT(time, '21:19'))
831,('М642АА777', 1, 3, CONVERT(time, '21:23'))
832,('В771КВ777', 0, 2, CONVERT(time, '21:25'))
833,('О683РЕ66', 0, 0, CONVERT(time, '21:30'))
834,('М411СМ66', 1, 2, CONVERT(time, '21:36'))
835,('Т034КВ02', 1, 3, CONVERT(time, '21:42'))
836,('Х891МУ102', 1, 0, CONVERT(time, '21:49'))
837,('Р544ОС777', 0, 0, CONVERT(time, '21:52'))
838,('Е665АТ177', 1, 1, CONVERT(time, '21:53'))
839,('Х928УУ177', 1, 1, CONVERT(time, '22:2'))
840,('М305ТА102', 1, 2, CONVERT(time, '22:4'))
841,('К787ОХ102', 0, 0, CONVERT(time, '22:7'))
842,('М305ТА102', 1, 2, CONVERT(time, '22:13'))
843,('О456ТО66', 1, 3, CONVERT(time, '22:19'))
844,('М722СР66', 1, 0, CONVERT(time, '22:25'))
845,('К485ОС177', 1, 1, CONVERT(time, '22:29'))
846,('Н044ВС66', 0, 3, CONVERT(time, '22:31'))
847,('Н592СЕ777', 0, 1, CONVERT(time, '22:35'))
848,('Х329СВ66', 0, 3, CONVERT(time, '22:38'))
849,('А785ТН02', 0, 3, CONVERT(time, '22:40'))
850,('Н592ХМ777', 1, 2, CONVERT(time, '22:47'))
851,('С253УО66', 0, 3, CONVERT(time, '22:51'))
852,('Х891МУ102', 0, 1, CONVERT(time, '22:51'))
853,('Е253ХХ66', 0, 2, CONVERT(time, '22:53'))
854,('Х239АУ77', 0, 3, CONVERT(time, '22:57'))
855,('М070РР77', 0, 0, CONVERT(time, '23:6'))
856,('Е937ХР777', 1, 0, CONVERT(time, '23:6'))
857,('А785ТН02', 1, 0, CONVERT(time, '23:12'))
858,('А936ТО66', 0, 1, CONVERT(time, '23:20'))
859,('О407УХ777', 0, 0, CONVERT(time, '23:20'))
860,('М225УУ777', 1, 1, CONVERT(time, '23:25'))
861,('Н677СО102', 0, 0, CONVERT(time, '23:32'))
862,('М411СМ66', 0, 0, CONVERT(time, '23:36'))
863,('Р544ОС777', 0, 1, CONVERT(time, '23:36'))
864,('К223УС777', 0, 2, CONVERT(time, '23:41'))
865,('В771КВ777', 0, 0, CONVERT(time, '23:47'))
866,('Н028АЕ777', 1, 1, CONVERT(time, '23:48'))
867,('У209СМ777', 0, 1, CONVERT(time, '23:51'))
868
869GO
870
871-- Иногородние автомобили
872CREATE VIEW NotResidentCars AS 
873SELECT stateNumber as 'Гос. номер',
874		r.regionName as 'Регион',
875		shvedov.getPostName(shvedov.getInsidePostId(stateNumber)) as 'Въезд через пост',
876		shvedov.getPostName(shvedov.getOutsidePostId(stateNumber)) as 'Выезд через пост'
877FROM KN_303_shvedov.shvedov.StateNumberInformation as sni,
878     KN_303_shvedov.shvedov.Regions as r
879WHERE  sni.regionNumber = r.regionNumber AND shvedov.isNonresident(id) = 1
880GO
881
882-- Транзитные автомобили
883CREATE VIEW TransitCars AS 
884SELECT stateNumber as 'Гос. номер',
885		r.regionName as 'Регион',
886		shvedov.getPostName(shvedov.getInsidePostId(stateNumber)) as 'Въезд через пост',
887		shvedov.getPostName(shvedov.getOutsidePostId(stateNumber)) as 'Выезд через пост'
888FROM KN_303_shvedov.shvedov.StateNumberInformation as sni,
889     KN_303_shvedov.shvedov.Regions as r
890WHERE  sni.regionNumber = r.regionNumber AND shvedov.isTransit(id) = 1
891GO
892
893--Местные атомобили
894CREATE VIEW LocalCars AS 
895SELECT stateNumber as 'Гос. номер',
896		r.regionName as 'Регион'
897FROM KN_303_shvedov.shvedov.StateNumberInformation as sni,
898     KN_303_shvedov.shvedov.Regions as r
899WHERE  r.regionNumber = '66'
900GO
901
902
903
904
905--Всего автомобилей
906CREATE VIEW AllCars AS
907SELECT count(*) AS 'Всего автомобилей'
908from KN_303_shvedov.shvedov.StateNumberInformation 
909GO
910
911--Количество прочих автомобилей
912CREATE VIEW AnotherCars AS
913	SELECT COUNT(*) AS 'Количество других автомобилей'
914	from KN_303_shvedov.shvedov.StateNumberInformation as sni,
915		KN_303_shvedov.shvedov.Regions as r
916	WHERE  sni.regionNumber = r.regionNumber AND shvedov.isResident(sni.id) != 1 AND shvedov.isTransit(sni.id) != 1 AND shvedov.isNonresident(sni.id) != 1
917GO
918
919--Все автомобили
920CREATE VIEW AllCarsData AS
921	SELECT sni.stateNumber AS 'Гос номер',
922		r.regionName AS 'Регион',
923		shvedov.getInsideData(sni.stateNumber) AS 'Время въезда',
924		shvedov.getOutsideData(sni.stateNumber) AS 'Время выезда'
925	from KN_303_shvedov.shvedov.StateNumberInformation as sni,
926		KN_303_shvedov.shvedov.Regions as r,
927		KN_303_shvedov.shvedov.TravelInformation ti
928	WHERE  sni.regionNumber = r.regionNumber AND ti.stateNumberId = sni.id
929GO
930
931select * from AllCarsData
932ORDER BY 'Регион';