UgXrFS5p

1USE master
2GO
3 
4IF  EXISTS (
5    SELECT name
6        FROM sys.databases
7        WHERE name = N'#Shchukina'
8)
9ALTER DATABASE [#Shchukina] set single_user with rollback immediate
10GO
11 
12IF  EXISTS (
13    SELECT name
14        FROM sys.databases
15        WHERE name = N'#Shchukina'
16)
17DROP DATABASE [#Shchukina]
18GO
19 
20 
21CREATE DATABASE [#Shchukina]
22GO
23 
24USE [#Shchukina]
25GO
26 
27 
28IF EXISTS(
29  SELECT *
30    FROM sys.schemas
31   WHERE name = N'Shchukina'
32)
33 DROP SCHEMA Shchukina
34GO
35 
36CREATE SCHEMA Shchukina
37GO
38 
39 
40 IF OBJECT_ID('Shchukina.regions', 'U') IS NOT NULL
41  DROP TABLE  Shchukina.regions
42GO
43 
44  CREATE TABLE Shchukina.regions
45(
46    Code smallint NOT NULL,
47    Name nvarchar(50) NOT NULL
48    CONSTRAINT PK_region_id PRIMARY KEY (Code)
49)
50GO
51 
52INSERT INTO Shchukina.regions
53  VALUES
54 (66, N'Свердловская область')
55 ,(96, N'Свердловская область')
56 ,(196, N'Свердловская область')
57 ,(77, N'г. Москва')
58 ,(97, N'г. Москва')
59 ,(99, N'г. Москва')
60 ,(177, N'г. Москва')
61 ,(197, N'г. Москва')
62 ,(199, N'г. Москва')
63 ,(777, N'г. Москва')
64 ,(799, N'г. Москва')
65 
66GO
67 
68IF OBJECT_ID('Shchukina.posts', 'U') IS NOT NULL
69  DROP TABLE  Shchukina.posts
70GO
71 
72CREATE TABLE Shchukina.posts
73(
74    post_id tinyint NOT NULL,
75    Name nvarchar(20) NOT NULL
76    CONSTRAINT PK_post_id PRIMARY KEY (post_id)
77)
78GO
79 
80INSERT INTO Shchukina.posts
81  VALUES
82  (1,'Пост №1')
83  ,(2,'Пост №2')
84  ,(3,'Пост №3')
85  ,(4,'Пост №4')
86  ,(5,'Пост №5')
87GO
88 
89SELECT * From Shchukina.posts
90 
91/**/
92/**/
93/**/
94/**/
95 
96 
97IF OBJECT_ID('Shchukina.registration', 'U') IS NOT NULL
98  DROP TABLE  Shchukina.registration
99GO
100 
101CREATE TABLE Shchukina.registration
102(
103    record_id tinyint NOT NULL,
104    post_id tinyint NOT NULL,
105    auto_number nvarchar(9) NOT NULL,
106    RegistrationTime time NOT NULL,
107    Direction tinyint NOT NULL
108    CONSTRAINT PK_record_id PRIMARY KEY (record_id),
109    CONSTRAINT FK_post_id FOREIGN KEY (post_id)
110    REFERENCES Shchukina.posts (post_id),
111    CONSTRAINT CK_REGION CHECK (LEN(SUBSTRING(auto_number,7,3)) = 3 AND (
112        LEFT(CAST(SUBSTRING(auto_number,7, 3) as int),1) = 7 OR
113        LEFT(CAST(SUBSTRING(auto_number,7, 3) as int),1) = 2 OR
114        LEFT(CAST(SUBSTRING(auto_number,7, 3) as int), 1) = 1
115    ) OR LEN(CAST(SUBSTRING(auto_number,7, 3) as int)) != 3)
116   
117)
118GO
119 
120IF OBJECT_ID('Shchukina.number', 'TR') IS NOT NULL
121    DROP TRIGGER Shchukina.number
122GO
123 
124CREATE TRIGGER Shchukina.number
125ON Shchukina.registration
126AFTER INSERT
127AS
128IF EXISTS(
129    SELECT auto_number
130    FROM Shchukina.registration
131    WHERE SUBSTRING(auto_number, 1, 6) NOT LIKE '[УКЕНХВАРОСМТETYOPAHKXCBM][0-9][0-9][0-9][УКЕНХВАРОСМТETYOPAHKXCBM][УКЕНХВАРОСМТETYOPAHKXCBM]'
132)
133BEGIN
134    PRINT 'ОШИБКА, введен некорректный номер'
135    ROLLBACK TRANSACTION;
136END
137GO
138
139
140CREATE FUNCTION Shchukina.DirectionIsValid(@auto_number nvarchar(9), @Direction tinyint, @regtime time)
141RETURNS tinyint
142AS
143BEGIN
144    DECLARE @lastDir tinyint;
145	DECLARE @lastTime time;
146    SELECT @lastDir=r.Direction, @lastTime=r.RegistrationTime
147        FROM Shchukina.registration r
148        WHERE auto_number=@auto_number
149        ORDER BY record_id DESC
150		OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
151    IF (@lastDir=@Direction)
152        RETURN(0);
153	IF (@regtime < DATEADD(MINUTE, 1, @lastTime))
154		RETURN(0);
155    RETURN(1);
156END
157GO
158 
159 
160ALTER TABLE Shchukina.registration ADD
161CONSTRAINT CK_DirectionIsValid CHECK (Shchukina.DirectionIsValid(auto_number, Direction, RegistrationTime) = 1)
162GO
163 
164 
165INSERT INTO Shchukina.registration
166  VALUES
167  (1, 1, 'E123KM77', '09:00:00', 1)
168  ,(2, 1, 'E123KM77', '09:01:00', 0)
169 
170  ,(3, 1, 'A789BC177', '10:00:00', 1)
171  ,(4, 4, 'A789BC177', '10:10:00', 0)
172 
173  ,(5, 4, 'P456AH96', '11:00:00', 0)
174   ,(6, 5, 'P456AH96', '11:50:00', 1)
175
176  ,(7, 2, 'O223YT199', '11:55:00', 1)
177  ,(8, 2, 'O223YT199', '12:00:00', 0)
178 
179  ,(9, 1, 'C429BC97', '12:01:00', 1)
180  ,(10, 4, 'C429BC97', '12:10:00', 0)
181 
182  ,(11, 4, 'B777AH66', '12:10:00', 0)
183   ,(12, 5, 'B777AH66', '13:00:00', 1)
184
185  ,(13, 2, 'K989YT99', '13:05:00', 1)
186  ,(14, 2, 'K989YT99', '13:15:00', 0)
187 
188  ,(15, 1, 'H111BC97', '13:30:00', 1)
189  ,(16, 4, 'H111BC97', '14:10:00', 0)
190 
191  ,(17, 4, 'X342AH196', '15:10:00', 0)
192   ,(18, 5, 'X342AH196', '15:20:00', 1)
193GO
194 
195SELECT * From Shchukina.registration
196 
197/**/
198/**/
199/**/
200/**/
201 
202 
203-- Местные
204SELECT this.auto_number AS 'Местные', region.Name AS 'Регион',
205 SUBSTRING(CAST(this.RegistrationTime as nvarchar), 1, 5) AS 'Выезд',
206 SUBSTRING(CAST(that.RegistrationTime as nvarchar), 1, 5) AS 'Въезд'
207FROM Shchukina.registration this
208    INNER JOIN Shchukina.posts AS post
209            ON post.post_id = this.post_id
210    INNER JOIN Shchukina.regions AS region
211            ON region.Code = SUBSTRING(this.auto_number, 7, 3),
212    Shchukina.registration that
213WHERE this.auto_number = that.auto_number AND this.Direction=0 AND that.Direction=1 AND
214       SUBSTRING(this.auto_number,7, 3) = '96' AND this.record_id < that.record_id
215 
216 
217 
218 
219-- Иногородние
220SELECT this.auto_number AS 'Иногородние', region.Name AS 'Регион',
221SUBSTRING(CAST(this.RegistrationTime as nvarchar), 1, 5) AS 'Въезд',
222 SUBSTRING(CAST(that.RegistrationTime as nvarchar), 1, 5) AS 'Выезд'
223FROM Shchukina.registration this
224    INNER JOIN Shchukina.posts AS post
225            ON post.post_id = this.post_id
226    INNER JOIN Shchukina.regions AS region
227            ON region.Code = SUBSTRING(this.auto_number, 7, 3),
228    Shchukina.registration that
229WHERE this.auto_number = that.auto_number AND this.Direction=1 AND that.Direction=0 AND
230            this.post_id = that.post_id AND this.record_id < that.record_id
231 
232-- Транзитные
233SELECT this.auto_number AS 'Транзитные', region.Name AS 'Регион',
234SUBSTRING(CAST(this.RegistrationTime as nvarchar), 1, 5) AS 'Въезд',
235 SUBSTRING(CAST(that.RegistrationTime as nvarchar), 1, 5) AS 'Выезд'
236FROM Shchukina.registration this
237    INNER JOIN Shchukina.posts AS post
238            ON post.post_id = this.post_id
239    INNER JOIN Shchukina.regions AS region
240            ON region.Code = SUBSTRING(this.auto_number, 7, 3),
241    Shchukina.registration that
242WHERE this.auto_number = that.auto_number AND this.Direction=1 AND that.Direction=0 AND
243            this.post_id <> that.post_id AND this.record_id < that.record_id
244            AND SUBSTRING(this.auto_number,7, 3) <> 96