Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE master
- GO
- IF EXISTS (
- SELECT name
- FROM sys.databases
- WHERE name = N'Nagaev' )
- ALTER DATABASE [Nagaev] set single_user with rollback immediate
- GO
- IF EXISTS (
- SELECT name
- FROM sys.databases
- WHERE name = N'Nagaev' )
- DROP DATABASE [Nagaev]
- GO
- CREATE DATABASE [Nagaev]
- GO
- USE [Nagaev]
- GO
- CREATE TABLE region (
- id TINYINT,
- name NVARCHAR(30),
- CONSTRAINT PK_region_id PRIMARY KEY (id)
- )
- GO
- CREATE TABLE rcode (
- region_id tinyint,
- code tinyint,
- CONSTRAINT PK_regio_id PRIMARY KEY (code),
- CONSTRAINT FK_regio_id FOREIGN KEY (region_id) REFERENCES region(id) ON UPDATE CASCADE,
- )
- GO
- CREATE TABLE color
- (
- id TINYINT,
- name NVARCHAR(20),
- CONSTRAINT PK_color_id PRIMARY KEY (id)
- )
- GO
- CREATE TABLE post (
- id tinyint NOT NULL,
- name varchar(20) NULL,
- CONSTRAINT PK_post_id PRIMARY KEY (id),
- )
- GO
- CREATE FUNCTION CorrectNumber (@num VARCHAR(30), @rcode tinyint)
- RETURNS int
- AS
- BEGIN
- DECLARE @str_rcode tinyint
- select @str_rcode = CONVERT(varchar, @rcode)
- if (UPPER(@num) like '[АВЕКМНОРСТУХ][0-9][0-9][0-9][АВЕКМНОРСТУХ][АВЕКМНОРСТУХ]' and @num not like '%000%' and
- (@str_rcode like '[0-9][0-9]' or @str_rcode like '[127][0-9][0-9]'))
- return 0
- return -1
- END
- GO
- CREATE FUNCTION CorrectRecord (@date DATETIME, @dir VARCHAR(1), @auto_number nvarchar(7), @auto_rcode tinyint, @id tinyint)
- RETURNS int
- AS
- BEGIN
- if ((SELECT COUNT(1) from record WHERE record.id != @id and record.auto_number = @auto_number and record.auto_rcode = @auto_rcode) = 0)
- return 0
- if EXISTS(
- SELECT * from record
- INNER JOIN
- (SELECT MAX(catchtime) as max_date, auto_number FROM record
- WHERE catchtime < @date and @auto_number = auto_number and @auto_rcode = auto_rcode
- GROUP BY auto_number) r
- ON record.catchtime = r.max_date and record.auto_number = r.auto_number
- WHERE @dir != record.direction)
- return 0
- return -1
- END
- GO
- CREATE TABLE auto (
- color_id tinyint NOT NULL,
- number NVARCHAR(7) NOT NULL,
- rcode tinyint NOT NULL,
- owner_name NVARCHAR(100) NOT NULL,
- CONSTRAINT PK_number PRIMARY KEY CLUSTERED
- (
- number ASC,
- rcode ASC
- ),
- CONSTRAINT FK_regi_id FOREIGN KEY (rcode) REFERENCES rcode(code) ON UPDATE CASCADE,
- CONSTRAINT FK_color_id FOREIGN KEY (color_id) REFERENCES color(id) ON UPDATE CASCADE
- )
- GO
- CREATE TABLE record (
- id tinyint NOT NULL,
- post_id tinyint NOT NULL,
- auto_number nvarchar(7) NOT NULL,
- auto_rcode tinyint,
- catchtime DATETIME NOT NULL,
- direction CHAR NOT NULL,
- CONSTRAINT CK_direction CHECK (direction = '>' or direction = '<'),
- CONSTRAINT CK_record CHECK (dbo.CorrectRecord(catchtime, direction, auto_number, auto_rcode, id) = 0),
- CONSTRAINT FK_post_id FOREIGN KEY (post_id) REFERENCES post(id) ON UPDATE CASCADE,
- CONSTRAINT FK_auto_id FOREIGN KEY (auto_number, auto_rcode) REFERENCES auto(number, rcode) ON UPDATE CASCADE,
- )
- GO
- CREATE TRIGGER on_insert_auto ON auto FOR INSERT AS
- BEGIN
- IF EXISTS (
- SELECT *
- FROM inserted WHERE dbo.CorrectNumber(number, rcode) = -1
- )
- BEGIN
- PRINT 'Некорректная запись'
- ROLLBACK TRANSACTION
- END
- END
- GO
- CREATE FUNCTION GetAutoType (
- @fromPost tinyint,
- @toPost tinyint,
- @fromRegion tinyint
- )
- RETURNS VARCHAR(30)
- AS
- BEGIN
- if (@fromPost != @toPost AND @fromRegion != 1)
- RETURN 'Транзитный'
- if (@fromPost = @toPost AND @fromRegion != 1)
- RETURN 'Иногородний'
- if (@fromRegion = 1)
- RETURN 'Местный'
- RETURN 'Прочий'
- END
- GO
- INSERT INTO region(id, name) VALUES
- (1, N'Свердловская область'),
- (2, N'Самарская область'),
- (3, N'Московская область'),
- (4, N'Челябинская область'),
- (5, N'Краснодарский край')
- GO
- INSERT INTO rcode(region_id, code) VALUES
- (1, 66), (1, 96),
- (2, 63), (2, 163),
- (3, 50), (3, 150),
- (4, 74), (4, 174),
- (5, 23), (5, 93)
- GO
- INSERT INTO color(id, name) VALUES
- (1, N'Красный'),
- (2, N'Белый'),
- (3, N'Синий'),
- (4, N'Черный'),
- (5, N'Серый')
- GO
- INSERT INTO post(id, name) VALUES
- (1, 'Северный')
- ,(2, 'Западный')
- ,(3, 'Востоный')
- ,(4, 'Южный')
- ,(5, 'Резервный')
- GO
- INSERT INTO auto (color_id, number, rcode, owner_name) VALUES
- (1, 'А123АА', 66, N'Артемов'),
- (1, 'В224АА', 63, N'Боровиков'),
- (1, 'С114АА', 50, N'Волгин'),
- (1, 'А213АА', 63, N'Грузде'),
- (1, 'В124АА', 163, N'Домашних'),
- (2, 'С223АА', 50, N'Жигалов'),
- (2, 'А113АА', 23, N'Зейналов'),
- (2, 'В214АА', 93, N'Клепинин'),
- (2, 'С123АА', 150, N'Ливанов'),
- (3, 'А214АА', 50, N'Миронов'),
- (3, 'В113АА', 63, N'Наймин'),
- (3, 'С224АА', 163, N'Проскурнин'),
- (3, 'А124АА', 96, N'Реванов'),
- (3, 'В213АА', 163, N'Сивой'),
- (4, 'С124АА', 66, N'Тарабанкин'),
- (4, 'А224АА', 174, N'Гайнанов'),
- (4, 'Н114АА', 163, N'Хоронякин'),
- (4, 'Т223АА', 150, N'Чалганов'),
- (5, 'Х123АА', 50, N'Вайс'),
- (5, 'А213АА', 74, N'Епифанов')
- --(21, 5, 'А21АА', 74, N'Сбиванов')
- GO
- INSERT INTO record (id, post_id, auto_number, auto_rcode, catchtime, direction) VALUES
- (1, 1, 'А123АА', 66, N'20120618 10:34:08', N'>')
- ,(2, 2, 'А123АА', 66, N'20120618 10:54:09', N'<')
- ,(3, 2, 'В224АА', 63, N'20120618 10:10:00', N'>')
- ,(4, 1, 'В224АА', 63, N'20120618 10:34:09', N'<')
- ,(5, 3, 'С114АА', 50, N'20120618 11:12:13', N'>')
- ,(6, 4, 'С114АА', 50, N'20120618 11:59:12', N'<')
- ,(7, 4, 'А213АА', 63, N'20120618 10:34:08', N'>')
- ,(8, 3, 'А213АА', 63, N'20120618 13:37:15', N'<')
- ,(9, 5, 'В124АА', 163, N'20120618 09:01:05', N'>')
- ,(10, 1, 'В124АА', 163, N'20120618 10:13:49', N'<')
- ,(11, 1, 'С223АА', 50, N'20120618 12:34:08', N'>')
- ,(12, 1, 'С223АА', 50, N'20120618 12:44:09', N'<')
- ,(13, 2, 'А113АА', 23, N'20120618 13:34:08', N'>')
- ,(14, 2, 'А113АА', 23, N'20120618 15:21:09', N'<')
- ,(15, 3, 'В214АА', 93, N'20120618 10:11:18', N'>')
- ,(16, 3, 'В214АА', 93, N'20120618 21:56:21', N'<')
- ,(17, 4, 'С123АА', 150, N'20120618 12:34:08', N'>')
- ,(18, 5, 'С123АА', 150, N'20120618 13:34:09', N'<')
- ,(19, 5, 'А214АА', 50, N'20120618 14:14:14', N'>')
- ,(20, 1, 'А214АА', 50, N'20120618 14:34:14', N'<')
- ,(21, 2, 'В113АА', 63, N'20120618 15:34:08', N'>')
- ,(22, 2, 'В113АА', 63, N'20120618 16:37:38', N'<')
- ,(23, 1, 'С224АА', 163, N'20120618 10:34:08', N'>')
- ,(24, 2, 'С224АА', 163, N'20120618 11:36:09', N'<')
- ,(25, 1, 'А124АА', 96, N'20120618 14:39:59', N'>')
- ,(26, 3, 'А124АА', 96, N'20120618 15:34:59', N'<')
- ,(27, 1, 'В213АА', 163, N'20120618 17:34:08', N'>')
- ,(28, 3, 'В213АА', 163, N'20120618 23:34:09', N'<')
- ,(29, 5, 'С124АА', 66, N'20120618 18:24:00', N'>')
- ,(30, 1, 'С124АА', 66, N'20120618 19:34:03', N'<')
- ,(31, 2, 'А224АА', 174, N'20120618 16:22:08', N'>')
- ,(32, 1, 'А224АА', 174, N'20120618 20:51:09', N'<')
- ,(33, 2, 'Н114АА', 163, N'20120618 11:03:08', N'>')
- ,(34, 1, 'Н114АА', 163, N'20120618 19:33:09', N'<')
- ,(35, 3, 'Т223АА', 150, N'20120618 19:28:17', N'>')
- ,(36, 3, 'Т223АА', 150, N'20120618 19:54:49', N'<')
- ,(37, 1, 'Х123АА', 50, N'20120618 23:00:00', N'>')
- ,(38, 4, 'Х123АА', 50, N'20120618 23:59:59', N'<')
- ,(39, 5, 'А213АА', 74, N'20120618 20:20:18', N'>')
- ,(40, 4, 'А213АА', 74, N'20120618 22:28:41', N'<')
- --,(41, 5, 'А213АА', 74, N'20120618 10:34:20', N'<')
- GO
- DECLARE @Temp TABLE
- (
- from_city tinyint,
- from_post tinyint,
- to_post tinyint
- );
- INSERT INTO
- @Temp
- SELECT rcode.region_id, rr1.post_id, rr2.post_id FROM auto a
- JOIN record rr1 ON rr1.auto_number = a.number and rr1.auto_rcode = a.rcode AND rr1.direction = N'>'
- JOIN record rr2 ON rr2.auto_number = a.number and rr2.auto_rcode = a.rcode AND rr2.direction = N'<'
- JOIN rcode ON rcode.code = a.rcode
- GROUP BY a.owner_name, a.number, rr1.post_id, rr2.post_id, rcode.region_id
- SELECT dbo.GetAutoType(from_post, to_post, from_city) as 'Тип', COUNT(*) as 'Количество' FROM @Temp
- GROUP BY dbo.GetAutoType(from_post, to_post, from_city)
- GO
- DECLARE @Temp TABLE
- (
- auto_number nvarchar(7),
- auto_rcode tinyint,
- from_city tinyint,
- from_post tinyint,
- to_post tinyint
- );
- INSERT INTO
- @Temp
- SELECT a.number, a.rcode, rr3.region_id, rr1.post_id, rr2.post_id FROM auto a
- JOIN record rr1 ON rr1.auto_number = a.number and rr1.auto_rcode = a.rcode AND rr1.direction = N'>'
- JOIN record rr2 ON rr2.auto_number = a.number AND rr2.auto_rcode = a.rcode and rr2.direction = N'<'
- JOIN rcode rr3 ON rr3.code = a.rcode
- GROUP BY a.owner_name, rr1.post_id, rr2.post_id, rr3.region_id, a.number, a.rcode
- SELECT
- a.owner_name as 'Владелец',
- a.number + CAST(a.rcode AS VARCHAR) as 'Номер авто',
- FORMAT (r1.catchtime, 'HH\:mm\:ss', 'ru-RU' ) as 'Время въезда',
- FORMAT (r2.catchtime, 'HH\:mm\:ss', 'ru-RU' ) as 'Время выезда',
- dbo.GetAutoType(from_post, to_post, from_city) as 'Тип',
- region.name as 'Родной регион'
- FROM @Temp t
- JOIN auto a ON a.number = t.auto_number and a.rcode = t.auto_rcode
- JOIN record r1 ON r1.direction = '>' and r1.auto_number = t.auto_number and r1.auto_rcode = t.auto_rcode
- JOIN record r2 ON r2.direction = '<' and r2.auto_number = t.auto_number and r2.auto_rcode = t.auto_rcode
- JOIN rcode rcode ON rcode.code = a.rcode
- JOIN region ON rcode.region_id = region.id
- GROUP BY a.owner_name, r1.catchtime, r2.catchtime, from_post, to_post,
- from_city, region.name, a.number, a.rcode
- ORDER BY a.owner_name
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement