C6mxgXLS

1USE master
2GO
3 
4IF EXISTS (
5    SELECT name
6    FROM sys.DATABASES
7    WHERE name = N'GusarovVadim'
8)
9ALTER DATABASE [GusarovVadim] SET single_user WITH ROLLBACK immediate
10GO
11 
12IF EXISTS (
13    SELECT name
14    FROM sys.DATABASES
15    WHERE name = N'GusarovVadim'
16)
17DROP DATABASE [GusarovVadim]
18GO
19 
20CREATE DATABASE [GusarovVadim]
21GO
22 
23 
24USE [GusarovVadim]
25GO
26 
27IF OBJECT_ID('GusarovVadim.Regions', 'U') IS NOT NULL
28    DROP TABLE GusarovVadim.Regions
29GO
30 
31CREATE TABLE Regions (
32    id TINYINT,
33    name NVARCHAR(30),
34    CONSTRAINT PK_region_id PRIMARY KEY (id)
35)
36GO
37 
38IF OBJECT_ID('GusarovVadim.RCodes', 'U') IS NOT NULL
39    DROP TABLE GusarovVadim.RCodes
40GO
41 
42CREATE TABLE RCodes (
43    region_id tinyint,
44    code INT,
45    CONSTRAINT PK_regio_id PRIMARY KEY (code),
46    CONSTRAINT FK_regio_id FOREIGN KEY (region_id) REFERENCES Regions(id) ON UPDATE CASCADE,
47)
48GO
49 
50IF OBJECT_ID('GusarovVadim.Categories', 'U') IS NOT NULL
51    DROP TABLE GusarovVadim.Categories
52GO
53 
54CREATE TABLE Categories (
55    id TINYINT,
56    name NVARCHAR(20),
57    CONSTRAINT PK_color_id PRIMARY KEY (id)
58)
59GO
60 
61IF OBJECT_ID('GusarovVadim.Posts', 'U') IS NOT NULL
62    DROP TABLE GusarovVadim.Posts
63GO
64 
65CREATE TABLE Posts (
66    id tinyint NOT NULL,
67    CONSTRAINT PK_post_id PRIMARY KEY (id),
68)
69GO
70 
71IF OBJECT_ID ( 'GusarovVadim.CorrectNumber', 'F' ) IS NOT NULL
72    DROP FUNCTION GusarovVadim.CorrectNumber
73GO
74 
75CREATE FUNCTION CorrectNumber (@num NVARCHAR(30), @rcode INT)
76RETURNS tinyint
77AS
78BEGIN
79    IF (((UPPER(@num) LIKE '[АВЕКМНОРСТУХ][0-9][0-9][0-9][АВЕКМНОРСТУХ][АВЕКМНОРСТУХ]' AND @rcode LIKE '[0-9][0-9]')
80        OR (UPPER(@num) LIKE '[АВЕКМНОРСТУХ][0-9][0-9][0-9][АВЕКМНОРСТУХ][АВЕКМНОРСТУХ]' AND @rcode LIKE '[127][0-9][0-9]')) 
81		AND SUBSTRING(@num, 2,3) NOT LIKE '000' AND @rcode > 0)
82        RETURN 1
83    RETURN 0
84END
85GO
86 
87IF OBJECT_ID ( 'GusarovVadim.CorrectTime', 'F' ) IS NOT NULL
88    DROP FUNCTION GusarovVadim.CorrectTime
89GO
90 
91CREATE FUNCTION CorrectTime (@DATE DATETIME, @dir VARCHAR(1), @auto_id NVARCHAR(9), @id tinyint)
92RETURNS tinyint
93AS
94BEGIN
95    IF((SELECT COUNT(1) FROM Records WHERE Records.id != @id AND Records.auto_id = @auto_id) = 0)
96        RETURN 1
97 
98    IF (EXISTS(
99        SELECT * FROM Records
100        INNER JOIN
101            (SELECT MAX(catchtime) AS max_date, auto_id FROM Records
102                WHERE catchtime < @DATE AND @auto_id = auto_id
103                GROUP BY auto_id) r
104        ON Records.catchtime = r.max_date AND Records.auto_id = r.auto_id
105        WHERE @dir != Records.direction
106    ))
107        RETURN 1
108    RETURN 0
109END
110GO
111 
112IF OBJECT_ID('GusarovVadim.Autos', 'U') IS NOT NULL
113    DROP TABLE GusarovVadim.Autos
114GO
115 
116CREATE TABLE Autos (
117    id_a NVARCHAR(9),
118    category_id tinyint NOT NULL,
119    NUMBER NVARCHAR(7) NOT NULL,
120    rcode INT NOT NULL,
121    family NVARCHAR(30) NOT NULL,
122    CONSTRAINT PK_auto_id PRIMARY KEY (id_a),
123    CONSTRAINT FK_regi_id FOREIGN KEY (rcode) REFERENCES RCodes(code) ON UPDATE CASCADE,
124    CONSTRAINT FK_color_id FOREIGN KEY (category_id) REFERENCES Categories(id) ON UPDATE CASCADE,
125)
126GO
127 
128IF OBJECT_ID ( 'GusarovVadim.valid_number', 'TR' ) IS NOT NULL
129    DROP FUNCTION _GusarovVadim.on_insert_rec
130GO
131 
132CREATE TRIGGER valid_number ON Autos FOR INSERT AS
133BEGIN
134    IF EXISTS (
135    SELECT NUMBER, rcode
136    FROM inserted WHERE dbo.CorrectNumber(NUMBER, rcode) = 0
137    )
138    BEGIN
139        PRINT 'Некорректный номер'
140        ROLLBACK TRANSACTION
141    END
142END
143GO
144 
145IF OBJECT_ID('GusarovVadim.Records', 'U') IS NOT NULL
146    DROP TABLE  GusarovVadim.Records
147GO
148 
149CREATE TABLE Records (
150    id tinyint NOT NULL,
151    post_id tinyint NOT NULL,
152    auto_id NVARCHAR(9) NOT NULL,
153    catchtime DATETIME NOT NULL,
154    direction CHAR  NOT NULL,
155    CHECK (dbo.CorrectTime(catchtime, direction, auto_id, id) = 1),
156    CHECK (direction = '\' or direction = '/'),
157   CONSTRAINT FK_post_id FOREIGN KEY (post_id) REFERENCES Posts(id) ON UPDATE CASCADE,
158   CONSTRAINT FK_auto_id FOREIGN KEY (auto_id) REFERENCES Autos(id_a) ON UPDATE CASCADE,
159)
160GO
161 
162IF OBJECT_ID ( 'GusarovVadim.GetAutoType', 'F' ) IS NOT NULL
163DROP FUNCTION GusarovVadim.GetAutoType
164GO
165 
166CREATE FUNCTION GetAutoType (
167   @fromPost tinyint,
168   @toPost tinyint,
169   @fromReg tinyint
170)
171RETURNS VARCHAR(30)
172AS
173BEGIN
174   if (@fromPost != @toPost AND @fromReg != 1)
175       RETURN 'Транзитный'
176   if (@fromPost = @toPost AND @fromReg != 1)
177       RETURN 'Иногородний'
178   if (@fromReg = 1)
179       RETURN 'Местный'
180   RETURN 'Прочий'
181END
182GO
183 
184INSERT INTO Regions(id, name) VALUES
185(1, N'Ленинградская область'),
186(2, N'Московская область'),
187(3, N'Чукотский автономный округ'),
188(4, N'Челябинская область'),
189(5, N'Ульяновская область')
190GO
191 
192INSERT INTO RCodes(region_id, code) VALUES
193(1, 47), (1, 147), (2, 50), (2, 150),
194(3, 87), (3, 187), (4, 74), (4, 174),
195(5, 73), (5, 173)
196GO
197 
198INSERT INTO Categories(id, name) VALUES
199(1, N'Легковая'),
200(2, N'Автобус'),
201(3, N'Легковая с прицепом'),
202(4, N'Грузовик'),
203(5, N'Маршрутка')
204GO
205 
206INSERT INTO Posts(id) VALUES
207(1),(2),(3),(4),(5)
208GO
209 
210INSERT INTO Autos (id_a, category_id, number, rcode, family) VALUES
211   ('а953ву147', 1, 'а953ву', 147, N'Иванов'),
212   ('х377ав74', 1, 'х377ав', 74, N'Сергеев'),
213   ('о122ка173', 1, 'о122ка', 173, N'Солдатенко'),
214   ('с454тт50', 1, 'с454тт', 50, N'Нагаев'),
215   ('к665мо187', 1, 'к665мо', 187, N'Гусаров'),
216   ('С323РА150', 2, 'С323РА', 150, N'Полтавец'),
217   ('О149РУ47', 2, 'О149РУ', 47, N'Шмаков'),
218   ('К458ЕК50', 2, 'К458ЕК', 50, N'Пушкин'),
219   ('О384СА73', 2, 'О384СА', 73, N'Аминов'),
220   ('н758ет187', 3, 'н758ет', 187, N'Котов'),
221   ('т929ек47', 3, 'т929ек', 47, N'Конышев'),
222   ('в587ек147', 3, 'в587ек', 147, N'Бакшеев'),
223   ('р285ет150', 3, 'р285ет', 150, N'Пожарский'),
224   ('к492ав173', 3, 'к492ав', 173, N'Носов'),
225   ('н588мк174', 4, 'н588мк', 174, N'Булыгина'),
226   ('Р235ОН87', 4, 'Р235ОН', 87, N'Вишняков'),
227   ('А521ВЕ74', 4, 'А521ВЕ', 74, N'Краснов'),
228   ('К458ЕВ173', 4, 'К458ЕВ', 173, N'Чукчин'),
229   ('С312ОН150', 5, 'С312ОН', 150, N'Попов'),
230   ('М914ОН87', 5, 'М914ОН', 87, N'Карамышева')
231GO
232 
233INSERT INTO Records (id, post_id, auto_id, catchtime, direction) VALUES
234   (1, 1, 'а953ву147', N'20120618 10:54:48', N'\'),
235   (2, 2, 'а953ву147', N'20120618 11:34:09', N'/'),
236   (3, 2, 'х377ав74', N'20120618 13:18:35', N'/'),
237   (4, 1, 'х377ав74', N'20120618 15:12:01', N'\'),
238   (5, 3, 'о122ка173', N'20120618 11:12:58', N'\'),
239   (6, 4, 'о122ка173', N'20120618 12:35:12', N'/'),
240   (7, 4, 'с454тт50', N'20120618 13:44:18', N'/'),
241   (8, 3, 'с454тт50', N'20120618 19:43:13', N'\'),
242   (9, 5, 'к665мо187', N'20120618 16:41:54', N'\'),
243   (10, 1, 'к665мо187', N'20120618 20:24:16', N'/'),
244   (11, 1, 'С323РА150', N'20120618 09:53:51', N'/'),
245   (12, 1, 'С323РА150', N'20120618 11:12:13', N'\'),
246   (13, 2, 'О149РУ47', N'20120618 08:41:01', N'\'),
247   (14, 2, 'О149РУ47', N'20120618 14:03:02', N'/'),
248   (15, 3, 'К458ЕК50', N'20120618 12:34:08', N'/'),
249   (16, 3, 'К458ЕК50', N'20120618 22:23:41', N'\'),
250   (17, 4, 'О384СА73', N'20120618 01:51:21', N'\'),
251   (18, 5, 'О384СА73', N'20120618 08:22:42', N'/'),
252   (19, 5, 'н758ет187', N'20120618 07:01:02', N'/'),
253   (20, 1, 'н758ет187', N'20120618 22:02:54', N'\'),
254   (21, 2, 'т929ек47', N'20120618 04:13:29', N'\'),
255   (22, 2, 'т929ек47', N'20120618 11:12:13', N'/'),
256   (23, 1, 'в587ек147', N'20120618 07:14:17', N'/'),
257   (24, 2, 'в587ек147', N'20120618 15:12:19', N'\'),
258   (25, 1, 'р285ет150', N'20120618 06:12:28', N'\'),
259   (26, 3, 'р285ет150', N'20120618 22:23:32', N'/'),
260   (27, 1, 'к492ав173', N'20120618 08:32:12', N'/'),
261   (28, 3, 'к492ав173', N'20120618 14:02:02', N'\'),
262   (29, 5, 'н588мк174', N'20120618 06:23:19', N'/'),
263   (30, 1, 'н588мк174', N'20120618 10:17:34', N'\'),
264   (31, 2, 'Р235ОН87', N'20120618 17:12:51', N'\'),
265   (32, 1, 'Р235ОН87', N'20120618 21:52:35', N'/'),
266   (33, 2, 'А521ВЕ74', N'20120618 11:21:32', N'/'),
267   (34, 1, 'А521ВЕ74', N'20120618 17:13:19', N'\'),
268   (35, 3, 'К458ЕВ173', N'20120618 06:15:23', N'\'),
269   (36, 3, 'К458ЕВ173', N'20120618 11:17:47', N'/'),
270   (37, 1, 'С312ОН150', N'20120618 07:43:28', N'/'),
271   (38, 4, 'С312ОН150', N'20120618 11:05:01', N'\'),
272   (39, 5, 'М914ОН87', N'20120618 12:23:12', N'\'),
273   (40, 4, 'М914ОН87', N'20120618 13:02:10', N'/')
274   --(39, 4, 'М914ОН87', N'20120618 03:02:10', N'/'),
275   --(40, 5, 'М914ОН87', N'20120618 11:23:12', N'/')
276GO
277 
278--SELECT a.family as "Владельцы легковых авто"
279--FROM Autos a
280--INNER JOIN Categories c
281--  ON c.id = a.category_id
282--WHERE c.name = 'Легковая'
283--GO
284 
285--SELECT a.family as "Автовладельцы",
286--  r.name as "Регион",
287--  a.number as "Номер"
288--FROM Autos a
289--INNER JOIN RCodes rc
290--ON rc.code = a.rcode
291--INNER JOIN Regions r
292--ON r.id = rc.region_id
293--WHERE r.name = 'Московская область'
294--GO
295 
296--DECLARE @Temp TABLE
297--(
298--  fr_city tinyint,
299--  to_city tinyint,
300--  fr_post tinyint,
301--  to_post tinyint
302--);
303 
304--INSERT INTO
305--  @Temp
306--SELECT rr3.region_id,
307--  rr1.post_id,
308--  rr1.post_id,
309--  rr2.post_id
310--FROM Autos a
311--JOIN Records rr1
312--  ON rr1.auto_id = a.id AND rr1.direction = N'\'
313--JOIN Records rr2
314--  ON rr2.auto_id = a.id AND rr2.direction = N'/'
315--JOIN RCodes rr3
316--  ON rr3.code = a.rcode
317--GROUP BY a.family, a.number, rr1.post_id, rr2.post_id, rr3.region_id
318 
319--SELECT dbo.GetAutoType(fr_post, to_post, fr_city) as 'Тип', COUNT(*) as 'Количество' FROM @Temp
320--GROUP BY dbo.GetAutoType(fr_post, to_post, fr_city)
321--GO
322 
323DECLARE @Temp TABLE
324(
325   auto_id NVARCHAR(9),
326   fr_city tinyint,
327   to_city tinyint,
328   fr_post tinyint,
329   to_post tinyint
330);
331 
332INSERT INTO
333@Temp
334SELECT a.id_a,
335   rr3.region_id,
336   rr1.post_id,
337   rr1.post_id,
338   rr2.post_id
339FROM Autos a
340JOIN Records rr1
341   ON rr1.auto_id = a.id_a AND rr1.direction = N'\'
342JOIN Records rr2
343   ON rr2.auto_id = a.id_a AND rr2.direction = N'/'
344JOIN RCodes rr3
345   ON rr3.code = a.rcode
346GROUP BY a.family, a.number, rr1.post_id, rr2.post_id, rr3.region_id, a.id_a
347 
348SELECT a.family as 'Владелец',
349   a.number + CAST(a.rcode AS VARCHAR) as 'Номер авто',
350   FORMAT (r1.catchtime, 'HH\:mm\:ss', 'ru-RU' ) as 'Время въезда',
351   FORMAT (r2.catchtime, 'HH\:mm\:ss', 'ru-RU' ) as 'Время выезда',
352   regs.name as 'Родной регион авто'
353FROM @Temp t
354JOIN Autos a
355   ON a.id_a = t.auto_id
356JOIN Records r1
357   ON r1.direction = '\' and r1.auto_id = t.auto_id
358JOIN Records r2
359   ON r2.direction = '/' and r2.auto_id = t.auto_id
360JOIN RCodes r
361   ON r.code = a.rcode
362JOIN Regions regs
363ON r.region_id = regs.id
364--WHERE dbo.GetAutoType(fr_post, to_post, fr_city) = 'Местный'
365GROUP BY a.family, r1.catchtime,
366   r2.catchtime, fr_post, to_post,
367   fr_city, to_city, regs.name, a.number,
368   a.rcode
369GO