lab4-1

use departaments

-- timed table
select agreementId, sum(price) as total_price
into #AgreementSummary
from Agreement
group by agreementId

select * from Agreement
join #AgreementSummary on Agreement.agreementId= #AgreementSummary.agreementId

drop table #AgreementSummary

-- if
declare @youngest Date

select @youngest = max(birthday) from Employee

if datediff(year, @youngest, getdate()) >= 18
print 'Все сотрудники совершеннолетние'
else
print 'НЕ ВСЕ совершеннолетние'

----------------
declare @sum money, @departamentName varchar(100), @countOfWork int, @amountOfGroup int

SELECT @sum = sum(price),
@departamentName = departamentName,
@countOfWork = count(*)
from DesignWork
inner join Departament on Departament.departamentId = DesignWork.departamentId
inner join Agreement on Agreement.agreementId = DesignWork.agreementId
group by Departament.departamentName
having count(*) >= 2

IF @sum > 4000
BEGIN
PRINT 'Название отдела: ' + @departamentName
PRINT 'Количество работ ' + CONVERT(varchar(50), @countOfWork)
PRINT 'Сумма ' + CONVERT(varchar(50), @sum)
END;
ELSE
PRINT 'Слишком дешево'


-- While
DECLARE @number INT, @factorial INT
SET @factorial = 1;
SET @number = 10;

WHILE @number > 0
BEGIN
SET @factorial = @factorial * @number
SET @number = @number - 1
END;

PRINT @factorial

---------

CREATE TABLE #Accounts ( CreatedAt DATE, Balance MONEY)

DECLARE @rate FLOAT, @period INT, @summa MONEY, @date DATE
SET @date = GETDATE()
SET @rate = 0.065;
SET @period = 5;
SET @summa = 10000;

WHILE @period > 0
BEGIN
INSERT INTO #Accounts VALUES(@date, @summa)
SET @period = @period - 1
SET @date = DATEADD(year, 1, @date)
SET @summa = @summa + @summa * @rate
END;

SELECT * FROM #Accounts

-- sklyar function

GO
CREATE FUNCTION dbo.calculate_work_days(@startDate date, @endDate date)
RETURNS int AS
BEGIN
RETURN datediff(DAY, @startDate, @endDate)
END

GO
drop function dbo.calculate_work_days

GO
SELECT dbo.calculate_work_days(startDate, endDate) as 'number of work days'
FROM DesignWork

-- Table value from function

GO
CREATE FUNCTION dbo.getMonthName(@DATE date) RETURNS CHAR(15)
WITH EXECUTE AS CALLER AS
BEGIN
DECLARE @month int;
DECLARE @monthName char(15);
SET @month= MONTH(@DATE)

IF (@month=1) SET @monthName='Январь';
IF (@month=2) SET @monthName='Февраль';
IF (@month=3) SET @monthName='Март';
IF (@month=4) SET @monthName='Апрель';
IF (@month=5) SET @monthName='Май';
IF (@month=6) SET @monthName='Июнь';
IF (@month=7) SET @monthName='Июль';
IF (@month=8) SET @monthName='Август';
IF (@month=9) SET @monthName='Сентябрь';
IF (@month=10) SET @monthName='Октябрь';
IF (@month=11) SET @monthName='Ноябрь';
IF (@month=12) SET @monthName='Декабрь';

RETURN(@monthName);
END;
GO
SELECT *, dbo.getMonthName(birthday) AS 'Месяц'
FROM Employee;

-- Procedure to select data
GO


-- select
create procedure Employees as
begin
select * from Employee
end
GO

exec Employees
GO

create procedure EmployeesWithMonthName as
begin
SELECT *, dbo.monthName(birthday) AS 'Месяц'
FROM Employee;
end
GO
exec EmployeesWithMonthName
GO
-- Procedure without params
GO

create procedure CurrentMonthName as
begin
print dbo.monthName(getdate())
end
GO

exec CurrentMonthName
GO

create procedure multiplication as
begin
declare @a int;
set @a = 2;
declare @b int;
set @b = 10;

print convert(varchar(5), @a * @b)
end
GO
exec multiplication

-- Procedure with params

CREATE PROCEDURE AddEmployee
@name VARCHAR(50),
@position VARCHAR(100),
@isMale bit,
@address VARCHAR(100),
@birthday date,
@departamentId int
AS
IF(not @birthday >= getdate())
INSERT INTO Employee
VALUES(@name, @position , @isMale, @address, @birthday, @departamentId)
ELSE
PRINT 'Неверная дата рождения'
RETURN
GO

EXEC AddEmployee 'Боровой Владислав', 'Техник', 1, 'Jakuba Kolasa 53/3/83', '2031-08-17', null
GO

CREATE PROCEDURE UpdateEmployee
@id int,
@birthday date
AS
update Employee
set birthday= @birthday
where @id = employeeId
GO

exec UpdateEmployee 4, '2021-08-17'
GO

select * from Organization
GO
CREATE PROCEDURE UpdateOrganizationCity
@id int,
@city nvarchar(50)
AS
IF(@city = 'Гродна')
RETURN

update Organization
set city= @city
where @id = organizationId
GO

exec UpdateOrganizationCity 4, 'Гродна'
GO

exec UpdateOrganizationCity 1, 'Киев'
GO