чужие лабы бд и прочее / ffff

USE Склад_326
GO


/*1 Создайте хранимую процедуру pr_КолебанияСпросаТоваров, которая решает
рассмотренную выше (см. раздел I) задачу определения наименования товара,
по которому был наибольший или наименьший спрос за последние N дней. Эта
процедура должна иметь два входных параметра (@Интервал, @ТипРезультата) и
два выходных параметра (@Имя, @Итог). Если значение входного параметра @ТипРезультата равно 1,
находится товар наибольшего спроса. Если же значение параметра равно 2 – находится товар наименьшего спроса.*/

CREATE PROCEDURE pr_КолебанияСпросаТоваров
@Интервал INT ,
@ТипРезультата INT,
@Имя char(20) OUTPUT,
@Итог INT OUTPUT
AS
DECLARE @Max INT, @Min INT
IF @ТипРезультата = 1
BEGIN
SELECT @Итог = КодТовара, @Max = SUM(Количество)
FROM Заказ
WHERE ДатаЗаказа BETWEEN GetDate() - @Интервал AND GetDate()
GROUP BY КодТовара
ORDER BY SUM(Количество)
SELECT @Имя = Наименование
FROM Товар
WHERE КодТовара = @Итог
END
ELSE
BEGIN
SELECT @Итог = КодТовара, @Min = SUM(Количество)
FROM Заказ
WHERE ДатаЗаказа BETWEEN GetDate() - @Интервал AND GetDate()
GROUP BY КодТовара
ORDER BY SUM(Количество) DESC
SELECT @Имя = Наименование
FROM Товар
WHERE КодТовара = @Итог
END
GO



DECLARE @Интервал1 INT, @ТипРезультата1 INT, @Имя1 char(20) , @Итог1 INT
SELECT @Интервал1 = 1000, @ТипРезультата1 = 1
EXEC pr_КолебанияСпросаТоваров @Интервал1, @ТипРезультата1 , @Имя1 OUTPUT, @Итог1 OUTPUT
SELECT @Имя1 AS [Наименование], @Итог1 AS [Код Товара]
GO

DROP PROCEDURE pr_КолебанияСпросаТоваров -- удаляем проецедуру
GO

/*2
Создайте хранимую процедуру pr_КлиентПоставщик_СтранаИнтервал,
которая подсчитывает, сколько различных клиентов и различных поставщиков
из указанной страны фигурирует в таблице Заказ, причем анализируются только те заказы,
в которых значение поля Дата заказа попадает в указанный интервал дат. Эта процедура
должна иметь три входных параметра (@Страна, @НачалоИнтервала, @КонецИнтервала) и два
выходных параметра (@ЧислоКлиентов, @ЧислоПоставщиков). Если же значение параметра
@Страна не будет указано (т.е. будет равно NULL), то подсчет клиентов и поставщиков
должен вестись независимо от их национальной принадлежности.*/

