mvdDqtrL

1USE _Lifanov
2GO
3---1--- Объемы продаж по типам товаров за выбранный месяц;
4
5--declare @m int, @y int
6--set @m=7
7--set @y=1997
8
9--create function sale(@m int, @y int)
10--returns table
11--return (select ty.Категория, sum(zn.Количество * zn.Цена*(1-zn.Скидка)) стоимость
12--from Заказано zn
13--join Заказы z on z.КодЗаказа = zn.КодЗаказа
14--join Товары t on t.КодТовара = zn.КодТовара
15--join Типы ty on ty.КодТипа = t.КодТипа
16--and z.ДатаИсполнения is not null and @m=MONTH(z.ДатаИсполнения)
17--								 and @y=YEAR(z.ДатаИсполнения)
18--group by ty.Категория)
19
20
21--create proc sale_month @m int, @y int
22--as
23--select ty.Категория, sum(zn.Количество * zn.Цена*(1-zn.Скидка)) стоимость
24--from Заказано zn
25--join Заказы z on z.КодЗаказа = zn.КодЗаказа
26--join Товары t on t.КодТовара = zn.КодТовара
27--join Типы ty on ty.КодТипа = t.КодТипа
28--and z.ДатаИсполнения is not null and @m=MONTH(z.ДатаИсполнения)
29--								 and @y=YEAR(z.ДатаИсполнения)
30--group by ty.Категория
31
32SELECT *
33FROM sale(7, 1996)
34
35---END---
36
37---2--- Товары, имеющие объемы продаж в своем типе, выше среднего
38
39--create function sale_tovar()
40--returns table
41--return (select ty.Категория, t.КодТипа, t.Марка, sum(zn.Количество * zn.Цена*(1-zn.Скидка)) стоимость
42--from Заказано zn
43--join Заказы z on z.КодЗаказа = zn.КодЗаказа
44--join Товары t on t.КодТовара = zn.КодТовара
45--join Типы ty on ty.КодТипа = t.КодТипа
46--and z.ДатаИсполнения is not null
47--group by ty.Категория, t.КодТипа, t.Марка)
48
49--create view sred_cat as
50--select Категория, avg(стоимость) Среднее
51--from sale_tovar()
52--group by Категория
53
54--select st.Марка, st.стоимость
55--from sale_tovar() st
56--where st.стоимость > (select Среднее from sred_cat sc where st.Категория = sc.Категория)
57
58
59---3--- сотрудников- лидеров продаж в каждом году
60--create function TopSaler1(@y int)
61--returns table
62--return(
63--(select distinct s.Имя, s.Фамилия, [dbo].[SalerProfit1](s.КодСотрудника, @y) Продал
64--from Заказы z
65--join Сотрудники s on z.КодСотрудника = s.КодСотрудника
66--join Заказано zn on zn.КодЗаказа = z.КодЗаказа
67--and z.ДатаИсполнения is not null
68--and @y=YEAR(z.ДатаИсполнения))
69--)
70
71--create function SalerProfit1(@n int, @y int)
72--returns money
73--begin
74--return(
75--select sum(zn.Количество * zn.Цена*(1-zn.Скидка)) Profit
76--from Заказы z
77--join Заказано zn on zn.КодЗаказа = z.КодЗаказа
78--and z.ДатаИсполнения is not null and @y = YEAR(z.ДатаИсполнения)
79--where z.КодСотрудника = @n
80--)
81--end
82
83--create function GetAllYears()
84--returns table
85--return(
86--select distinct YEAR(z.ДатаИсполнения) Год
87--from Заказы z where z.ДатаИсполнения is not null
88--)
89
90--create view YearSotr
91--as
92--select zz.КодСотрудника Код, year(zz.ДатаИсполнения) Год, sum(z.Количество * z.Цена*(1-z.Скидка)) Продал
93--from Заказано z
94--join Заказы zz on z.КодЗаказа = zz.КодЗаказа
95--and zz.ДатаИсполнения is not null
96--group by zz.КодСотрудника, year(zz.ДатаИсполнения)
97
98--alter view MaxSum
99--as
100--select Год, max(Продал) mm
101--from YearSotr
102--group by Год
103
104--select distinct s.КодСотрудника, s.Фамилия, a.Год, m.mm
105--from Сотрудники s
106--join YearSotr a on a.Код=s.КодСотрудника
107--join MaxSum m on m.Год = a.Год and a.Продал = m.mm
108
109---3---
110--create procedure GetAllFinishedZ(@surname nvarchar(20))
111--as
112--select z.ДатаИсполнения, z.КодЗаказа from Заказы z
113--join Сотрудники s on z.КодСотрудника=s.КодСотрудника
114--and z.ДатаИсполнения is not null and s.Фамилия=@surname
115
116--GetAllFinishedZ 'Бабкина'
117---END---
118
119---------------------2.1------------------------
120
121-- CREATE VIEW PostAndTov
122--   AS
123--     SELECT
124--       p.КодПоставщика,
125--       t.КодТовара
126--     FROM Поставщики p
127--       JOIN Товары t ON p.КодПоставщика = t.КодПоставщика
128--
129--
130-- ALTER FUNCTION Price1(@id INT)
131--   RETURNS MONEY
132--   BEGIN
133--     RETURN (SELECT (z.Количество * z.Цена * (1 - z.Скидка)) [Цена]
134--             FROM Заказано z
135--             WHERE z.КодЗаказа = @id)
136--   END
137--
138ALTER FUNCTION PriceForOne(@id INT)
139  RETURNS MONEY
140  BEGIN
141    RETURN (
142      SELECT sum(t.Цена / substring(t.ЕдиницаИзмерения, 0, charindex(' ', t.ЕдиницаИзмерения)))
143      FROM Товары t
144      WHERE t.КодТовара = @id
145    )
146  END
147
148CREATE FUNCTION SkladAmount(@id INT)
149  RETURNS INT
150  BEGIN
151    RETURN (
152      SELECT cast(substring(t.ЕдиницаИзмерения, 0, charindex(' ', t.ЕдиницаИзмерения)) AS INT)
153      FROM Товары t
154      WHERE t.КодТовара = @id
155    )
156  END
157--
158--
159-- ALTER VIEW PostResult AS
160--   SELECT
161--     pt.КодПоставщика,
162--     pt.КодТовара,
163--     t.КодТипа,
164--     zk.ДатаИсполнения,
165--     dbo.Price1(z.КодЗаказа) [Сумма],
166--     dbo.Price1(z.КодЗаказа) - (z.Количество * dbo.PriceForOne(Z.КодТовара)) [Прибыль]
167--   FROM PostAndTov pt INNER JOIN Заказано z ON z.КодТовара = pt.КодТовара
168--     JOIN Заказы zk ON zk.КодЗаказа = z.КодЗаказа
169--     JOIN  Товары t on t.КодТовара=pt.КодТовара
170--
171--
172-- SELECT *
173-- FROM PostResult
174--
175-- -----------------------2.2------------------------
176-- SELECT *
177-- FROM PostResult pr WHERE pr.КодПоставщика=29 ORDER BY pr.КодТипа
178
179------------------------4.1-------------------------------
180
181
182-- ALTER TRIGGER ZAPAS_SKLADA
183-- ON _Lifanov.dbo.Заказано
184-- INSTEAD OF INSERT AS
185-- BEGIN
186--   INSERT INTO Заказано (КодЗаказа,
187--                         КодТовара,
188--                         Цена,
189--                         Количество,
190--                         Скидка,
191--                         Стоимость)
192--     SELECT
193--       i.КодЗаказа,
194--       i.КодТовара,
195--       i.Цена,
196--       i.Количество,
197--       i.Скидка,
198--       i.Стоимость
199--     FROM inserted i
200--       JOIN Товары t ON i.КодТовара = t.КодТовара
201--     WHERE i.Количество <= t.НаСкладе * cast(substring(t.ЕдиницаИзмерения, 0, charindex(' ', t.ЕдиницаИзмерения)) AS INT)
202-- END
203--
204--
205-- INSERT INTO Заказано (КодЗаказа,
206--                       КодТовара,
207--                       Цена,
208--                       Количество,
209--                       Скидка,
210--                       Стоимость) VALUES (10958, 1, 77777, 9999, 0, 0)
211--
212-- INSERT INTO Заказано (КодЗаказа,
213--                       КодТовара,
214--                       Цена,
215--                       Количество,
216--                       Скидка,
217--                       Стоимость) VALUES (10958, 1, 77777, 1, 1, 1)
218--
219
220----------------------------------------------------------4.2----------------------------------------
221-- ALTER TABLE Сотрудники
222--   ADD Архив INT;
223--
224--
225-- ALTER TRIGGER Delete_Sotr
226--   ON _Lifanov.dbo.Сотрудники
227-- INSTEAD OF DELETE AS
228--   BEGIN
229--     IF EXISTS(
230--         SELECT *
231--         FROM deleted d
232--           JOIN Заказы z ON z.КодСотрудника = d.КодСотрудника AND z.ДатаИсполнения IS NULL
233--     )
234--       BEGIN
235--         ROLLBACK;
236--         THROW 50001, 'Cannot delete Sotr', 1;
237--       END
238--     ELSE
239--       BEGIN
240--         UPDATE Сотрудники
241--         SET Архив = 1 WHERE КодСотрудника IN (SELECT КодСотрудника FROM deleted)
242--       END
243--   END
244--
245--
246-- DELETE FROM Сотрудники
247-- WHERE Фамилия = 'asd'
248--
249-- INSERT INTO Сотрудники (КодСотрудника, Фамилия, Имя, Должность, Обращение, ДатаРождения, ДатаНайма, Адрес, Город, Область, Индекс, Страна, ДомашнийТелефон, Добавочный, Фотография, Примечания, Подчиняется, Архив)
250-- VALUES (123, 'asd','asd', 'asd','asd', 1998-04-28, 1998-04-28, 'aa', 'aa', 'aa', 123, 'aa', 123213, 1,NULL , 'aa', NULL ,0)
251--
252
253-------------------------5------------------------------------------------------------------
254-- Динамический курсор по категориям(типам), их суммарной стоимости, странам клиентов,
255--  по интересующему периоду времени.
256
257
258DECLARE @m INT, @y INT
259SET @m = 8
260SET @y = 1996
261
262DECLARE SuperCursor CURSOR FOR
263  SELECT
264    ty.Категория,
265    za.СтранаПолучателя,
266    year(za.ДатаИсполнения),
267    month(za.ДатаИсполнения),
268    sum(z.Количество * z.Цена * (1 - z.Скидка))
269  FROM Типы ty
270    JOIN Товары t ON ty.КодТипа = t.КодТипа
271    JOIN Заказано z ON t.КодТовара = z.КодТовара
272    JOIN Заказы za ON za.КодЗаказа = z.КодЗаказа
273  WHERE month(za.ДатаИсполнения) = @m AND year(za.ДатаИсполнения) = @y
274  GROUP BY ty.Категория, za.СтранаПолучателя, year(za.ДатаИсполнения), month(za.ДатаИсполнения)
275
276
277DECLARE @cat VARCHAR(50),
278@country VARCHAR(50),
279@date DATETIME,
280@summa FLOAT
281
282OPEN SuperCursor
283FETCH NEXT FROM SuperCursor
284INTO @cat, @country, @date, @summa
285WHILE @@FETCH_STATUS = 0
286  BEGIN
287    PRINT 'Категория ' + @cat + ' Страна ' + @country + ' Дата ' + cast(@date AS VARCHAR(50))
288          + ' Сумма ' + cast(@summa AS VARCHAR(50))
289
290    FETCH NEXT FROM SuperCursor
291    INTO @cat, @country, @date, @summa
292  END
293CLOSE SuperCursor
294DEALLOCATE SuperCursor