· 9 years ago · Dec 17, 2016, 02:52 PM
1use master
2GO
3
4IF EXISTS (
5SELECT name
6FROM sys.databases
7WHERE name = N'[ Mitrofanova]'
8)
9ALTER DATABASE Mitrofanova set single_user with rollback immediate
10GO
11
12IF EXISTS (
13SELECT name
14FROM sys.databases
15WHERE name = N'Mitrofanova'
16)
17DROP DATABASE Mitrofanova
18GO
19
20IF OBJECT_ID('Mitrofanova') IS NOT NULL
21DROP DATABASE Mitrofanova
22GO
23
24CREATE DATABASE Mitrofanova
25GO
26
27USE Mitrofanova
28GO
29
30--/////////////////////////////////
31CREATE TABLE Person
32(
33idP int not null,
34nameP nvarchar(90) not null,
35adress nvarchar(100) not null,
36mobilTel nvarchar(11) not null,
37workTel nvarchar(8) not null
38CONSTRAINT PK_name PRIMARY KEY (idP)
39)
40GO
41
42CREATE TABLE Commission
43(
44idC int not null,
45profil nvarchar(40) not null,
46CONSTRAINT PK_idCom PRIMARY KEY (idC)
47)
48GO
49
50
51CREATE TABLE Worker
52(
53id int not null,
54idCom int not null,
55dateIn date not null,
56dateOut date,
57position nvarchar(20) not null,
58--CONSTRAINT PK_id PRIMARY KEY (id),
59constraint FK_id foreign key (id) references Person(idP),
60constraint FK_idC foreign key (idCom) references Commission(idC)
61)
62GO
63
64
65CREATE TABLE Meeting
66(
67idMeet int not null,
68idComm int not null,
69timeStart dateTime not null,
70timeFinish dateTime not null,
71room nvarchar(10) not null,
72constraint PK_idMeet primary key (idMeet),
73constraint FK_ComM foreign key (idComm) references Commission(idC)
74)
75go
76
77
78CREATE TABLE Members
79(
80idM int not null,
81idP int not null,
82constraint FK_idMeet foreign key (idM) references Meeting(idMeet)
83)
84go
85
86
87CREATE PROCEDURE PrintCom
88as
89begin
90 DECLARE c CURSOR FOR SELECT idC, profil FROM Commission
91 DECLARE @idC int
92 DECLARE @profil nvarchar(40)
93 OPEN c
94 FETCH NEXT FROM c
95 INTO @idC, @profil
96 WHILE @@fetch_status = 0
97 begin
98
99 select profil as N'Профиль', position as N'ДолжноÑÑ‚ÑŒ', nameP as N'ФИО' FROM Worker INNER JOIN
100 Person on Person.idP = Worker.id INNER JOIN
101 Commission on Commission.idC = Worker.idCom
102 where idCom = @idC and dateOut > getdate()
103 FETCH NEXT FROM c
104 INTO @idC, @profil
105 end
106 CLOSE c
107 DEALLOCATE c
108end
109go
110
111CREATE PROCEDURE ShowPred (@st date, @f date, @com nvarchar(40))
112as
113begin
114 declare @Preds table(
115 fio nvarchar(90),
116 fr date,
117 toT date
118 )
119 declare @fio nvarchar(90)
120 declare @idComN int
121 declare @idW int
122 declare @idCom int
123 declare @stW date
124 declare @finW date
125 declare @pos nvarchar(20)
126 DECLARE c CURSOR FOR SELECT * FROM Worker
127 OPEN c
128 FETCH NEXT FROM c
129 INTO @idW, @idCom, @stW , @finW , @pos
130 WHILE @@fetch_status = 0
131 BEGIN
132 set @idComN = (select idC from Commission where profil = @com)
133 if ((@st <= @stW and @f >= @finW) or (@st >= @stW and @f <= @finW)
134 or (@st >= @stW and @f >= @finW and @finW > @st) or (@st <= @stW and @f <= @finW and @stW < @f)) and @pos = N'ПредÑедатель' and @idCom = @idComN
135 begin
136 set @fio = (select nameP from Person where idP = @idW)
137 insert into @Preds values (@fio, @stW, @finW)
138 end
139 FETCH NEXT FROM c
140 INTO @idW, @idCom, @stW , @finW , @pos
141 END
142 CLOSE c
143 DEALLOCATE c
144
145 select fio as N'ФИО предÑедателÑ', fr as N'С', toT as N'До' from @Preds
146 group by fr, fio, toT
147end
148go
149
150
151create procedure findMiss (@st date, @f date, @com nvarchar(40))
152as
153begin
154 declare @Mis table
155 (
156 fio nvarchar(90),
157 m int
158 )
159 declare @fio nvarchar(90)
160 declare @idComN int
161 declare @idW int
162 declare @idCom int
163 declare @stW date
164 declare @finW date
165 declare @pos nvarchar(20)
166 declare @allM int
167 declare @misOwn int
168
169
170 set @idComN = (select idC from Commission where profil = @com)
171 set @allM = (select count(*) from Meeting where timeStart >= @st and timeFinish <= @f and idComm = @idComN)
172 DECLARE c CURSOR FOR SELECT * FROM Worker
173 OPEN c
174 FETCH NEXT FROM c
175 INTO @idW, @idCom, @stW , @finW , @pos
176 WHILE @@fetch_status = 0
177 BEGIN
178 if @idCom = @idComN and @finW >= @st
179 begin
180 set @fio = (select nameP from Person where idP = @idW)
181 set @misOwn = (select count(*) from Members where idM = @idComn and @idW = idP)
182 insert into @Mis values
183 (@fio, @allM-@misOwn)
184 end
185 FETCH NEXT FROM c
186 INTO @idW, @idCom, @stW , @finW , @pos
187 END
188 CLOSE c
189 DEALLOCATE c
190
191 select fio as N'ФИО', m as N'Пропущено Ñобраний' from @Mis
192end
193go
194
195
196CREATE PROCEDURE DelOld
197AS
198begin
199 declare @idW int
200 declare @idCom int
201 declare @stW date
202 declare @finW date
203 declare @pos nvarchar(20)
204
205
206 select * from Worker
207 delete from Worker where DATEADD(YEAR, 10, dateOut) < getdate()
208 select * from Worker
209
210
211 delete from Person where (select count(id) from Worker where id = idP) = 0
212 select * from Person
213 /*DECLARE c CURSOR FOR SELECT * FROM Worker
214 OPEN c
215 FETCH NEXT FROM c
216 INTO @idW, @idCom, @stW , @finW , @pos
217 WHILE @@fetch_status = 0
218 BEGIN
219
220 --if DATEADD(YEAR,10, @finW) < :
221
222
223 FETCH NEXT FROM c
224 INTO @idW, @idCom, @stW , @finW , @pos
225 END*/
226end
227go
228
229CREATE TRIGGER CheckTwo on Members after insert
230as
231begin
232 declare @com int
233 declare @per int
234 declare @mainSt dateTime
235 declare @mainFin dateTime
236 declare @secSt dateTime
237 declare @secFin dateTime
238 DECLARE c CURSOR FOR SELECT *
239 FROM INSERTED
240 OPEN c
241 FETCH NEXT FROM c
242 INTO @com, @per
243 WHILE @@fetch_status = 0
244 BEGIN
245 set @mainSt = (select timeStart from Meeting where @com = idMeet)
246 set @mainFin = (select timeFinish from Meeting where @com = idMeet)
247 declare @difCom int
248 declare @per2 int
249 DECLARE c2 CURSOR FOR SELECT *
250 FROM Members
251 OPEN c2
252 FETCH NEXT FROM c2
253 INTO @difCom, @per2
254 WHILE @@fetch_status = 0
255 begin
256 if @per2 = @per and @difCom != @com
257 begin
258 set @secSt = (select timeStart from Meeting where @difCom = idMeet)
259 set @secFin = (select timeFinish from Meeting where @difCom = idMeet)
260 if (@mainSt >= @secSt and @mainFin <= @secFin) or (@mainSt <= @secSt and @mainFin >= @secFin)
261 or (@mainSt <= @secSt and @mainFin <= @secFin and @secSt<@mainFin ) or (@mainSt >= @secSt and @mainFin >= @secFin and @secFin > @mainSt)
262 begin
263 ROLLBACK TRANSACTION
264 RAISERROR (N'Один человек находитÑÑ Ð¾Ð´Ð½Ð¾Ð²Ñ€ÐµÐ¼ÐµÐ½Ð½Ð¾ в двух меÑтах! ', 16, 1)
265 end
266 end
267 FETCH NEXT FROM c2
268 INTO @difCom, @per2
269 end
270 FETCH NEXT FROM c
271 INTO @com, @per
272
273 CLOSE c2
274 DEALLOCATE c2
275 end
276 CLOSE c
277 DEALLOCATE c
278END
279go
280
281
282ALTER TABLE Members ENABLE TRIGGER CheckTwo
283GO
284
285
286INSERT INTO Person VALUES
287(1, N'Иванов Иван Иванович', N'ЛуначарÑокго 1 32', N'89089001111', N'36354363')
288,(2, N'Петров Петр Петрович', N'Ленина 5 454', N'89085551111', N'36354300')
289,(3, N'Сидоров Семен Иванович', N'Попова 87 85', N'89089551111', N'30054363')
290,(4, N'Камнев Карл Петрович', N'Фрунзе 54 22', N'89089051111', N'36300363')
291,(5, N'Лукин Яков Семенович', N'Фурманова 46 85', N'89089501111', N'11177111')
292,(6, N'Ðвдеев Петр Владимирович', N'Ленина 46 85', N'89089500000', N'11100111')
293,(7, N'Кондратьева Ðнна Петровна', N'Сулимова 13 85', N'89087700000', N'3475158')
294
295,(8, N'Кириллов Петр Петрович', N'Ленина 15 454', N'89085554511', N'36354307')
296,(9, N'Ðнтонов Семен Иванович', N'Попова 187 85', N'89087771111', N'30054368')
297,(10, N'ФедÑев Карл Петрович', N'Фрунзе 554 22', N'89081111111', N'36300369')
298,(11, N'Ручкин Яков Семенович', N'Фурманова 146 85', N'89089001111', N'11177119')
299,(12, N'Карандашкин Петр Владимирович', N'Ленина 246 85', N'89081110000', N'11100119')
300,(13, N'ЛаÑтикова Ðнна Петровна', N'Сулимова 113 85', N'89088880000', N'3475159')
301GO
302
303/*
304INSERT INTO Members VALUES
305(1,3)
306go*/
307
308INSERT INTO Commission VALUES
309(1, N'Образование')
310,(2, N'Жилье')
311,(3, N'КоррупциÑ')
312,(4, N'ПреÑтупноÑÑ‚ÑŒ')
313,(5, N'Здоровье')
314
315
316INSERT INTO Worker VALUES
317(1,1, N'2015-05-15', N'2018-05-15', N'ПредÑедатель')
318,(2,1, N'2010-05-15', N'2012-05-15' , N'ПредÑедатель')
319,(2,2, N'2016-05-15', N'2018-05-15' , N'Член')
320,(3, 3,N'2016-05-15', N'2018-05-15' , N'ПредÑедатель')
321,(4,4, N'2013-05-15', N'2015-05-15', N'Член')
322,(5,5, N'2015-05-15', N'2017-05-15', N'ПредÑедатель')
323,(6, 5,N'2015-07-15', N'2017-07-15', N'Член')
324,(1, 4, N'2016-05-15', N'2018-05-15' , N'ПредÑедатель')
325,(7, 2, N'2015-05-15', N'2018-05-15', N'ПредÑедатель')
326,(8, 1, N'2015-05-15', N'2018-05-15', N'Член')
327,(9, 2, N'2015-05-15', N'2018-05-15', N'Член')
328,(10, 3, N'2015-05-15', N'2018-05-15', N'Член')
329,(11, 4, N'2015-05-15', N'2018-05-15', N'Член')
330,(12, 5, N'2015-05-15', N'2018-05-15', N'Член')
331
332,(9, 1, N'2015-05-15', N'2018-05-15', N'Член')
333,(10, 1, N'2015-05-15', N'2018-05-15', N'Член')
334,(11, 1, N'2015-05-15', N'2018-05-15', N'Член')
335,(12, 1, N'2015-05-15', N'2018-05-15', N'Член')
336,(13, 1, N'1990-05-15', N'1993-05-15', N'Член')
337,(13, 1, N'2010-05-15', N'2020-05-15', N'Член')
338GO
339
340INSERT INTO Meeting VALUES
341(1,1, N'12-01-16 12:32', N'12-01-16 14:32', N'A100')
342,(2,2, N'12-01-16 12:30', N'12-01-16 15:32', N'S120')
343,(3,2, N'14-01-16 12:30', N'12-01-16 15:32', N'S120')
344go
345
346INSERT INTO Members VALUES
347(1, 1)
348,(1,12)
349,(2, 3)
350,(3, 5)
351go
352
353
354SELECT *
355FROM Worker INNER JOIN
356 Person on Person.idP = Worker.id INNER JOIN
357 Commission on Commission.idC = Worker.idCom
358go
359
360PrintCom --ÑпиÑок вÑех комиÑÑий
361
362--ÑпиÑок комиÑÑий Ð´Ð»Ñ ÐºÐ°Ð¶Ð´Ð¾Ð³Ð¾ человека
363SELECT nameP as N'ФИО', profil as N'комиÑÑиÑ', position as N'должноÑÑ‚ÑŒ' FROM Commission INNER JOIN
364 Worker on id = Worker.idCom inner JOIN
365 Person on Worker.id = Person.idP
366 group by nameP, profil, position
367go
368
369--кол-во заÑедний Ð´Ð»Ñ ÐºÐ°Ð¶Ð´Ð¾Ð¹ ком. за период
370select profil as N'КомиÑÑиÑ', count(idComm) as N'количеÑтво заÑеданий' from Meeting inner join
371 Commission on Commission.idC = idComm
372 where N'13-01-16 12:30' <= timeStart and timeStart <= N'15-01-16 12:32'
373 group by profil, idComm
374go
375
376
377--ÑпиÑок заÑеданий в указанный интервал дат в хронологичеÑком порÑдке, Ð´Ð»Ñ ÐºÐ°Ð¶Ð´Ð¾Ð³Ð¾ заÑÐµÐ´Ð°Ð½Ð¸Ñ â€“ ÑпиÑок приÑутÑтвующих
378select idMeet as N'ID СобраниÑ', timeStart as N'С', timeFinish as N'До', nameP as N'ФИО' from Meeting inner join
379 Members on Members.idM = idMeet inner join
380 Person on Person.idP = Members.idP
381go
382
383ShowPred N'2000-05-15', N'2020-05-15', N'Образование' --вÑе предÑедатели за период
384go
385
386FindMiss N'2014-05-15', N'2020-05-15', N'Образование' -- ÑпиÑок членов Ñ ÑƒÐºÐ°Ð·Ð°Ð½Ð¸ÐµÐ¼ количеÑтва пропущенных заÑеданий.
387go
388
389
390--DelOld --удалить Ñтарые