CREATE PROCEDURE pr_КлиентПоставщик_СтранаИнтервал
@Страна CHAR(20),
@НачалоИнтервала DATETIME,
@КонецИнтервала DATETIME,
@ЧислоКлиентов INT OUTPUT,
@ЧислоПоставщиков INT OUTPUT
AS
IF @Страна IS NOT NULL
BEGIN
CREATE TABLE #goods (
Страна CHAR(20),
ДатаЗаказа DATETIME,
СрокПоставки DATETIME,
Код INT,
КодРегиона INT
)
INSERT #goods
SELECT Регион.Страна, Заказ.ДатаЗаказа, Заказ.СрокПоставки, Заказ.КодПоставщика, Поставщик.КодРегиона
FROM Заказ
INNER JOIN Поставщик ON Заказ.КодПоставщика= Поставщик.КодПоставщика
INNER JOIN Регион ON Поставщик.КодРегиона = Регион.КодРегиона
WHERE Регион.Страна = @Страна AND ДатаЗаказа BETWEEN @НачалоИнтервала AND @КонецИнтервала
ORDER BY Поставщик.КодПоставщика
SELECT @ЧислоПоставщиков = COUNT(DISTINCT Код)
FROM #goods
DELETE FROM #goods
INSERT #goods
SELECT Регион.Страна, Заказ.ДатаЗаказа, Заказ.СрокПоставки, Заказ.КодКлиента
FROM Заказ
INNER JOIN Клиент ON Заказ.КодКлиента = Клиент.КодКлиента
INNER JOIN Регион ON Клиент.КодРегиона = Регион.КодРегиона
WHERE Регион.Страна = @Страна AND ДатаЗаказа BETWEEN @НачалоИнтервала AND @КонецИнтервала
SELECT @ЧислоКлиентов = COUNT(DISTINCT Код)
FROM #goods
END
ELSE
BEGIN
CREATE TABLE #goods2 (
ДатаЗаказа DATETIME,
СрокПоставки DATETIME,
Код INT,
КодРегиона INT
)
INSERT #goods2
SELECT Заказ.ДатаЗаказа, Заказ.СрокПоставки, Заказ.КодПоставщика, Поставщик.КодРегиона
FROM Заказ
INNER JOIN Поставщик ON Заказ.КодПоставщика= Поставщик.КодПоставщика
INNER JOIN Регион ON Поставщик.КодРегиона = Регион.КодРегиона
WHERE ДатаЗаказа BETWEEN @НачалоИнтервала AND @КонецИнтервала
ORDER BY Поставщик.КодПоставщика
SELECT @ЧислоПоставщиков = COUNT(DISTINCT Код)
FROM #goods2
DELETE FROM #goods2
INSERT #goods2
SELECT Заказ.ДатаЗаказа, Заказ.СрокПоставки, Заказ.КодКлиента, Клиент.КодКлиента
FROM Заказ
INNER JOIN Клиент ON Заказ.КодКлиента= Клиент.КодКлиента
INNER JOIN Регион ON Клиент.КодРегиона = Регион.КодРегиона
WHERE ДатаЗаказа BETWEEN @НачалоИнтервала AND @КонецИнтервала
SELECT @ЧислоКлиентов = COUNT(DISTINCT Код)
FROM #goods2
END

GO

SET DATEFORMAT dmy
DECLARE @Страна1 CHAR(20), @НачалоИнтервала1 DATETIME, @КонецИнтервала1 DATETIME, @ЧислоКлиентов1 INT , @ЧислоПоставщиков1 INT
SELECT @Страна1 = 'Беларусь', @НачалоИнтервала1 = '01.01.2010', @КонецИнтервала1 = '01.06.2021'
EXEC pr_КлиентПоставщик_СтранаИнтервал @Страна1, @НачалоИнтервала1, @КонецИнтервала1, @ЧислоКлиентов1 OUTPUT , @ЧислоПоставщиков1 OUTPUT
SELECT @ЧислоКлиентов1 AS [Количество клиентов], @ЧислоПоставщиков1 AS [Количество Поставщиков]
GO

DROP PROCEDURE pr_КлиентПоставщик_СтранаИнтервал
GO

/*3 Создайте хранимую процедуру pr_Товар_СтранаВалютаИнтервал,
которая подсчитывает, сколько различных товаров в конкретной
валюте было заказано клиентами из указанной страны, причем анализируются только те заказы,
в которых значение поля Дата заказа попадает в заданный интервал дат. Эта процедура должна
иметь четыре входных параметра (@Страна, @Валюта, @НачалоИнтервала, @КонецИнтервала) и один
выходной параметр (@ЧислоТоваров). При этом расширьте возможности процедуры следующим образом:
если значение параметра @Страна не будет указано (т.е. будет равно NULL), то подсчет товаров
должен вестись независимо от национальной принадлежности клиента;
если значение параметра @Валюта не будет указано (т.е. будет равно NULL), то подсчет товаров
должен вестись применительно к национальной валюте (код валюты – BYR).*/

CREATE PROCEDURE pr_Товар_СтранаВалютаИнтервал
@Страна VARCHAR(30),
@Валюта VARCHAR(30) = 'BYR',
@НачалоИнтервала DATETIME,
@КонецИнтервала DATETIME
AS
DECLARE @ЧислоТоваров INT
SET @ЧислоТоваров = 0
IF @Валюта IS NULL
BEGIN
SELECT Заказ.КодТовара, Заказ.Количество
INTO #Товары1
FROM Заказ
INNER JOIN Клиент
ON Заказ.КодКлиента = Клиент.КодКлиента
WHERE (Заказ.ДатаЗаказа BETWEEN @НачалоИнтервала AND @КонецИнтервала)
SELECT @ЧислоТоваров = COUNT(DISTINCT Количество)
FROM #Товары1
INNER JOIN Товар ON #Товары1.КодТовара = Товар.КодТовара
WHERE Товар.КодВалюты = @Валюта
END

