daily pastebin goal
64%
SHARE
TWEET

Untitled

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