Laba_4_BD_funcs+def

--task 1
create or alter function SalaryByComp(@compName varchar(30))
returns table
as
return
(
select avg(salary) 'Зарплата'
from Cooperator
join Department on Cooperator.Dept_id = Department.Dept_id
join Company on Company.Company_id = Department.Company_id
where Company.Name = @compName
)

select * from SalaryByComp('ПАО АНК Башнефть')

--task 2
create or alter function QuantEmp(@months int)
returns int
as
begin
declare @now date = GETDATE()
declare @count int
set @count = (select COUNT(Cooperator.Cooperator_id)
                from Cooperator
                where DATEDIFF(MONTH, Cooperator.Start_date, @now) <= @months)
return @count
end

select dbo.QuantEmp(10) 'Работники'

--task 3
create or alter function EmpAllGrades(@deptName varchar(30))
returns table
as
return
(
select c.Name, c.Surname, c.Category
from Cooperator c
join Department on c.Dept_id = Department.Dept_id
where Department.Name = @deptName and c.Cooperator_id in (select Cooperator.Cooperator_id
                                                            from Cooperator, Evaluation
                                                            where Evaluation.Cooperator_id = Cooperator.Cooperator_id and Evaluation.Status = 3)
                                and c.Cooperator_id in (select Cooperator.Cooperator_id
                                                            from Cooperator, Evaluation
                                                            where Evaluation.Cooperator_id = Cooperator.Cooperator_id and Evaluation.Status = 4)
                                and c.Cooperator_id in (select Cooperator.Cooperator_id
                                                            from Cooperator, Evaluation
                                                            where Evaluation.Cooperator_id = Cooperator.Cooperator_id and Evaluation.Status = 5)
)

select * from EmpAllGrades('IT-отдел')

--task  4

create or alter function DeptsWLow()
returns table
as
return
(
select top(2) count(c.Cooperator_id) 'Количество работников', d.Name
from Department d
JOIN Cooperator c ON d.Dept_id = c.Dept_id
GROUP BY d.Dept_id,  d.Name
ORDER BY count(c.Cooperator_id)
)

select * from DeptsWLow()


--task 5
create or alter function  getCoops()
returns @f_coops table
(
    Coop_id int primary key,
    Coop_name varchar(30),
    Coop_surn varchar(30),
    Coop_cat varchar(30),
    Last_status tinyint,
    Coop_res varchar(30)
)
as
begin

    insert @f_coops
    select c.Cooperator_id, c.Name, c.Surname, c.Category, e.Status, 'Годен'
    from Cooperator c, Evaluation e
    where (e.Date = (select max(e.Date)
                    from Evaluation e
                    where e.Cooperator_id = c.Cooperator_id)) and e.Status >= 3

    union
    select c.Cooperator_id, c.Name, c.Surname, c.Category, e.Status, 'Уволен'
    from Cooperator c, Evaluation e
    where (e.Date = (select max(e.Date)
                    from Evaluation e
                    where e.Cooperator_id = c.Cooperator_id)) and e.Status < 3
    return
end

select * from dbo.getCoops()

--zashita
/*
посчитать сколько сотруднков у которых все оценки 5, сколько сотрудников у которых все 4
на вход отдел
*/


create or alter function  Coop4n5(@deptName varchar(30))
returns @st_coops table
(
    Coop_id int,
    Coop_name varchar(30),
    Coop_surn varchar(30),
    Coop_status varchar(30)
)
as
begin

    insert @st_coops
    select c.Cooperator_id , c.Name, c.Surname, 'все 5'
    from Cooperator c, Evaluation e, Department d
    where d.Name = @deptName and d.Dept_id = c.Dept_id and e.Status = 5 and e.Cooperator_id = c.Cooperator_id  and c.Cooperator_id not in (select Cooperator.Cooperator_id
                                                            from Cooperator, Evaluation
                                                            where Evaluation.Cooperator_id = Cooperator.Cooperator_id and Evaluation.Status <= 4)

    union
    select c.Cooperator_id , c.Name, c.Surname, 'все 4'
    from Cooperator c, Evaluation e, Department d
    where d.Name = @deptName and d.Dept_id = c.Dept_id and e.Status = 4  and e.Cooperator_id = c.Cooperator_id  and c.Cooperator_id not in (select Cooperator.Cooperator_id
                                                            from Cooperator, Evaluation
                                                            where Evaluation.Cooperator_id = Cooperator.Cooperator_id and Evaluation.Status <= 3 or Evaluation.Status = 5 and Evaluation.Cooperator_id = Cooperator.Cooperator_id)


    return
end


select * from dbo.Coop4n5('Отдел Инженеров')