ELSE
BEGIN
SELECT Заказ.КодТовара, Заказ.Количество
INTO #Товары2
FROM Заказ
INNER JOIN Клиент
ON Заказ.КодКлиента = Клиент.КодКлиента
INNER JOIN Регион
ON Клиент.КодРегиона = Регион.КодРегиона
WHERE (Заказ.ДатаЗаказа BETWEEN @НачалоИнтервала AND @КонецИнтервала) AND Регион.Страна = @Страна

SELECT @ЧислоТоваров = COUNT(DISTINCT Количество)
FROM #Товары2
INNER JOIN Товар ON #Товары2.КодТовара = Товар.КодТовара
WHERE Товар.КодВалюты = @Валюта
END

SELECT @ЧислоТоваров [Количество различных товаров]
GO


EXEC pr_Товар_СтранаВалютаИнтервал 'Беларусь', BYR, "02.01.2006", "02.09.2021"
GO

DROP PROCEDURE pr_Товар_СтранаВалютаИнтервал
GO

/*4Создайте пользовательскую функцию fn_getЧислоДней_вМесяце типа Scalar(скалярное значение
любого из типов данных), которая для
конкретной даты возвращает число дней в месяце, который определяется этой датой
(високосность года не учитывается). Эта функция должна иметь один входной параметр (@Дата).*/

CREATE FUNCTION fn_getЧислоДней_вМесяце
(@Дата DATETIME)
RETURNS INT
BEGIN
DECLARE @Days INT
SET @DAYS = DATEDIFF(DAY, @Дата, DATEADD(MONTH,1,@Дата))
RETURN @Days
END
GO

SET DATEFORMAT dmy
DECLARE @Дата1 DATETIME
SELECT @Дата1 = '16.05.2021'
SELECT dbo.fn_getЧислоДней_вМесяце(@Дата1) AS [Количество дней]
GO

DROP FUNCTION fn_getЧислоДней_вМесяце
GO

/*5. Создайте пользовательскую функцию fn_getФИО_вФормате типа Scalar, которая на основе
текстовой строки, содержащей фамилию, имя и отчество, формирует текстовую строку в одном
из следующих форматов:
1) исходная строка переводится в верхний регистр;
2) исходная строка переводится в нижний регистр;
3) на верхнем регистре должны быть только первые буквы слов;
4) выводится только фамилия, а имя и отчество заменяются их первыми буквами с точкой.
Эта функция должна иметь два входных параметра (@ФИО, @Формат).
Усложненный вариант. Расширьте возможности функции таким образом, чтобы была
допустима исходная строка (задаваемая параметром @ФИО), содержащая не один, а несколько
пробелов между фамилией и именем или между именем и отчеством, а также допускающая наличие
лидирующих пробелов перед фамилией.*/



