daily pastebin goal
79%
SHARE
TWEET

Untitled

a guest Nov 9th, 2017 58 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE master
  2. GO
  3.  
  4. IF EXISTS (
  5.     SELECT name
  6.     FROM sys.databases
  7.     WHERE name = N'Nagaev' )
  8. ALTER DATABASE [Nagaev] set single_user with rollback immediate
  9. GO
  10.  
  11. IF EXISTS (
  12.     SELECT name
  13.     FROM sys.databases
  14.     WHERE name = N'Nagaev' )
  15. DROP DATABASE [Nagaev]
  16. GO
  17.  
  18. CREATE DATABASE [Nagaev]
  19. GO
  20.  
  21. USE [Nagaev]
  22. GO
  23.  
  24. CREATE TABLE region (
  25.     id TINYINT,
  26.     name NVARCHAR(30),
  27.     CONSTRAINT PK_region_id PRIMARY KEY (id)
  28. )
  29. GO
  30.  
  31. CREATE TABLE rcode (
  32.     region_id tinyint,
  33.     code tinyint,
  34.     CONSTRAINT PK_regio_id PRIMARY KEY (code),
  35.     CONSTRAINT FK_regio_id FOREIGN KEY (region_id) REFERENCES region(id) ON UPDATE CASCADE,
  36. )
  37. GO
  38.  
  39. CREATE TABLE color
  40. (
  41.     id TINYINT,
  42.     name NVARCHAR(20),
  43.     CONSTRAINT PK_color_id PRIMARY KEY (id)
  44. )
  45. GO
  46.  
  47. CREATE TABLE post (
  48.     id tinyint NOT NULL,
  49.     name varchar(20) NULL,
  50.     CONSTRAINT PK_post_id PRIMARY KEY (id),
  51. )
  52. GO
  53.  
  54. CREATE FUNCTION CorrectNumber (@num VARCHAR(30), @rcode tinyint)
  55. RETURNS int
  56. AS
  57. BEGIN
  58.     DECLARE @str_rcode tinyint
  59.     select @str_rcode = CONVERT(varchar, @rcode)
  60.     if (UPPER(@num) like '[А-ЯA-Z][0-9][0-9][0-9][А-ЯA-Z][А-ЯA-Z]' and
  61.         (@str_rcode like '[0-9][0-9]' or @str_rcode like '[127][0-9][0-9]'))
  62.         return 0
  63.     return -1
  64. END
  65. GO
  66.  
  67. CREATE FUNCTION CorrectRecord (@date DATETIME, @dir VARCHAR(1), @auto_id tinyint, @id tinyint)
  68. RETURNS int
  69. AS
  70. BEGIN
  71.     if ((SELECT COUNT(1) from record WHERE record.id != @id and record.auto_id = @auto_id) = 0)
  72.         return 0
  73.  
  74.     if EXISTS(
  75.         SELECT * from record
  76.         INNER JOIN
  77.             (SELECT MAX(catchtime) as max_date, auto_id FROM record
  78.                 WHERE catchtime < @date and @auto_id = auto_id
  79.                 GROUP BY auto_id) r
  80.         ON record.catchtime = r.max_date and record.auto_id = r.auto_id
  81.         WHERE @dir != record.direction)
  82.         return 0
  83.  
  84.     return -1
  85. END
  86. GO
  87.  
  88. CREATE TABLE auto (
  89.     id tinyint NOT NULL,
  90.     color_id tinyint NOT NULL,
  91.     number NVARCHAR(7) NOT NULL,
  92.     rcode tinyint NOT NULL,
  93.     owner_name NVARCHAR(100) NOT NULL,
  94.     CONSTRAINT PK_auto_id PRIMARY KEY (id),
  95.     CONSTRAINT FK_regi_id FOREIGN KEY (rcode) REFERENCES rcode(code) ON UPDATE CASCADE,
  96.     CONSTRAINT FK_color_id FOREIGN KEY (color_id) REFERENCES color(id) ON UPDATE CASCADE
  97. )
  98. GO
  99.  
  100. CREATE TRIGGER on_insert_auto ON auto FOR INSERT AS
  101. BEGIN
  102.     IF EXISTS (
  103.     SELECT *
  104.     FROM inserted WHERE dbo.CorrectNumber(number, rcode) = -1
  105.     )
  106.     BEGIN
  107.         PRINT 'Некорректная запись'
  108.         ROLLBACK TRANSACTION
  109.     END
  110. END
  111. GO
  112.  
  113.  
  114. CREATE TABLE record (
  115.     id tinyint NOT NULL,
  116.     post_id tinyint NOT NULL,
  117.     auto_id tinyint NOT NULL,
  118.     catchtime DATETIME NOT NULL,
  119.     direction CHAR NOT NULL,
  120.     CONSTRAINT CK_direction CHECK (direction = '>' or direction = '<'),
  121.     CONSTRAINT CK_record CHECK (dbo.CorrectRecord(catchtime, direction, auto_id, id) = 0),
  122.     CONSTRAINT FK_post_id FOREIGN KEY (post_id) REFERENCES post(id) ON UPDATE CASCADE,
  123.     CONSTRAINT FK_auto_id FOREIGN KEY (auto_id) REFERENCES auto(id) ON UPDATE CASCADE,
  124. )
  125. GO
  126.  
  127. CREATE FUNCTION GetAutoType (
  128.     @fromPost tinyint,
  129.     @toPost tinyint,
  130.     @fromRegion tinyint
  131. )
  132. RETURNS VARCHAR(30)
  133. AS
  134. BEGIN
  135.     if (@fromPost != @toPost AND @fromRegion != 1)
  136.         RETURN 'Транзитный'
  137.     if (@fromPost = @toPost AND @fromRegion != 1)
  138.         RETURN 'Иногородний'
  139.     if (@fromRegion = 1)
  140.         RETURN 'Местный'
  141.     RETURN 'Прочий'
  142. END
  143. GO
  144.  
  145. INSERT INTO region(id, name) VALUES
  146. (1, N'Свердловская область'),
  147. (2, N'Самарская область'),
  148. (3, N'Московская область'),
  149. (4, N'Челябинская область'),
  150. (5, N'Краснодарский край')
  151. GO
  152.  
  153. INSERT INTO rcode(region_id, code) VALUES
  154. (1, 66), (1, 96),
  155. (2, 63), (2, 163),
  156. (3, 50), (3, 150),
  157. (4, 74), (4, 174),
  158. (5, 23), (5, 93)
  159. GO
  160.  
  161. INSERT INTO color(id, name) VALUES
  162. (1, N'Красный'),
  163. (2, N'Белый'),
  164. (3, N'Синий'),
  165. (4, N'Черный'),
  166. (5, N'Серый')
  167. GO
  168.  
  169. INSERT INTO post(id, name) VALUES
  170. (1, 'Северный')
  171. ,(2, 'Западный')
  172. ,(3, 'Востоный')
  173. ,(4, 'Южный')
  174. ,(5, 'Резервный')
  175. GO
  176.  
  177. INSERT INTO auto (id, color_id, number, rcode, owner_name) VALUES
  178. (1, 1, 'A123АА', 66, N'Артемов'),
  179. (2, 1, 'В224АА', 63, N'Боровиков'),
  180. (3, 1, 'С114АА', 50, N'Волгин'),
  181. (4, 1, 'А213АА', 63, N'Грузде'),
  182. (5, 1, 'В124АА', 163, N'Домашних'),
  183. (6, 2, 'С223АА', 50, N'Жигалов'),
  184. (7, 2, 'А113АА', 23, N'Зейналов'),
  185. (8, 2, 'В214АА', 93, N'Клепинин'),
  186. (9, 2, 'С123АА', 150, N'Ливанов'),
  187. (10, 3, 'А214АА', 50, N'Миронов'),
  188. (11, 3, 'В113АА', 63, N'Наймин'),
  189. (12, 3, 'С224АА', 163, N'Проскурнин'),
  190. (13, 3, 'А124АА', 96, N'Реванов'),
  191. (14, 3, 'В213АА', 163, N'Сивой'),
  192. (15, 4, 'С124АА', 66, N'Тарабанкин'),
  193. (16, 4, 'А224АА', 174, N'Гайнанов'),
  194. (17, 4, 'Н114АА', 163, N'Хоронякин'),
  195. (18, 4, 'Т223АА', 150, N'Чалганов'),
  196. (19, 5, 'Х123АА', 50, N'Вайс'),
  197. (20, 5, 'А213АА', 74, N'Епифанов')
  198. --(21, 5, 'А21АА', 74, N'Сбиванов')
  199. GO
  200.  
  201. INSERT INTO record (id, post_id, auto_id, catchtime, direction) VALUES
  202. (1, 1, 1, N'20120618 10:34:08', N'>')
  203. ,(2, 2, 1, N'20120618 10:54:09', N'<')
  204. ,(3, 2, 2, N'20120618 10:10:00', N'>')
  205. ,(4, 1, 2, N'20120618 10:34:09', N'<')
  206. ,(5, 3, 3, N'20120618 11:12:13', N'>')
  207. ,(6, 4, 3, N'20120618 11:59:12', N'<')
  208. ,(7, 4, 4, N'20120618 10:34:08', N'>')
  209. ,(8, 3, 4, N'20120618 13:37:15', N'<')
  210. ,(9, 5, 5, N'20120618 09:01:05', N'>')
  211. ,(10, 1, 5, N'20120618 10:13:49', N'<')
  212. ,(11, 1, 6, N'20120618 12:34:08', N'>')
  213. ,(12, 1, 6, N'20120618 12:44:09', N'<')
  214. ,(13, 2, 7, N'20120618 13:34:08', N'>')
  215. ,(14, 2, 7, N'20120618 15:21:09', N'<')
  216. ,(15, 3, 8, N'20120618 10:11:18', N'>')
  217. ,(16, 3, 8, N'20120618 21:56:21', N'<')
  218. ,(17, 4, 9, N'20120618 12:34:08', N'>')
  219. ,(18, 5, 9, N'20120618 13:34:09', N'<')
  220. ,(19, 5, 10, N'20120618 14:14:14', N'>')
  221. ,(20, 1, 10, N'20120618 14:34:14', N'<')
  222. ,(21, 2, 11, N'20120618 15:34:08', N'>')
  223. ,(22, 2, 11, N'20120618 16:37:38', N'<')
  224. ,(23, 1, 12, N'20120618 10:34:08', N'>')
  225. ,(24, 2, 12, N'20120618 11:36:09', N'<')
  226. ,(25, 1, 13, N'20120618 14:39:59', N'>')
  227. ,(26, 3, 13, N'20120618 15:34:59', N'<')
  228. ,(27, 1, 14, N'20120618 17:34:08', N'>')
  229. ,(28, 3, 14, N'20120618 23:34:09', N'<')
  230. ,(29, 5, 15, N'20120618 18:24:00', N'>')
  231. ,(30, 1, 15, N'20120618 19:34:03', N'<')
  232. ,(31, 2, 16, N'20120618 16:22:08', N'>')
  233. ,(32, 1, 16, N'20120618 20:51:09', N'<')
  234. ,(33, 2, 17, N'20120618 11:03:08', N'>')
  235. ,(34, 1, 17, N'20120618 19:33:09', N'<')
  236. ,(35, 3, 18, N'20120618 19:28:17', N'>')
  237. ,(36, 3, 18, N'20120618 19:54:49', N'<')
  238. ,(37, 1, 19, N'20120618 23:00:00', N'>')
  239. ,(38, 4, 19, N'20120618 23:59:59', N'<')
  240. ,(39, 5, 20, N'20120618 20:20:18', N'<')
  241. ,(40, 4, 20, N'20120618 22:28:41', N'>')
  242. --,(41, 5, 20, N'20120618 10:34:20', N'<')
  243. GO
  244.  
  245.  
  246. DECLARE @Temp TABLE
  247. (
  248. from_city tinyint,
  249. from_post tinyint,
  250. to_post tinyint
  251. );
  252.  
  253. INSERT INTO
  254. @Temp
  255. SELECT rcode.region_id, rr1.post_id, rr2.post_id FROM auto a
  256.     JOIN record rr1 ON rr1.auto_id = a.id AND rr1.direction = N'>'
  257.     JOIN record rr2 ON rr2.auto_id = a.id AND rr2.direction = N'<'
  258.     JOIN rcode ON rcode.code = a.rcode
  259. GROUP BY a.owner_name, a.number, rr1.post_id, rr2.post_id, rcode.region_id
  260.  
  261. SELECT dbo.GetAutoType(from_post, to_post, from_city) as 'Тип', COUNT(*) as 'Количество' FROM @Temp
  262. GROUP BY dbo.GetAutoType(from_post, to_post, from_city)
  263. GO
  264.  
  265. DECLARE @Temp TABLE
  266. (
  267. auto_id tinyint,
  268. from_city tinyint,
  269. from_post tinyint,
  270. to_post tinyint
  271. );
  272.  
  273. INSERT INTO
  274. @Temp
  275. SELECT a.id, rr3.region_id, rr1.post_id, rr2.post_id FROM auto a
  276.     JOIN record rr1 ON rr1.auto_id = a.id AND rr1.direction = N'>'
  277.     JOIN record rr2 ON rr2.auto_id = a.id AND rr2.direction = N'<'
  278.     JOIN rcode rr3 ON rr3.code = a.rcode
  279. GROUP BY a.owner_name, a.number, rr1.post_id, rr2.post_id, rr3.region_id, a.id
  280.  
  281. SELECT
  282.        a.owner_name as 'Владелец',
  283.        a.number + CAST(a.rcode AS VARCHAR) as 'Номер авто',
  284.        FORMAT (r1.catchtime, 'HH\:mm\:ss', 'ru-RU' ) as 'Время въезда',
  285.        FORMAT (r2.catchtime, 'HH\:mm\:ss', 'ru-RU' ) as 'Время выезда',
  286.        dbo.GetAutoType(from_post, to_post, from_city) as 'Тип',     
  287.        region.name as 'Родной регион'
  288. FROM @Temp t
  289.     JOIN auto a ON a.id = t.auto_id
  290.     JOIN record r1 ON r1.direction = '>' and r1.auto_id = t.auto_id
  291.     JOIN record r2 ON r2.direction = '<' and r2.auto_id = t.auto_id
  292.     JOIN rcode rcode ON rcode.code = a.rcode
  293.     JOIN region ON rcode.region_id = region.id
  294. GROUP BY a.owner_name, r1.catchtime,  r2.catchtime, from_post, to_post,
  295.          from_city, region.name, a.number, a.rcode
  296. ORDER BY r1.catchtime
  297. GO
RAW Paste Data
Top