· 6 years ago · Nov 27, 2019, 05:34 AM
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 (shvedov.isStateNumberCorrect(@stateNumber) = 1)
353 BEGIN
354 IF NOT EXISTS(SELECT id
355 FROM KN_303_shvedov.shvedov.StateNumberInformation
356 WHERE stateNumber = UPPER(@stateNumber))
357 BEGIN
358 INSERT INTO KN_303_shvedov.shvedov.StateNumberInformation (stateNumber) VALUES
359 (UPPER(@stateNumber))
360 END
361
362 DECLARE tmpTableCursor CURSOR FOR
363 SELECT TOP 1 id
364 FROM KN_303_shvedov.shvedov.StateNumberInformation
365 WHERE stateNumber = UPPER(@stateNumber)
366 OPEN tmpTableCursor
367 DECLARE @stateNumberId INT
368 FETCH NEXT FROM tmpTableCursor INTO @stateNumberId
369 CLOSE tmpTableCursor
370 DEALLOCATE tmpTableCursor
371 IF (shvedov.isCarInCity(@stateNumber) IS NULL OR shvedov.isCarInCity(@stateNumber) <> @isInside
372 AND datediff(mi, shvedov.getLastTravelTime(@stateNumber), @crossingTime) >= 5)
373 BEGIN
374 INSERT INTO KN_303_shvedov.shvedov.TravelInformation (stateNumberId, isInside, postId, crossingTime) VALUES
375 (@stateNumberId, @isInside, @postId, @crossingTime)
376 END
377 END
378 FETCH NEXT FROM travleCursor INTO @stateNumber, @isInside, @postId, @crossingTime
379 END
380
381 CLOSE travleCursor
382 DEALLOCATE travleCursor
383END
384GO
385
386
387
388
389IF OBJECT_ID (N'KN_303_shvedov.shvedov.getInsidePostId', N'FN') IS NOT NULL
390 DROP FUNCTION shvedov.getInsidePostId;
391GO
392
393CREATE FUNCTION shvedov.getInsidePostId(@stateNumber varchar(9))
394RETURNS tinyint
395AS
396BEGIN
397 DECLARE getInsidePostIdCursor CURSOR FOR
398 SELECT postId
399 FROM KN_303_shvedov.shvedov.TravelInformation
400 WHERE stateNumberId = shvedov.getStateNumberId(@stateNumber) AND isInside = 1
401
402 OPEN getInsidePostIdCursor
403 DECLARE @result tinyint
404 FETCH NEXT FROM getInsidePostIdCursor INTO @result
405 CLOSE getInsidePostIdCursor
406 DEALLOCATE getInsidePostIdCursor
407 RETURN @result
408END;
409GO
410
411IF OBJECT_ID (N'KN_303_shvedov.shvedov.getPostName', N'FN') IS NOT NULL
412 DROP FUNCTION shvedov.getPostName;
413GO
414
415CREATE FUNCTION shvedov.getPostName(@postId tinyint)
416RETURNS varchar(30)
417AS
418BEGIN
419 DECLARE getInsidePostIdCursor CURSOR FOR
420 SELECT postName
421 FROM KN_303_shvedov.shvedov.Post
422 WHERE postId = @postId
423
424 OPEN getInsidePostIdCursor
425 DECLARE @result varchar(30)
426 FETCH NEXT FROM getInsidePostIdCursor INTO @result
427 CLOSE getInsidePostIdCursor
428 DEALLOCATE getInsidePostIdCursor
429 RETURN @result
430END;
431GO
432
433
434IF OBJECT_ID (N'KN_303_shvedov.shvedov.getOutsidePostId', N'FN') IS NOT NULL
435 DROP FUNCTION shvedov.getOutsidePostId;
436GO
437
438CREATE FUNCTION shvedov.getOutsidePostId(@stateNumber varchar(9))
439RETURNS tinyint
440AS
441BEGIN
442 DECLARE getOutsidePostIdCursor CURSOR FOR
443 SELECT postId
444 FROM KN_303_shvedov.shvedov.TravelInformation
445 WHERE stateNumberId = shvedov.getStateNumberId(@stateNumber) AND isInside = 0
446
447 OPEN getOutsidePostIdCursor
448 DECLARE @result tinyint
449 FETCH NEXT FROM getOutsidePostIdCursor INTO @result
450 CLOSE getOutsidePostIdCursor
451 DEALLOCATE getOutsidePostIdCursor
452 RETURN @result
453END;
454GO
455
456IF OBJECT_ID (N'KN_303_shvedov.shvedov.getOutsideData', N'FN') IS NOT NULL
457 DROP FUNCTION shvedov.getOutsidePostId;
458GO
459
460CREATE FUNCTION shvedov.getOutsideData(@stateNumber varchar(9))
461RETURNS time
462AS
463BEGIN
464 DECLARE getOutsidePostIdCursor CURSOR FOR
465 SELECT crossingTime
466 FROM KN_303_shvedov.shvedov.TravelInformation
467 WHERE stateNumberId = shvedov.getStateNumberId(@stateNumber) AND isInside = 0
468
469 OPEN getOutsidePostIdCursor
470 DECLARE @result time
471 FETCH NEXT FROM getOutsidePostIdCursor INTO @result
472 CLOSE getOutsidePostIdCursor
473 DEALLOCATE getOutsidePostIdCursor
474 RETURN @result
475END;
476GO
477
478IF OBJECT_ID (N'KN_303_shvedov.shvedov.getInsideData', N'FN') IS NOT NULL
479 DROP FUNCTION shvedov.getOutsidePostId;
480GO
481
482CREATE FUNCTION shvedov.getInsideData(@stateNumber varchar(9))
483RETURNS time
484AS
485BEGIN
486 DECLARE getOutsidePostIdCursor CURSOR FOR
487 SELECT crossingTime
488 FROM KN_303_shvedov.shvedov.TravelInformation
489 WHERE stateNumberId = shvedov.getStateNumberId(@stateNumber) AND isInside = 1
490
491 OPEN getOutsidePostIdCursor
492 DECLARE @result time
493 FETCH NEXT FROM getOutsidePostIdCursor INTO @result
494 CLOSE getOutsidePostIdCursor
495 DEALLOCATE getOutsidePostIdCursor
496 RETURN @result
497END;
498GO
499
500IF OBJECT_ID (N'KN_303_shvedov.shvedov.isResident', N'FN') IS NOT NULL
501 DROP FUNCTION shvedov.isResident;
502GO
503
504CREATE FUNCTION shvedov.isResident(@stateNumberId int)
505RETURNS bit
506AS
507BEGIN
508 if (shvedov.getRegionNumber(shvedov.getStateNumber(@stateNumberId)) = 66)
509 RETURN 1
510 RETURN 0
511END;
512GO
513
514IF OBJECT_ID (N'KN_303_shvedov.shvedov.isTransit', N'FN') IS NOT NULL
515 DROP FUNCTION shvedov.isTransit;
516GO
517
518CREATE FUNCTION shvedov.isTransit(@stateNumberId int)
519RETURNS bit
520AS
521BEGIN
522 IF (shvedov.isResident(@stateNumberId) = 0)
523 BEGIN
524 DECLARE @insidePostId tinyint = shvedov.getInsidePostId(shvedov.getStateNumber(@stateNumberId))
525 DECLARE @outsidePostId tinyint = shvedov.getOutsidePostId(shvedov.getStateNumber(@stateNumberId))
526 if (@insidePostId != @outsidePostId)
527 RETURN 1
528 END
529 RETURN 0
530END;
531GO
532
533IF OBJECT_ID (N'KN_303_shvedov.shvedov.isNonresident', N'FN') IS NOT NULL
534 DROP FUNCTION shvedov.isNonresident;
535GO
536
537CREATE FUNCTION shvedov.isNonresident(@stateNumberId int)
538RETURNS bit
539AS
540BEGIN
541 IF (shvedov.isResident(@stateNumberId) = 0)
542 BEGIN
543 DECLARE @insidePostId tinyint = shvedov.getInsidePostId(shvedov.getStateNumber(@stateNumberId))
544 DECLARE @outsidePostId tinyint = shvedov.getOutsidePostId(shvedov.getStateNumber(@stateNumberId))
545 if (@insidePostId = @outsidePostId)
546 RETURN 1
547 END
548 RETURN 0
549END;
550GO
551
552INSERT INTO KN_303_shvedov.shvedov.TravelInformationForInsert (stateNumber, isInside, postId, crossingTime) VALUES
553 ('67', 1, 3, CONVERT(time, '0:5'))
554
555GO
556
557-- Иногородние автомобили
558CREATE VIEW NotResidentCars AS
559SELECT stateNumber as 'Гос. номер',
560 r.regionName as 'Регион',
561 shvedov.getPostName(shvedov.getInsidePostId(stateNumber)) as 'Въезд через пост',
562 shvedov.getPostName(shvedov.getOutsidePostId(stateNumber)) as 'Выезд через пост'
563FROM KN_303_shvedov.shvedov.StateNumberInformation as sni,
564 KN_303_shvedov.shvedov.Regions as r
565WHERE sni.regionNumber = r.regionNumber AND shvedov.isNonresident(id) = 1
566GO
567
568-- Транзитные автомобили
569CREATE VIEW TransitCars AS
570SELECT stateNumber as 'Гос. номер',
571 r.regionName as 'Регион',
572 shvedov.getPostName(shvedov.getInsidePostId(stateNumber)) as 'Въезд через пост',
573 shvedov.getPostName(shvedov.getOutsidePostId(stateNumber)) as 'Выезд через пост'
574FROM KN_303_shvedov.shvedov.StateNumberInformation as sni,
575 KN_303_shvedov.shvedov.Regions as r
576WHERE sni.regionNumber = r.regionNumber AND shvedov.isTransit(id) = 1
577GO
578
579--Местные атомобили
580CREATE VIEW LocalCars AS
581SELECT stateNumber as 'Гос. номер',
582 r.regionName as 'Регион'
583FROM KN_303_shvedov.shvedov.StateNumberInformation as sni,
584 KN_303_shvedov.shvedov.Regions as r
585WHERE r.regionNumber = '66'
586GO
587
588
589
590
591--Всего автомобилей
592CREATE VIEW AllCars AS
593SELECT count(*) AS 'Всего автомобилей'
594from KN_303_shvedov.shvedov.StateNumberInformation
595GO
596
597--Количество прочих автомобилей
598CREATE VIEW AnotherCars AS
599 SELECT COUNT(*) AS 'Количество других автомобилей'
600 from KN_303_shvedov.shvedov.StateNumberInformation as sni,
601 KN_303_shvedov.shvedov.Regions as r
602 WHERE sni.regionNumber = r.regionNumber AND shvedov.isResident(sni.id) != 1 AND shvedov.isTransit(sni.id) != 1 AND shvedov.isNonresident(sni.id) != 1
603GO
604
605--Все автомобили
606CREATE VIEW AllCarsData AS
607 SELECT sni.stateNumber AS 'Гос номер',
608 r.regionName AS 'Регион',
609 shvedov.getInsideData(sni.stateNumber) AS 'Время въезда',
610 shvedov.getOutsideData(sni.stateNumber) AS 'Время выезда'
611 from KN_303_shvedov.shvedov.StateNumberInformation as sni,
612 KN_303_shvedov.shvedov.Regions as r,
613 KN_303_shvedov.shvedov.TravelInformation ti
614 WHERE sni.regionNumber = r.regionNumber AND ti.stateNumberId = sni.id
615GO
616
617select * from AllCarsData
618ORDER BY 'Регион'