CREATE FUNCTION fn_getФИО_вФормате
(@ФИО char(30), @Формат int)
RETURNS CHAR(30)
BEGIN
DECLARE @RESULT char(30)
DECLARE @surname char(10)
DECLARE @name char(10)
DECLARE @patronymic char(10)
IF @Формат = 1
BEGIN
SELECT @RESULT = UPPER(@ФИО)
END
IF @Формат = 2
BEGIN
SELECT @RESULT = LOWER(@ФИО)
END
IF @Формат = 3
BEGIN /*CHARINDEX(substring, string, start)
SUBSTRING(string, start, length)*/
DECLARE @pos INT
SELECT @pos = CHARINDEX(' ', @ФИО)
SELECT @surname = SUBSTRING(@ФИО, 1, @pos-1)
SELECT @ФИО = SUBSTRING(@ФИО, @pos+1, LEN(@ФИО)-@pos)
SELECT @pos = CHARINDEX(' ', @ФИО)
SELECT @name = SUBSTRING(@ФИО, 1, @pos-1)
SELECT @ФИО = SUBSTRING(@ФИО, @pos+1, LEN(@ФИО)-@pos)
SELECT @patronymic = @ФИО
SELECT @RESULT = UPPER(LEFT(@surname,1))+LOWER(SUBSTRING(@surname,2,LEN(@surname)))+' '+
UPPER(LEFT(@name,1))+LOWER(SUBSTRING(@name,2,LEN(@name)))+' '+
UPPER(LEFT(@patronymic,1))+LOWER(SUBSTRING(@patronymic,2,LEN(@patronymic)))
RETURN @RESULT
/*
SELECT @surname = SUBSTRING (@ФИО, 1, CHARINDEX(' ', @ФИО))
SELECT @surname = LOWER(@surname)
SELECT @surname = UPPER(LEFT(@surname,1))+SUBSTRING(@surname,2,LEN(@surname)-1)
SELECT @name = SUBSTRING (@ФИО, CHARINDEX(' ', @ФИО)+1, CHARINDEX(' ', @ФИО,CHARINDEX(' ',LEN(@ФИО))))
SELECT @name = LOWER(@name)
SELECT @name = UPPER(LEFT(@name,1))+SUBSTRING(@name,2,LEN(@name))
SELECT @patronymic = SUBSTRING (@ФИО, CHARINDEX(' ', @ФИО,CHARINDEX(' ', @ФИО)+1)+1,LEN(@ФИО))
SELECT @patronymic = LOWER(@patronymic)
SELECT @patronymic = UPPER(LEFT(@patronymic,1))+SUBSTRING(@patronymic,2,LEN(@patronymic))
SELECT @RESULT = @surname + @name + @patronymic*/
END
IF @Формат = 4
BEGIN
SELECT @surname = SUBSTRING (@ФИО, 1, CHARINDEX(' ', @ФИО))
SELECT @surname = LOWER(@surname)
SELECT @surname = UPPER(LEFT(@surname,1))+SUBSTRING(@surname,2,LEN(@surname))
SELECT @name = SUBSTRING (@ФИО, CHARINDEX(' ', @ФИО)+1, CHARINDEX(' ', @ФИО,CHARINDEX(' ',LEN(@ФИО))))
SELECT @name = LOWER(@name)
SELECT @name = UPPER(LEFT(@name,1)) + '.'
SELECT @patronymic = SUBSTRING (@ФИО, CHARINDEX(' ', @ФИО,CHARINDEX(' ', @ФИО)+1)+1,LEN(@ФИО))
SELECT @patronymic = LOWER(@patronymic)
SELECT @patronymic = UPPER(LEFT(@patronymic,1)) + '.'
SELECT @RESULT = @surname + @name + @patronymic
END
RETURN @RESULT
END
GO


DECLARE @ФИО1 char(30)
DECLARE @Формат1 int
SET @ФИО1 ='Хомякова Анна Александро'
SELECT dbo.fn_getФИО_вФормате(@ФИО1,3) AS [Работа со строкой]
GO

DROP FUNCTION fn_getФИО_вФормате
GO




--6Создайте пользовательскую функцию fn_getGroup_НаименованиеВалюта типа Inline Table-valued, которая возвращает
--таблицу со следующими столбцами:

--Наименование товара
--Имя валюты
--Заказанное кол-во
--Стоимость в национальной валюте



--Эта таблица должна отражать результат группировки данных по полям Наименование и ИмяВалюты. Для каждой
-- такой группы подсчитывается итоговое количество заказанного товара и итоговая стоимость в национальной валюте.
--Пользовательская функция fn_getGroup_НаименованиеВалюта должна иметь два входных параметра (@НачалоИнтервала, @КонецИнтервала),
-- поэтому при формировании результирующей таблицы необходимо учитывать только те строки из таблицы Заказ, в которых значение
-- поля Дата заказа попадает в указанный параметрами интервал дат.



CREATE FUNCTION fn_getGroup_НаименованиеВалюта
(@НачалоИнтервала DATE, @КонецИнтервала DATE)
RETURNS TABLE
AS RETURN
SELECT Товар.Наименование as [Наименование товара], Валюта.ИмяВалюты AS [Имя валюты], SUM(Заказ.Количество) AS Количество, (SUM(Заказ.Количество) * Товар.Цена) AS [Стоимость в валюте]
FROM Заказ
INNER JOIN Товар ON Заказ.КодТовара = Товар.КодТовара
INNER JOIN Валюта ON Валюта.КодВалюты = Товар.КодВалюты
WHERE (Заказ.ДатаЗаказа BETWEEN @НачалоИнтервала AND @КонецИнтервала)
GROUP BY Товар.Наименование, Валюта.ИмяВалюты, Товар.Цена
GO

