daily pastebin goal
20%
SHARE
TWEET

Untitled

a guest Nov 12th, 2017 58 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE master
  2.  
  3. -- DROP DATABASE [A_Imaev]
  4.  
  5. CREATE DATABASE [A_Imaev]
  6. GO
  7.  
  8. USE [A_Imaev]
  9. GO
  10.  
  11. IF OBJECT_ID('Records') IS NOT NULL
  12.     DROP TABLE Records
  13. IF OBJECT_ID('Posts') IS NOT NULL
  14.     DROP TABLE Posts
  15. IF OBJECT_ID('Cars') IS NOT NULL
  16.     DROP TABLE Cars
  17. IF OBJECT_ID('RegionCodes') IS NOT NULL
  18.     DROP TABLE RegionCodes
  19. IF OBJECT_ID('Regions') IS NOT NULL
  20.     DROP TABLE Regions
  21. IF OBJECT_ID('Persons') IS NOT NULL
  22.     DROP TABLE Persons
  23. IF OBJECT_ID('lastAction') IS NOT NULL
  24.     DROP FUNCTION lastAction
  25.  
  26. CREATE TABLE Regions (
  27.     id int PRIMARY KEY NOT NULL,
  28.     name nvarchar(128) NOT NULL
  29. )
  30. CREATE TABLE RegionCodes (
  31.     code int PRIMARY KEY NOT NULL,
  32.     region_id int FOREIGN KEY REFERENCES Regions(id)
  33. )
  34. CREATE TABLE Persons (
  35.     id int PRIMARY KEY IDENTITY NOT NULL,
  36.     name nvarchar(128),
  37.     family nvarchar(128)
  38. )
  39. CREATE TABLE Cars (
  40.     id int PRIMARY KEY IDENTITY NOT NULL,
  41.     mark nvarchar(128),
  42.     color nvarchar(64),
  43.     number nvarchar(64),
  44.     region_code int FOREIGN KEY REFERENCES RegionCodes(code),
  45.     owner int FOREIGN KEY REFERENCES Persons(id),
  46. )
  47. GO
  48. CREATE TRIGGER CarsTrigger ON Cars FOR INSERT AS
  49.     IF EXISTS (SELECT * FROM inserted WHERE number NOT LIKE '[АВЕКМНОРСТУХABEKMHOPCTYX][0-9][0-9][0-9][АВЕКМНОРСТУХABEKMHOPCTYX][АВЕКМНОРСТУХABEKMHOPCTYX]') OR
  50.         EXISTS (SELECT * FROM inserted WHERE NOT (region_code < 100 OR region_code BETWEEN 100 AND 199 OR region_code BETWEEN 200 AND 299 OR region_code BETWEEN 700 AND 799))
  51.     BEGIN
  52.         RAISERROR ('Неверный номер автомобиля или код региона', 16, 1);
  53.         ROLLBACK TRANSACTION;
  54.         RETURN
  55.     END
  56. GO
  57.  
  58. CREATE TABLE Posts (
  59.     id int PRIMARY KEY IDENTITY NOT NULL,
  60.     name nvarchar(128)
  61. )
  62. CREATE TABLE Records (
  63.     id int PRIMARY KEY IDENTITY NOT NULL,
  64.     post_id int FOREIGN KEY REFERENCES Posts(id),
  65.     car_id int FOREIGN KEY REFERENCES Cars(id),
  66.     incoming bit,
  67.     record_time datetime
  68. )
  69. GO
  70. CREATE FUNCTION lastAction (@car_id int, @rec_id int, @time datetime)
  71. RETURNS int
  72. AS
  73. BEGIN
  74.     DECLARE @result bit = (SELECT TOP(1) incoming FROM Records WHERE car_id = @car_id AND id != @rec_id AND record_time <= @time ORDER BY record_time DESC)
  75.     IF @result IS NULL RETURN -1
  76.     RETURN CAST(@result AS int)
  77. END
  78. GO
  79. CREATE TRIGGER RecordsTrigger ON Records FOR INSERT AS
  80.     IF EXISTS (SELECT * FROM inserted WHERE incoming=dbo.lastAction(car_id, id, record_time))
  81.     BEGIN
  82.         RAISERROR ('Неверное действие для машины', 16, 1);
  83.         ROLLBACK TRANSACTION;
  84.         RETURN
  85.     END
  86. GO
  87.  
  88.  
  89. INSERT INTO Regions VALUES
  90.     (13, 'Республика Мордовия'),
  91.     (23, 'Краснодарский край'),      
  92.     (50, 'Московская область'),
  93.     (66, 'Свердловская область'),    
  94.     (74, 'Челябинская область'),
  95.     (77, 'город Москва')  
  96. GO
  97.    
  98. INSERT INTO RegionCodes VALUES
  99.     (66, 66),
  100.     (96, 66),
  101.     (196, 66),
  102.  
  103.     (77, 77),
  104.     (97, 77),
  105.     (99, 77),
  106.     (177, 77),
  107.     (197, 77),
  108.     (199, 77),
  109.     (777, 77),
  110.    
  111.     (23, 23),
  112.     (93, 23),
  113.     (123, 23),
  114.    
  115.     (74, 74),
  116.     (174, 74),
  117.    
  118.     (13, 13),
  119.     (113, 13),
  120.  
  121.     (50, 50),
  122.     (90, 50),
  123.     (150, 50),
  124.     (190, 50),
  125.     (750, 50)
  126.  GO
  127.  
  128. INSERT INTO Persons VALUES
  129.     ('Иван', 'Иванов'),
  130.     ('Петр', 'Петров'),
  131.     ('Василий', 'Васильев'),
  132.     ('Татьяна', 'Татьянова'),
  133.     ('Михаил', 'Михайлов')
  134.  GO
  135.  
  136. INSERT INTO Cars VALUES
  137.     ('Honda', 'Красный',  'B387ТХ', 96, 1),
  138.     ('Toyota', 'Черный',  'А064ВТ', 96, 1),
  139.     ('Mazda', 'Черный',  'О360СК', 66, 2),
  140.     ('BMW', 'Красный',  'С777АК', 174, 3),
  141.     ('Mercedes', 'Белый',  'Н404НН', 777, 4),
  142.     ('Daewoo', 'Оранжевый',  'А202КЕ', 123, 5),
  143.     ('Bently', 'Желтый',  'А501СВ', 23, 4)
  144.  GO
  145.  
  146. INSERT INTO RegionCodes VALUES
  147. (000,50)
  148.  
  149. INSERT INTO Cars VALUES
  150.     ('Honda', 'Красный',  'B387ТA', 000, 1)
  151. GO
  152.  
  153. INSERT INTO Cars VALUES
  154.     ('Honda', 'Красный',  'Z777ZZ', 23, 1)
  155. GO
  156.  
  157.  
  158. INSERT INTO Posts VALUES
  159.     ('Северный пост'),
  160.     ('Южный пост'),
  161.     ('Западный пост'),
  162.     ('Восточный пост'),
  163.     ('Северо-Западный пост')
  164. GO
  165.  
  166. INSERT INTO Records VALUES
  167.     (1, 4, 1, '12:30'),
  168.     (2, 4, 0, '14:15'),
  169.     (1, 4, 1, '15:30'),
  170.     (2, 4, 0, '16:15'),
  171.     (3, 1, 0, '15:15'),
  172.     (4, 1, 1, '16:15'),
  173.     (5, 5, 1, '17:15'),
  174.     (5, 5, 0, '18:15'),
  175.     (3, 7, 0, '18:15')
  176. GO
  177.    
  178. INSERT INTO Records VALUES
  179.     (3, 7, 0, '18:15')
  180. GO
  181.  
  182. SELECT Convert(nvarchar,r.record_time,108) AS Время, post.name AS Пост, IIF(r.incoming=1, 'Да', 'Нет') AS 'В город',
  183.     c.mark AS Марка, c.color AS Цвет, c.number AS Номер,
  184.     reg.name AS Регион, person.family AS Фамилия, person.name AS Имя
  185.     FROM Records AS r
  186.         INNER JOIN Cars AS c ON r.car_id=c.id
  187.         INNER JOIN Persons AS person ON c.owner=person.id
  188.         INNER JOIN Posts AS post ON post.id=r.post_id
  189.         INNER JOIN RegionCodes AS codes ON codes.code=c.region_code
  190.         INNER JOIN Regions AS reg ON codes.region_id=reg.id
  191.     ORDER BY r.record_time
  192. GO
  193.  
  194. DECLARE @CurrentRegion int = (SELECT id FROM Regions WHERE name='Свердловская область')
  195.  
  196. DECLARE @transit table(car_id int)
  197. INSERT INTO @transit SELECT DISTINCT t1.car_id FROM Records AS t1
  198.     WHERE
  199.         t1.incoming = 1 AND
  200.         (SELECT codes.region_id
  201.             FROM Cars AS cars
  202.             JOIN RegionCodes AS codes ON cars.region_code=codes.code
  203.                 WHERE cars.id=t1.car_id)!=@CurrentRegion AND
  204.         EXISTS (SELECT * FROM Records AS t2 WHERE
  205.             t1.car_id = t2.car_id AND
  206.             t1.post_id != t2.post_id AND
  207.             t1.record_time < t2.record_time AND
  208.             t2.incoming = 0
  209.         )
  210.  
  211. DECLARE @outer table(car_id int)
  212. INSERT INTO @outer SELECT DISTINCT t1.car_id FROM Records AS t1
  213.     WHERE
  214.         t1.incoming = 1 AND
  215.         EXISTS (SELECT * FROM Records AS t2 WHERE
  216.             t1.car_id = t2.car_id AND
  217.             t1.post_id = t2.post_id AND
  218.             t1.record_time < t2.record_time AND
  219.             t2.incoming = 0
  220.         ) AND t1.car_id NOT IN (SELECT * FROM @transit)
  221.  
  222. DECLARE @local table(car_id int)
  223. INSERT INTO @local SELECT DISTINCT t1.car_id FROM Records AS t1
  224.     WHERE
  225.         t1.incoming = 0 AND
  226.         (SELECT b.region_id
  227.             FROM Cars AS a
  228.             JOIN RegionCodes AS b ON a.region_code=b.code
  229.                 WHERE a.id=t1.car_id)=@CurrentRegion AND
  230.         EXISTS (SELECT * FROM Records AS t2 WHERE
  231.             t1.car_id = t2.car_id AND
  232.             t1.record_time < t2.record_time AND
  233.             t2.incoming = 1
  234.         ) AND t1.car_id NOT IN (SELECT * FROM @transit) AND
  235.         t1.car_id NOT IN (SELECT * FROM @outer)
  236.  
  237. DECLARE @other table(car_id int)
  238. INSERT INTO @other SELECT DISTINCT t1.car_id FROM Records AS t1
  239.     WHERE
  240.         t1.car_id NOT IN (SELECT * FROM @transit) AND
  241.         t1.car_id NOT IN (SELECT * FROM @outer) AND
  242.         t1.car_id NOT IN (SELECT * FROM @local)
  243.  
  244. SELECT b.id AS 'Транзитные', b.mark AS 'Марка', b.color as 'Цвет' , b.number AS 'Номер',
  245.     b.region_code AS 'Код региона', reg.name as 'Регион', person.family as 'Фамилия', person.name as 'Имя' FROM @transit AS a
  246.         JOIN Cars AS b ON a.car_id=b.id
  247.         JOIN Persons AS person ON b.owner=person.id
  248.         JOIN RegionCodes AS codes ON codes.code=b.region_code
  249.         JOIN Regions AS reg ON codes.region_id=reg.id;
  250.  
  251. SELECT b.id AS ' Иногородние', b.mark AS 'Марка',b.color as 'Цвет' , b.number AS 'Номер',
  252.     b.region_code AS 'Код региона', reg.name as 'Регион', person.family as 'Фамилия', person.name as 'Имя' FROM @outer AS a
  253.         JOIN Cars AS b ON a.car_id=b.id
  254.         JOIN Persons AS person ON b.owner=person.id
  255.         JOIN RegionCodes AS codes ON codes.code=b.region_code
  256.         JOIN Regions AS reg ON codes.region_id=reg.id;
  257.  
  258. SELECT b.id AS 'Местные', b.mark AS 'Марка',b.color as 'Цвет' , b.number AS 'Номер',
  259.     b.region_code AS 'Код региона', reg.name as 'Регион', person.family as 'Фамилия', person.name as 'Имя' FROM @local AS a
  260.         JOIN Cars AS b ON a.car_id=b.id
  261.         JOIN Persons AS person ON b.owner=person.id
  262.         JOIN RegionCodes AS codes ON codes.code=b.region_code
  263.         JOIN Regions AS reg ON codes.region_id=reg.id;
  264.  
  265. SELECT b.id AS 'Другие', b.mark AS 'Марка',b.color as 'Цвет' , b.number AS 'Номер',
  266.     b.region_code AS 'Код региона', reg.name as 'Регион', person.family as 'Фамилия', person.name as 'Имя' FROM @other AS a
  267.         JOIN Cars AS b ON a.car_id=b.id
  268.         JOIN Persons AS person ON b.owner=person.id
  269.         JOIN RegionCodes AS codes ON codes.code=b.region_code
  270.         JOIN Regions AS reg ON codes.region_id=reg.id;
  271. GO
RAW Paste Data
Top