· 8 years ago · Nov 17, 2017, 09:32 AM
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
96IF OBJECT_ID('Shchukina.registration', 'U') IS NOT NULL
97 DROP TABLE Shchukina.registration
98GO
99
100CREATE TABLE Shchukina.registration
101(
102 record_id tinyint NOT NULL,
103 post_id tinyint NOT NULL,
104 auto_number nvarchar(9) NOT NULL,
105 RegistrationTime time NOT NULL,
106 Direction tinyint NOT NULL
107 CONSTRAINT PK_record_id PRIMARY KEY (record_id),
108 CONSTRAINT FK_post_id FOREIGN KEY (post_id)
109 REFERENCES Shchukina.posts (post_id),
110 CONSTRAINT CHK_REGION CHECK (LEN(SUBSTRING(auto_number,7,3)) = 3 AND (
111 LEFT(CAST(SUBSTRING(auto_number,7, 3) as int),1) = 7 OR
112 LEFT(CAST(SUBSTRING(auto_number,7, 3) as int),1) = 2 OR
113 LEFT(CAST(SUBSTRING(auto_number,7, 3) as int), 1) = 1
114 ) OR LEN(CAST(SUBSTRING(auto_number,7, 3) as int)) != 3)
115)
116GO
117
118IF OBJECT_ID('Shchukina.number', 'TR') IS NOT NULL
119 DROP TRIGGER Shchukina.number
120GO
121
122CREATE TRIGGER Shchukina.number
123ON Shchukina.registration
124AFTER INSERT
125AS
126IF EXISTS(
127 SELECT auto_number
128 FROM Shchukina.registration
129 WHERE SUBSTRING(auto_number, 1, 6) NOT LIKE '[УКЕÐХВÐРОСМТETYOPAHKXCBM][0-9][0-9][0-9][УКЕÐХВÐРОСМТETYOPAHKXCBM][УКЕÐХВÐРОСМТETYOPAHKXCBM]'
130)
131BEGIN
132 PRINT 'ОШИБКÐ, введен некорректный номер'
133 ROLLBACK TRANSACTION;
134END
135GO
136
137CREATE FUNCTION Shchukina.DirectionIsValid(@auto_nimber nvarchar, @post_id tinyint, @Direction tinyint)
138RETURNS bit
139AS
140BEGIN
141DECLARE @sameDirsCount int;
142SET @sameDirsCount=(SELECT COUNT(*)
143FROM lab2.Passes
144WHERE CarId=@CarId AND DirectionId=@DirectionId);
145IF @sameDirsCount=1
146RETURN(1);
147DECLARE @lastIn int;
148SET @lastIn=(SELECT TOP 1
149t.passId
150FROM (SELECT TOP 2 Passes.PassId
151FROM lab2.Passes
152WHERE CarId=@CarId AND DirectionId=@DirectionId
153ORDER BY PassId DESC) as t
154ORDER BY PassId);
155DECLARE @outs int;
156SET @outs=(SELECT COUNT(*)
157FROM lab2.Passes
158WHERE CarId=@CarId AND NOT DirectionId=@DirectionId);
159IF (@lastIn>@outs)
160RETURN(0);
161RETURN(1);
162IF ABS(@sameDirsCount-@outs)>=2
163RETURN(0);
164RETURN(1);
165END
166GO
167
168IF OBJECT_ID('Shchukina.time', 'TR') IS NOT NULL
169 DROP TRIGGER Shchukina.time
170GO
171
172CREATE TRIGGER Shchukina.time
173ON Shchukina.registration
174AFTER INSERT
175AS
176IF EXISTS(
177 SELECT Direction
178 FROM Shchukina.registration
179 WHERE
180)
181BEGIN
182 PRINT 'ОШИБКÐ, введен некорректный номер'
183 ROLLBACK TRANSACTION;
184END
185GO
186
187INSERT INTO Shchukina.registration
188 VALUES
189 (1, 1, 'E123KM77', '09:00:00', 1)
190 ,(2, 1, 'E123KM77', '10:00:00', 0)
191
192 ,(3, 1, 'A789BC177', '11:00:00', 1)
193 ,(4, 4, 'A789BC177', '12:00:00', 0)
194
195 ,(5, 4, 'P456AH96', '12:00:00', 0)
196 ,(6, 5, 'P456AH96', '13:00:00', 1)
197GO
198
199SELECT * From Shchukina.registration
200
201/**/
202/**/
203/**/
204/**/
205
206
207-- МеÑтные
208SELECT this.auto_number AS 'МеÑтные', region.Name AS 'Регион',
209 SUBSTRING(CAST(this.RegistrationTime as nvarchar), 1, 5) AS 'Выезд',
210 SUBSTRING(CAST(that.RegistrationTime as nvarchar), 1, 5) AS 'Въезд'
211FROM Shchukina.registration this
212 INNER JOIN Shchukina.posts AS post
213 ON post.post_id = this.post_id
214 INNER JOIN Shchukina.regions AS region
215 ON region.Code = SUBSTRING(this.auto_number, 7, 3),
216 Shchukina.registration that
217WHERE this.auto_number = that.auto_number AND this.Direction <> that.Direction AND
218 SUBSTRING(this.auto_number,7, 3) = '96' AND this.record_id < that.record_id
219
220
221
222
223-- Иногородние
224SELECT this.AutoID AS 'Иногородние', region.Name AS 'Регион'
225FROM Shchukina.registration this
226 INNER JOIN Shchukina.posts AS ПоÑÑ‚
227 ON ПоÑÑ‚.post_id = this.post_id
228 INNER JOIN Shchukina.regions AS Регион
229 ON Регион.Code = SUBSTRING(this.auto_number, 7, 3),
230 Shchukina.registration that
231WHERE this.AutoID = that.AutoID AND this.Direction <> that.Direction AND
232 this.post_id = that.post_id AND this.record_id < that.record_id
233
234-- Транзитные
235SELECT this.AutoID AS 'Транзитные', region.Name 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 = this.region_code,
241 Shchukina.registration that
242WHERE this.AutoID = that.AutoID AND this.Direction <> that.Direction AND
243 this.post_id <> that.post_id AND this.record_id < that.record_id
244 AND this.region_code <> 96