b6k7wHPd

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, @Direction tinyint)
141RETURNS int
142AS
143BEGIN
144	DECLARE @lastDir int;
145	SET @lastDir=(SELECT a.Direction
146		FROM (SELECT TOP 1 *
147		FROM (SELECT TOP 2 *
148		FROM Shchukina.registration
149		WHERE auto_number=@auto_number
150		ORDER BY record_id DESC) as t
151		ORDER BY record_id) as a);
152	IF (@lastDir=@Direction)
153		RETURN(0);
154	RETURN(1);
155END
156GO
157 
158
159ALTER TABLE Shchukina.registration ADD
160CONSTRAINT CK_DirectionIsValid CHECK (Shchukina.DirectionIsValid(auto_number, Direction) = 1)
161GO
162
163 
164INSERT INTO Shchukina.registration
165  VALUES
166  (1, 1, 'E123KM77', '09:00:00', 1)
167  ,(2, 1, 'E123KM77', '10:00:00', 0)
168 
169  ,(3, 1, 'A789BC177', '11:00:00', 1)
170  ,(4, 4, 'A789BC177', '12:00:00', 0)
171 
172  ,(5, 4, 'P456AH96', '12:00:00', 0)
173    ,(6, 5, 'P456AH96', '13:00:00', 1)
174GO
175 
176SELECT * From Shchukina.registration
177 
178/**/
179/**/
180/**/
181/**/
182
183INSERT INTO Shchukina.registration
184  VALUES
185  (9, 1, 'E123KM377', '09:00:00', 1)
186  ,(10, 1, 'E123KM377', '10:00:00', 1)
187GO
188
189INSERT INTO Shchukina.registration
190  VALUES
191  (3, 1, 'A789BC177', '11:00:00', 1)
192  ,(4, 4, 'A789BC177', '12:00:00', 0)
193 
194  ,(5, 4, 'P456AH96', '12:00:00', 0)
195    ,(6, 5, 'P456AH96', '13:00:00', 1)
196GO
197 
198 
199-- Местные
200SELECT this.auto_number AS 'Местные', region.Name AS 'Регион',
201 SUBSTRING(CAST(this.RegistrationTime as nvarchar), 1, 5) AS 'Выезд',
202 SUBSTRING(CAST(that.RegistrationTime as nvarchar), 1, 5) AS 'Въезд'
203FROM Shchukina.registration this
204    INNER JOIN Shchukina.posts AS post
205            ON post.post_id = this.post_id
206    INNER JOIN Shchukina.regions AS region
207            ON region.Code = SUBSTRING(this.auto_number, 7, 3),
208    Shchukina.registration that
209WHERE this.auto_number = that.auto_number AND this.Direction <> that.Direction AND
210       SUBSTRING(this.auto_number,7, 3) = '96' AND this.record_id < that.record_id
211 
212 
213 
214 
215-- Иногородние
216SELECT this.AutoID AS 'Иногородние', region.Name AS 'Регион'
217FROM Shchukina.registration this
218    INNER JOIN Shchukina.posts AS Пост
219            ON Пост.post_id = this.post_id
220    INNER JOIN Shchukina.regions AS Регион
221            ON Регион.Code = SUBSTRING(this.auto_number, 7, 3),
222    Shchukina.registration that
223WHERE this.AutoID = that.AutoID AND this.Direction <> that.Direction AND
224            this.post_id = that.post_id AND this.record_id < that.record_id
225 
226-- Транзитные
227SELECT this.AutoID AS 'Транзитные', region.Name AS 'Регион'
228FROM Shchukina.registration this
229    INNER JOIN Shchukina.posts AS post
230            ON post.post_id = this.post_id
231    INNER JOIN Shchukina.regions AS region
232            ON region.Code = this.region_code,
233    Shchukina.registration that
234WHERE this.AutoID = that.AutoID AND this.Direction <> that.Direction AND
235            this.post_id <> that.post_id AND this.record_id < that.record_id
236            AND this.region_code <> 96