7aLeQUxn

· 8 years ago · Nov 30, 2017, 05:34 PM
1USE master 
2GO
3
4IF EXISTS (
5	SELECT	name 
6	FROM	sys.databases 
7	WHERE	name = N'Merzlyakov' )
8ALTER DATABASE [Merzlyakov] set single_user with rollback immediate
9GO
10
11IF EXISTS (
12	SELECT	name 
13	FROM	sys.databases 
14	WHERE	name = N'Merzlyakov' )
15DROP DATABASE [Merzlyakov]
16GO
17
18CREATE DATABASE [Merzlyakov]
19GO
20
21USE [Merzlyakov]
22GO
23
24IF EXISTS(
25  SELECT *
26    FROM sys.schemas
27   WHERE name = N'Transport'
28)
29 DROP SCHEMA Transport
30GO
31 
32CREATE SCHEMA Transport
33GO
34
35IF OBJECT_ID('Transport.Regions', 'U') IS NOT NULL
36	DROP TABLE  Transport.Regions
37GO
38
39CREATE TABLE Transport.Regions (
40	id	int PRIMARY KEY,
41	name NVARCHAR(30),
42)
43GO
44
45INSERT INTO Transport.Regions(id, name) VALUES
46	(66, N'Ð¡Ð²ÐµÑ€Ð´Ð»Ð¾Ð²ÑÐºÐ°Ñ Ð¾Ð±Ð»Ð°ÑÑ‚ÑŒ')
47	,(01, N'Ð ÐµÑÐ¿ÑƒÐ±Ð»Ð¸ÐºÐ° ÐÐ´Ñ‹Ð³ÐµÑ')
48	,(74, N'Ð§ÐµÐ»ÑÐ±Ð¸Ð½ÑÐºÐ°Ñ Ð¾Ð±Ð»Ð°ÑÑ‚ÑŒ')
49	,(77, N'ÐœÐ¾ÑÐºÐ²Ð°')
50	,(59, N'ÐŸÐµÑ€Ð¼ÑÐºÐ¸Ð¹ ÐºÑ€Ð°Ð¹')
51GO
52
53IF OBJECT_ID('Transport.RCodes', 'U') IS NOT NULL
54	DROP TABLE  Transport.RCodes
55GO
56
57CREATE TABLE Transport.RCodes (
58	code int PRIMARY KEY,
59	region_id int FOREIGN KEY (region_id) REFERENCES Transport.Regions(id) ON UPDATE CASCADE,
60)
61GO
62
63INSERT INTO Transport.RCodes(code, region_id) VALUES
64	(66, 66)
65	,(96, 66)
66	,(196, 66)
67	,(166, 66)
68	,(74, 74)
69	,(174, 74)
70	,(1, 1)
71	,(101, 1)
72	,(777, 77)
73	,(77, 77)
74	,(177, 77)
75	,(59, 59)
76	,(159, 59)
77
78GO
79
80IF OBJECT_ID('Transport.Posts', 'U') IS NOT NULL
81	DROP TABLE  Transport.Posts
82GO
83
84CREATE TABLE Transport.Posts (
85	id int PRIMARY KEY IDENTITY(1,1),
86	name NVARCHAR(20),
87)
88GO
89
90INSERT INTO Transport.Posts(name) VALUES
91	('Ð¡ÐµÐ²ÐµÑ€')
92	,('Ð®Ð³')
93	,('Ð—Ð°Ð¿Ð°Ð´')
94	,('Ð’Ð¾ÑÑ‚Ð¾Ðº')
95GO
96
97IF OBJECT_ID('Transport.Records', 'U') IS NOT NULL
98	DROP TABLE  Transport.Records
99GO
100
101CREATE TABLE Transport.Records (
102	post int FOREIGN KEY (post) REFERENCES Transport.Posts(id) ON UPDATE CASCADE,
103	number VARCHAR(9) CHECK(
104		(number like '[ÐÐ’Ð•ÐšÐœÐÐžÐ Ð¡Ð¢Ð£Ð¥][0-9][0-9][0-9][ÐÐ’Ð•ÐšÐœÐÐžÐ Ð¡Ð¢Ð£Ð¥][ÐÐ’Ð•ÐšÐœÐÐžÐ Ð¡Ð¢Ð£Ð¥][0-9][0-9]'
105		or number like '[ÐÐ’Ð•ÐšÐœÐÐžÐ Ð¡Ð¢Ð£Ð¥][0-9][0-9][0-9][ÐÐ’Ð•ÐšÐœÐÐžÐ Ð¡Ð¢Ð£Ð¥][ÐÐ’Ð•ÐšÐœÐÐžÐ Ð¡Ð¢Ð£Ð¥][127][0-9][0-9]')
106		and number not like N'%000%' and number not like N'%00'),
107	--region AS CAST(SUBSTRING(number, 7, 3) AS int),
108	curtime TIME(0)	NOT NULL,
109	direction bit NOT NULL,--  0-Ð²ÑŠÐµÐ·Ð´, 1-Ð²Ñ‹ÐµÐ·Ð´
110	--CONSTRAINT FK_region FOREIGN KEY (region) REFERENCES Transport.RCodes(code) ON UPDATE CASCADE,
111)
112GO
113
114CREATE TRIGGER CheckDirection
115ON Transport.Records
116AFTER INSERT
117AS
118BEGIN
119    DECLARE @insertedTime TIME(0)
120	DECLARE @insertedNumber VARCHAR(9)
121    DECLARE @insertedDirection bit
122    SET @insertedTime = (SELECT TOP 1 curtime
123                           FROM inserted
124                           ORDER BY curtime DESC)
125    SET @insertedNumber = (SELECT TOP 1 number
126                           FROM inserted
127                           ORDER BY curtime DESC)
128    SET @insertedDirection = (SELECT TOP 1 direction
129                              FROM inserted
130                              ORDER BY curtime DESC)
131    DECLARE @lastCapturedAutoDirection bit
132    SET @lastCapturedAutoDirection = (SELECT TOP 1 Direction
133                                  FROM Transport.Records
134                                  WHERE Transport.Records.number = @insertedNumber and
135                                   Transport.Records.curtime < @insertedTime
136                                  ORDER BY curtime DESC)
137    IF @insertedDirection = @lastCapturedAutoDirection
138    BEGIN
139        PRINT 'ÐÐ²Ñ‚Ð¾Ð¼Ð¾Ð±Ð¸Ð»ÑŒ Ð½Ðµ Ð¼Ð¾Ð¶ÐµÑ‚ Ð½ÐµÑÐºÐ¾Ð»ÑŒÐºÐ¾ Ñ€Ð°Ð· Ð¿Ð¾Ð´Ñ€ÑÐ´ Ð²ÑŠÐµÑ…Ð°Ñ‚ÑŒ/Ð²Ñ‹ÐµÑ…Ð°Ñ‚ÑŒ'
140        ROLLBACK TRANSACTION
141    END
142END
143GO
144
145--ÐœÐµÑÑ‚Ð½Ñ‹Ð¹
146INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð001ÐÐ96', N'10:34:45', 1)
147INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (2, 'Ð001ÐÐ96', N'10:34:47', 0)
148INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð021ÐÐ96', N'10:34:48', 1)
149INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (2, 'Ð021ÐÐ96', N'10:34:49', 0)
150--Ð¢Ñ€Ð°Ð½Ð·Ð¸Ñ‚Ð½Ñ‹Ð¹
151INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (3, 'Ð’222Ð’Ð’174', N'10:34:49', 0)
152INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð’222Ð’Ð’174', N'10:34:50', 1)
153--Ð˜Ð½Ð¾Ð³Ð¾Ñ€Ð¾Ð´Ð½Ð¸Ð¹
154INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð¡333Ð¡Ð¡174', N'10:34:55', 0)
155INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð¡333Ð¡Ð¡174', N'10:34:57', 1)
156--ÐŸÑ€Ð¾Ñ‡Ð¸Ðµ
157INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð•444Ð•Ð•59', N'10:35:02', 0)
158INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð•444Ð•Ð•59', N'10:35:03', 1)
159
160INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð¥007Ð¥Ð¥777', N'10:36:00', 1)
161GO
162
163----ÐŸÑ€Ð¾Ð²ÐµÑ€ÐºÐ° Ð½Ð¾Ð¼ÐµÑ€Ð°
164--INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð', N'10:34:45', 0)
165--INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð001Ð©Ð96', N'10:34:45', 1)
166--INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð000ÐÐ96', N'10:34:45', 0)
167--INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð001Ð¡Ð496', N'10:34:45', 0)
168--INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð001Ð¡Ð100', N'10:34:45', 1)
169--INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð001ÐšÐ00', N'10:34:45', 0)
170
171----ÐŸÑ€Ð¾Ð²ÐµÑ€ÐºÐ° Ð½Ð° Ð¿Ð¾Ð²Ñ‚Ð¾Ñ€Ð½Ñ‹Ð¹ Ð²ÑŠÐµÐ·Ð´/Ð²Ñ‹ÐµÐ·Ð´
172--INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð001ÐÐ96', N'10:34:45', 0)
173--INSERT INTO Transport.Records (post, number, curtime, direction) VALUES (1, 'Ð001ÐÐ96', N'10:34:46', 0)
174--GO
175
176
177----------Ð´Ð²Ð° Ð²ÑÐ¿Ð¾Ð¼Ð¾Ð³Ð°Ñ‚ÐµÐ»ÑŒÐ½Ñ‹Ñ… Ð¿Ñ€ÐµÐ´ÑÑ‚Ð°Ð²Ð»ÐµÐ½Ð¸Ñ Ð´Ð»Ñ Ð²Ñ‹Ñ‡Ð¸ÑÐ»ÐµÐ½Ð¸Ñ Ñ‚Ð¸Ð¿Ð¾Ð² Ð°Ð²Ñ‚Ð¾-------------
178
179IF OBJECT_ID (N'Transport.AuxiliaryTableForPAR', N'U') IS NOT NULL
180    DROP VIEW Transport.AuxiliaryTableForPAR
181GO
182 
183CREATE VIEW Transport.AuxiliaryTableForPAR
184AS
185SELECT number AS carNumber, MAX(curtime) AS penultTime
186FROM Transport.Records r
187WHERE curtime != (select max(curtime) from Transport.Records e where e.number=r.number)
188GROUP BY number
189GO
190
191SELECT * FROM Transport.AuxiliaryTableForPAR
192GO
193
194IF OBJECT_ID (N'Transport.AuxiliaryTableForLAR', N'U') IS NOT NULL
195    DROP VIEW Transport.AuxiliaryTableForLAR
196GO
197 
198CREATE VIEW Transport.AuxiliaryTableForLAR
199AS
200SELECT DISTINCT number AS carNumber, MAX(curtime) AS lastTime
201FROM Transport.Records
202GROUP BY number
203GO
204
205SELECT * FROM Transport.AuxiliaryTableForLAR
206GO
207
208IF OBJECT_ID (N'Transport.PenultAutosRegistration', N'U') IS NOT NULL
209    DROP VIEW Transport.PenultAutosRegistration
210GO
211 
212CREATE VIEW Transport.PenultAutosRegistration
213AS
214SELECT number AS carNumber, post, direction, curtime AS penultTime
215FROM Transport.AuxiliaryTableForPAR
216	INNER JOIN Transport.Records ON AuxiliaryTableForPAR.carNumber=Records.number and AuxiliaryTableForPAR.penultTime=Records.curtime
217GROUP BY number, post, direction, curtime
218GO
219
220IF OBJECT_ID (N'Transport.LastAutosRegistration', N'U') IS NOT NULL
221    DROP VIEW Transport.LastAutosRegistration
222GO
223 
224CREATE VIEW Transport.LastAutosRegistration
225AS
226SELECT DISTINCT number AS carNumber, post, Direction, curtime AS lastTime
227FROM Transport.AuxiliaryTableForLAR
228	INNER JOIN Transport.Records ON AuxiliaryTableForLAR.carNumber=Records.number and AuxiliaryTableForLAR.lastTime=Records.curtime
229GROUP BY number, post, direction, curtime
230GO
231 
232 DECLARE @home int
233 SET @home=66
234go
235----Ð¢Ñ€Ð°Ð½Ð·Ð¸Ñ‚Ð½Ñ‹Ðµ----
236IF OBJECT_ID (N'Transport.TransitionalAutos', N'U') IS NOT NULL
237    DROP VIEW Transport.TransitionalAutos
238GO
239 
240CREATE VIEW Transport.TransitionalAutos
241AS
242SELECT DISTINCT number AS 'ÐÐ¾Ð¼ÐµÑ€'
243                --, region AS 'ÐÐ¾Ð¼ÐµÑ€ Ñ€ÐµÐ³Ð¸Ð¾Ð½Ð°'
244                , reg.name AS 'Ð ÐµÐ³Ð¸Ð¾Ð½'
245                , par.penultTime AS 'Ð’Ñ€ÐµÐ¼Ñ Ð¿Ð¾ÑÐ»ÐµÐ´Ð½ÐµÐ³Ð¾ Ð²ÑŠÐµÐ·Ð´Ð°'
246                , lar.lastTime AS 'Ð’Ñ€ÐµÐ¼Ñ Ð¿Ð¾ÑÐ»ÐµÐ´Ð½ÐµÐ³Ð¾ Ð²Ñ‹ÐµÐ·Ð´Ð°'
247FROM Transport.Records AS rec 
248		INNER JOIN Transport.RCodes AS rc ON rc.code=CAST(SUBSTRING(number, 7, 3) AS int)
249		INNER JOIN Transport.Regions AS reg ON rc.region_id = reg.id 
250		INNER JOIN Transport.PenultAutosRegistration AS par ON number = par.carNumber 
251		INNER JOIN Transport.LastAutosRegistration AS lar ON number = lar.carNumber
252WHERE penultTime < lastTime
253      AND par.Direction = 0
254      AND lar.Direction = 1
255      AND par.post != lar.post
256      AND rc.region_id != 66
257GO
258
259----ÐœÐµÑÑ‚Ð½Ñ‹Ðµ----
260IF OBJECT_ID (N'Transport.LocalAutos', N'U') IS NOT NULL
261    DROP VIEW Transport.LocalAutos
262GO
263 
264CREATE VIEW Transport.LocalAutos
265AS
266SELECT DISTINCT number AS 'ÐÐ¾Ð¼ÐµÑ€'
267                --, region AS 'ÐÐ¾Ð¼ÐµÑ€ Ñ€ÐµÐ³Ð¸Ð¾Ð½Ð°'
268                , reg.name AS 'Ð ÐµÐ³Ð¸Ð¾Ð½'
269                , par.penultTime AS 'Ð’Ñ€ÐµÐ¼Ñ Ð¿Ð¾ÑÐ»ÐµÐ´Ð½ÐµÐ³Ð¾ Ð²ÑŠÐµÐ·Ð´Ð°'
270                , lar.lastTime AS 'Ð’Ñ€ÐµÐ¼Ñ Ð¿Ð¾ÑÐ»ÐµÐ´Ð½ÐµÐ³Ð¾ Ð²Ñ‹ÐµÐ·Ð´Ð°'
271FROM Transport.Records AS rec 
272		INNER JOIN Transport.RCodes AS rc ON rc.code=CAST(SUBSTRING(number, 7, 3) AS int)
273		INNER JOIN Transport.Regions AS reg ON rc.region_id = reg.id 
274		INNER JOIN Transport.PenultAutosRegistration AS par ON number = par.carNumber 
275		INNER JOIN Transport.LastAutosRegistration AS lar ON number = lar.carNumber
276WHERE penultTime < lastTime
277      AND par.Direction = 1
278      AND lar.Direction = 0
279      AND rc.region_id = 66
280GO
281
282----Ð˜Ð½Ð¾Ð³Ð¾Ñ€Ð¾Ð´Ð½Ð¸Ðµ----
283IF OBJECT_ID (N'Transport.NonresidentAutos', N'U') IS NOT NULL
284    DROP VIEW Transport.NonresidentAutos
285GO
286 
287CREATE VIEW Transport.NonresidentAutos
288AS
289SELECT DISTINCT number AS 'ÐÐ¾Ð¼ÐµÑ€'
290                --, region AS 'ÐÐ¾Ð¼ÐµÑ€ Ñ€ÐµÐ³Ð¸Ð¾Ð½Ð°'
291                , reg.name AS 'Ð ÐµÐ³Ð¸Ð¾Ð½'
292                , par.penultTime AS 'Ð’Ñ€ÐµÐ¼Ñ Ð¿Ð¾ÑÐ»ÐµÐ´Ð½ÐµÐ³Ð¾ Ð²ÑŠÐµÐ·Ð´Ð°'
293                , lar.lastTime AS 'Ð’Ñ€ÐµÐ¼Ñ Ð¿Ð¾ÑÐ»ÐµÐ´Ð½ÐµÐ³Ð¾ Ð²Ñ‹ÐµÐ·Ð´Ð°'
294FROM Transport.Records AS rec 
295		INNER JOIN Transport.RCodes AS rc ON rc.code=CAST(SUBSTRING(number, 7, 3) AS int)
296		INNER JOIN Transport.Regions AS reg ON rc.region_id = reg.id 
297		INNER JOIN Transport.PenultAutosRegistration AS par ON number = par.carNumber 
298		INNER JOIN Transport.LastAutosRegistration AS lar ON number = lar.carNumber
299WHERE penultTime < lastTime
300      AND par.Direction = 0
301      AND lar.Direction = 1
302      AND par.post = lar.post
303GO
304
305----ÐŸÑ€Ð¾Ñ‡Ð¸Ðµ----
306IF OBJECT_ID (N'Transport.OtherAutos', N'U') IS NOT NULL
307    DROP VIEW Transport.OtherAutos
308GO
309 
310CREATE VIEW Transport.OtherAutos
311AS
312SELECT DISTINCT rec.number AS 'ÐÐ¾Ð¼ÐµÑ€'
313                --, region AS 'ÐÐ¾Ð¼ÐµÑ€ Ñ€ÐµÐ³Ð¸Ð¾Ð½Ð°'
314                , reg.name AS 'Ð ÐµÐ³Ð¸Ð¾Ð½Ð°'
315                , lar.lastTime AS 'Ð’Ñ€ÐµÐ¼Ñ Ð¿Ð¾ÑÐ»ÐµÐ´Ð½ÐµÐ¹ Ð°ÐºÑ‚Ð¸Ð²Ð½Ð¾ÑÑ‚Ð¸' 
316FROM Transport.Records AS rec
317		INNER JOIN Transport.RCodes AS rc ON rc.code=CAST(SUBSTRING(number, 7, 3) AS int)
318		INNER JOIN Transport.Regions AS reg ON rc.region_id = reg.id 
319		INNER JOIN Transport.LastAutosRegistration AS lar ON number = lar.carNumber
320WHERE NOT EXISTS (SELECT number
321				FROM Transport.LocalAutos AS LA 
322				WHERE rec.number = LA.ÐÐ¾Ð¼ÐµÑ€) AND
323		NOT EXISTS (SELECT number
324				FROM Transport.NonresidentAutos AS NA 
325				WHERE rec.number = NA.ÐÐ¾Ð¼ÐµÑ€) AND
326		NOT EXISTS (SELECT number
327				FROM Transport.TransitionalAutos AS TA 
328				WHERE rec.number = TA.ÐÐ¾Ð¼ÐµÑ€) 
329GO
330
331--Ð¢Ñ€Ð°Ð½Ð·Ð¸Ñ‚Ð½Ñ‹Ðµ
332SELECT * FROM Transport.TransitionalAutos
333GO
334--ÐœÐµÑ‚ÑÐ½Ñ‹Ðµ
335SELECT * FROM Transport.LocalAutos
336GO
337--Ð˜Ð½Ð¾Ð³Ð¾Ñ€Ð¾Ð´Ð½Ð¸Ðµ
338SELECT * FROM Transport.NonresidentAutos
339GO
340--ÐŸÑ€Ð¾Ñ‡Ð¸Ðµ
341SELECT * FROM Transport.OtherAutos
342GO
343
344SELECT * FROM Transport.LastAutosRegistration
345GO
346
347SELECT * FROM Transport.PenultAutosRegistration
348GO
349
350SELECT * FROM Transport.Records
351GO