· 8 years ago · Dec 12, 2017, 06:06 PM
1use master;
2drop database yuliyayakovenko;
3create database yuliyayakovenko;
4use yuliyayakovenko;
5
6
7create schema yuliyayakovenko;
8go
9
10create table Regions (
11 id int primary key not null,
12 name nvarchar(40)
13);
14create table RegionCodes (
15 code int primary key not null,
16 region_id int foreign key references Regions(id),
17 constraint CH_code check (code < 300 or code between 700 and 799)
18);
19create table Persons (
20 id int primary key identity not null,
21 name nvarchar(40),
22 family nvarchar(40)
23);
24create table Cars (
25 id int primary key identity not null,
26 mark nvarchar(40),
27 color nvarchar(40),
28 number nvarchar(40),
29 region_code int foreign key references RegionCodes(code),
30 owner int foreign key references Persons(id),
31 constraint CH_Cars_number check (number like '[ÀÂÅÊÌÃÃŽÃÑÒÓÕABEKMHOPCTYX][0-9][0-9][0-9][ÀÂÅÊÌÃÃŽÃÑÒÓÕABEKMHOPCTYX][ÀÂÅÊÌÃÃŽÃÑÒÓÕABEKMHOPCTYX]'),
32 constraint CH_Cars_region_code check (region_code<300 or region_code between 700 and 799)
33);
34create table Posts (
35 id int primary key identity not null,
36 name nvarchar(40)
37);
38create table Records (
39 id int primary key identity not null,
40 post_id int foreign key references Posts(id),
41 car_id int foreign key references Cars(id),
42 incoming bit,
43 record_time time
44);
45go
46insert into Regions values
47 (34, 'Volgogradskaya oblast'),
48 (51, 'Murmanskaya oblast'),
49 (66, 'Sverdlovskaya oblast'),
50 (67, 'Smolenskaya oblast'),
51 (76, 'Yaroslavskaya oblast')
52go
53insert into RegionCodes values
54 (34, 34),
55 (134, 34),
56 (51, 51),
57 (66, 66),
58 (96, 66),
59 (196, 66),
60 (67, 67),
61 (76, 76)
62go
63insert into Persons values
64 ('last name 1', 'first name 1'),
65 ('last name 2', 'first name 2'),
66 ('last name 3', 'first name 3'),
67 ('last name 4', 'first name 4'),
68 ('last name 5', 'first name 5')
69go
70insert into Cars values
71 ('car 1', 'black', 'C111CC', 134, 1),
72 ('car 2', 'white', 'T222Ã’T', 66, 2),
73 ('car 3', 'blue', 'B333BB', 51, 3),
74 ('car 4', 'yellow', 'Õ444ÕÕ', 196, 4),
75 ('car 5', 'black', 'À555ÀÀ', 76, 5),
76 ('car 6', 'red', 'Y666YY', 67, 4),
77 ('car 7', 'blue', 'ÃŽ000ÃŽÃŽ', 66, 4)
78go
79insert into Posts values
80 ('First post'),
81 ('Second post'),
82 ('Third post'),
83 ('Fourth post'),
84 ('Fifth post')
85go
86
87create function lastAction (@car_id int, @rec_id int)
88returns int
89as
90begin
91 declare @result bit = (select top(1) incoming from Records where id != @rec_id and car_id = @car_id order by record_time desc)
92 if @result is null return -1
93 return cast(@result as int)
94end
95
96go
97alter table Records add constraint CH_Records check(incoming != lastAction(car_id, id));
98go
99insert into Records values
100 (5, 5, 1, '03:05'),
101 (5, 3, 1, '04:05'),
102 (5, 3, 0, '05:05'),
103 (1, 4, 1, '06:00'),
104 (1, 4, 0, '02:00'),
105 (2, 2, 0, '00:30'),
106 (2, 2, 1, '01:30'),
107 (3, 1, 1, '10:30'),
108 (4, 1, 0, '11:30')
109go
110
111select Convert(nvarchar, r.record_time, 108) as Time, post.name as Post, IIF(r.incoming=1, 'Yes', 'No') as 'To town',
112 c.mark as mark, c.color as color, c.number as number,
113 reg.name as region, pers.family as lastName, pers.name as firstName
114 from Records as r
115 inner join Cars as c on r.car_id=c.id
116 inner join Persons as pers on c.owner=pers.id
117 inner join Posts as post on post.id=r.post_id
118 inner join RegionCodes as codes on codes.code=c.region_code
119 inner join Regions as reg on codes.region_id=reg.id
120 order by r.record_time
121go
122declare @CurrentRegion int = (select id from Regions where name='Sverdlovskaya oblast');
123declare @transit table(car_id int);
124insert into @transit select distinct t1.car_id from Records as t1
125 where
126 t1.incoming = 1 and
127 (select regc.region_id
128 from Cars as cars
129 join RegionCodes as regc on cars.region_code=regc.code
130 where cars.id=t1.car_id)!=@CurrentRegion and
131 exists (select * from Records as t2 where
132 t1.car_id = t2.car_id and
133 t1.post_id != t2.post_id and
134 t1.record_time < t2.record_time and
135 t2.incoming = 0
136 );
137declare @outer table(car_id int);
138insert into @outer select distinct t1.car_id from Records as t1
139 where
140 t1.incoming = 1 and
141 exists (select * from Records as t2 where
142 t1.car_id = t2.car_id and
143 t1.post_id = t2.post_id and
144 t1.record_time < t2.record_time and
145 t2.incoming = 0
146 ) and t1.car_id not in (select * from @transit);
147declare @local table(car_id int);
148insert into @local select distinct t1.car_id from Records as t1
149 where
150 t1.incoming = 0 and
151 (select b.region_id
152 from Lyapina.Cars as a
153 join Lyapina.RegionCodes as b on a.region_code=b.code
154 where a.id=t1.car_id)=@CurrentRegion and
155 exists (select * from Records as t2 where
156 t1.car_id = t2.car_id and
157 t1.record_time < t2.record_time and
158 t2.incoming = 1
159 ) and t1.car_id not in (select * from @transit) and
160 t1.car_id not in (select * from @outer);
161declare @other table(car_id int);
162insert into @other select distinct t1.car_id from Records as t1
163 where
164 t1.car_id not in (select * from @transit) and
165 t1.car_id not in (select * FROM @outer) and
166 t1.car_id not in (select * from @local)
167select distinct c.mark as 'Mark',c.color as 'Color' , c.number as 'Number',
168 c.region_code as 'Region code', reg.name as 'Region', pers.family as 'Last name', pers.name as 'First name'
169 from Cars as c
170 inner join Records as record on record.car_id=c.id
171 inner join Persons as pers on c.owner=pers.id
172 inner join RegionCodes as codes on codes.code=c.region_code
173 inner join Regions as reg on codes.region_id=reg.id
174 order by reg.name
175select b.id as 'Transitional', b.mark as 'Mark', b.color as 'Color' , b.number as 'Number',
176 b.region_code as 'Region code', reg.name as 'Region name', pers.family as 'Last name', pers.name as 'First name' from @transit as a
177 join Cars as b on a.car_id=b.id
178 join Persons as pers on b.owner=pers.id
179 join RegionCodes as codes on codes.code=b.region_code
180 join Regions as reg on codes.region_id=reg.id;
181select b.id as 'From another city', b.mark as 'Mark', b.color as 'Color' , b.number as 'Number',
182 b.region_code as 'Region code', reg.name as 'Region', pers.family as 'Last name', pers.name as 'First name' from @outer as a
183 join Cars as b on a.car_id=b.id
184 join Persons as pers on b.owner=pers.id
185 join RegionCodes as codes on codes.code=b.region_code
186 join Regions as reg on codes.region_id=reg.id;
187select b.id as 'Local', b.mark as 'Mark',b.color as 'Color' , b.number as 'Number',
188 b.region_code as 'Region code', reg.name as 'Region', pers.family as 'Last name', pers.name as 'First name' from @local as a
189 join Cars as b on a.car_id=b.id
190 join Persons as pers on b.owner=pers.id
191 join RegionCodes as codes on codes.code=b.region_code
192 join Regions as reg on codes.region_id=reg.id;
193
194select b.id as 'Others', b.mark as 'Mark',b.color as 'Color' , b.number as 'Number',
195 b.region_code as 'Region code', reg.name as 'Region', pers.family as 'Last name', pers.name as 'First name' from @other as a
196 join Cars as b on a.car_id=b.id
197 join Persons as pers on b.owner=pers.id
198 join RegionCodes as codes on codes.code=b.region_code
199 join Regions as reg on codes.region_id=reg.id;
200select count(a.car_id) as 'Transitional' from @transit as a;
201select count(a.car_id) as 'From another city' from @outer as a;
202select count(a.car_id) as 'Local' from @local as a;
203select count(a.car_id) as 'Other' from @other as a;
204go