SET DATEFORMAT dmy
SELECT * FROM fn_getGroup_НаименованиеВалюта('01.07.2008','15.07.2021')
GO

DROP FUNCTION fn_getGroup_НаименованиеВалюта
GO


--7Создайте пользовательскую функцию fn_getTable_СтоимостьНВ типа Multi-statement Table-valued,
--которая возвращает таблицу со следующими столбцами:

/*Номер
Дата заказа
Имя клиента
Наименование товара
Количество
Цена в НВ
Стоимость в НВ*/


/*Эта таблица строится в три этапа. Сначала создается таблица со столбцами, показанными выше, где
столбец Номер является автоинкрементным первичным ключом,
столбцы Цена в НВ (цена в национальной валюте)
и Стоимость в НВ (стоимость в национальной валюте) являются вычисляемыми.
Число строк этой таблицы будет равно числу строк в таблице Заказ.

На втором этапе подсчитывается средняя стоимость в национальной валюте всех заказанных товаров.

На третьем этапе из полученной таблицы удаляются все те строки, в которых значение столбца Стоимость в НВ будет меньше,
чем подсчитанная на втором этапе средняя стоимость в национальной валюте. В результате будет получена таблица, которую и должна
возвращать данная пользовательская функция. Эта функция не имеет ни одного входного параметра. */

CREATE FUNCTION fn_getTable_СтоимостьНВ ()
RETURNS @multi TABLE (
Номер INT IDENTITY(1,1) PRIMARY KEY,
ДатаЗаказа DATE NOT NULL,
ИмяКлиента VARCHAR(30) NOT NULL,
НаименованиеТовара VARCHAR(20) NOT NULL,
Количество INT NOT NULL,
ЦенаВНацВалюте INT NOT NULL,
СтоимостьВНацВалюте INT NOT NULL)
BEGIN

DECLARE @rowset TABLE (
Номер INT IDENTITY(1,1) PRIMARY KEY,
ДатаЗаказа date NOT NULL,
ИмяКлиента varchar(30) NOT NULL,
НаименованиеТовара VARCHAR(20) NOT NULL,
Количество INT NOT NULL,
ЦенаВНацВалюте INT NULL,
СтоимостьВНацВалюте INT NULL)
INSERT @rowset (ДатаЗаказа, ИмяКлиента, НаименованиеТовара, Количество, ЦенаВНацВалюте, СтоимостьВНацВалюте)
SELECT ДатаЗаказа, ИмяКлиента, Наименование, Количество, Цена, (Цена*Количество)
FROM Заказ
INNER JOIN Клиент ON Заказ.КодКлиента=Клиент.КодКлиента
INNER JOIN Товар ON Заказ.КодТовара = Товар.КодТовара
DECLARE @Price INT
SELECT @Price=AVG(СтоимостьВНацВалюте)

FROM @rowset
DELETE
FROM @rowset
WHERE СтоимостьВНацВалюте < @Price
INSERT @multi
SELECT ДатаЗаказа, ИмяКлиента, НаименованиеТовара, Количество, ЦенаВНацВалюте, СтоимостьВНацВалюте
FROM @rowset
RETURN
END
GO

SELECT * FROM fn_getTable_СтоимостьНВ()
GO

DROP FUNCTION fn_getTable_СтоимостьНВ




/*
DECLARE @Counter INT
SELECT @Counter = count(*)
FROM @rowset
SELECT @Price = (SUM(СтоимостьВНацВалюте)/@Counter)*/



CREATE PROCEDURE pr_СоздатьТаблицу
@Имя CHAR(20) ,
@Фамилия CHAR(20),
@Возраст INT
AS
--DECLARE @Имя CHAR(20), @Фамилия CHAR(20),@Возраст INT
CREATE TABLE #ABC (
A CHAR(20),
B CHAR(20),
C INT
)
INSERT INTO #ABC
VALUES(@Имя, @Фамилия,@Возраст)
SELECT @Имя AS [A], @Фамилия AS [B], @Возраст AS [C]

GO

DECLARE @Имя1 CHAR(20), @Фамилия1 CHAR(20), @Возраст1 INT
SELECT @Имя1 = 'Анна', @Фамилия1 = 'Хомякова', @Возраст1 = 18
EXEC pr_СоздатьТаблицу @Имя1, @Фамилия1, @Возраст1
GO

DROP PROCEDURE pr_СоздатьТаблицу
GO