1s5a5rsX

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', '10:00:00', 0)
169 
170  ,(3, 1, 'A789BC177', '11:00:00', 1)
171  ,(4, 4, 'A789BC177', '12:00:00', 0)
172 
173  ,(5, 4, 'P456AH96', '12:00:00', 0)
174   ,(6, 5, 'P456AH96', '13:00:00', 1)
175GO
176 
177SELECT * From Shchukina.registration
178 
179/**/
180/**/
181/**/
182/**/
183 
184 
185-- Местные
186SELECT this.auto_number AS 'Местные', region.Name AS 'Регион',
187 SUBSTRING(CAST(this.RegistrationTime as nvarchar), 1, 5) AS 'Выезд',
188 SUBSTRING(CAST(that.RegistrationTime as nvarchar), 1, 5) AS 'Въезд'
189FROM Shchukina.registration this
190    INNER JOIN Shchukina.posts AS post
191            ON post.post_id = this.post_id
192    INNER JOIN Shchukina.regions AS region
193            ON region.Code = SUBSTRING(this.auto_number, 7, 3),
194    Shchukina.registration that
195WHERE this.auto_number = that.auto_number AND this.Direction=0 AND that.Direction=1 AND
196       SUBSTRING(this.auto_number,7, 3) = '96' AND this.record_id < that.record_id
197 
198 
199 
200 
201-- Иногородние
202SELECT this.auto_number AS 'Иногородние', region.Name AS 'Регион',
203SUBSTRING(CAST(this.RegistrationTime as nvarchar), 1, 5) AS 'Въезд',
204 SUBSTRING(CAST(that.RegistrationTime as nvarchar), 1, 5) AS 'Выезд'
205FROM Shchukina.registration this
206    INNER JOIN Shchukina.posts AS post
207            ON post.post_id = this.post_id
208    INNER JOIN Shchukina.regions AS region
209            ON region.Code = SUBSTRING(this.auto_number, 7, 3),
210    Shchukina.registration that
211WHERE this.auto_number = that.auto_number AND this.Direction=1 AND that.Direction=0 AND
212            this.post_id = that.post_id AND this.record_id < that.record_id
213 
214-- Транзитные
215SELECT this.auto_number AS 'Транзитные', region.Name AS 'Регион',
216SUBSTRING(CAST(this.RegistrationTime as nvarchar), 1, 5) AS 'Въезд',
217 SUBSTRING(CAST(that.RegistrationTime as nvarchar), 1, 5) AS 'Выезд'
218FROM Shchukina.registration this
219    INNER JOIN Shchukina.posts AS post
220            ON post.post_id = this.post_id
221    INNER JOIN Shchukina.regions AS region
222            ON region.Code = SUBSTRING(this.auto_number, 7, 3),
223    Shchukina.registration that
224WHERE this.auto_number = that.auto_number AND this.Direction=1 AND that.Direction=0 AND
225            this.post_id <> that.post_id AND this.record_id < that.record_id
226            AND SUBSTRING(this.auto_number,7, 3) <> 96