Create a new version of paste:

use dbeksamen
drop table if exists timeregistration
drop table if exists employeeprojects
drop table if exists employee
drop table if exists boss
drop table if exists project
drop table if exists customer

create table customer
(
id int identity primary key,
customername varchar(50)
)
create table project
(
id int identity primary key,
projectname varchar(50),
projectstatus char(9) check(projectstatus in ('active','inactive')),
customerid int not null references customer,
fixedprice int,
estimatedhours int,
hourlyrate int
)
create table boss
(
id int identity primary key,
name varchar(50)
)
create table employee 
(
id int identity primary key,
employeename varchar(50),
bossid int not null references boss,
timeoff int
)
create table employeeprojects
(
id int identity primary key,
employeeid int not null references employee,
projectid int not null references project
)
create table timeregistration
(
id int identity primary key,
starttime datetime,
endtime datetime,
employeeid int not null references employee,
projectid int not null references project,
note varchar(500)
)
go
insert into customer values ('microsoft'), ('apple')

insert into project values ('projectOne', 'active', 1, 5000, 10, null),
('projectTwo', 'inactive', 1, null, null, 500),
('projectThree', 'active', 2, 15000, 30, null),
('projectFour', 'active', 2, 6000, 11, null)

insert into boss values ('jackson'), ('peterson')

insert into employee values ('Ravn', 1, 2), ('Frost', 2, 5), ('Dennis', 1, 3)

insert into employeeprojects values (1, 1), (1,2), (2,3), (3,4)

insert into timeregistration values ((convert(datetime,'19-05-19 06:00:00 PM',5)), (convert(datetime,'19-05-19 08:00:00 PM',5)), 1, 1, '2 hours on projectOne'),
((convert(datetime,'13-05-19 09:00:00 PM',5)), (convert(datetime,'13-05-19 11:00:00 PM',5)), 1, 2, '2 hours on projectTwo'),
((convert(datetime,'13-05-19 09:00:00 PM',5)), (convert(datetime,'13-05-19 11:00:00 PM',5)), 2, 3, '2 hours on projectThree'),
((convert(datetime,'13-05-19 09:00:00 PM',5)), (convert(datetime,'13-05-19 11:00:00 PM',5)), 3, 4, '2 hours on projectFour')

-- Ex2) ---------------------------------------------------
go
create index Employeetbl_timeoff
on employee (timeoff asc)

go
select * from employee with(index(Employeetbl_timeoff))

create index Projecttbl_fixedprice
on project (fixedprice asc)

go
select * from project with(index(Projecttbl_fixedprice))

-- Ex3) ---------------------------------------------------
go
select e.employeename
from employee e
join timeregistration t on e.id = t.employeeid
join project p on t.projectid = p.id
join customer c on c.id = p.customerid
where c.customername = 'apple'

--Ex4) -----------------------------------------------------
go
create or alter trigger timeregistrationTrigger
on timeregistration
instead of insert
as
begin
	 declare @daysbefore int
	 declare @employeeonproject int
	 declare @projectstatus char(9)
	 
	 select @daysbefore = DATEDIFF(day, GETDATE(), (select starttime from inserted))
	 if(@daysbefore <= -7)
	 begin
	 RAISERROR('Noget gik galt', 16, 1)
	 return
	 end
	 
	 select @employeeonproject = ep.employeeid 
	 from employeeprojects ep
	 where ep.employeeid = (select employeeid from inserted) and ep.projectid = (select projectid from inserted)
	 if(@employeeonproject IS NULL)
	 begin
	 RAISERROR('Noget gik galt', 16, 1)
	 return
	 end

	 select @projectstatus = p.projectstatus
	 from project p 
	 where p.id = (select projectid from inserted)
	 if(@projectstatus = 'inactive')
	 begin
	 RAISERROR('Noget gik galt', 16, 1)
	 return
	 end

	insert into timeregistration values (
		(select starttime from inserted),
		(select endtime from inserted),
		(select employeeid from inserted),
		(select projectid from inserted),
		(select note from inserted))
end

--test til krav1) 
insert into timeregistration values ((convert(datetime,'14-05-19 09:00:00 PM',5)), (convert(datetime,'13-05-19 11:00:00 PM',5)), 1, 1, '2 hours on projectOne')
--test til krav 2)
insert into timeregistration values ((convert(datetime,'20-05-19 09:00:00 PM',5)), (convert(datetime,'13-05-19 11:00:00 PM',5)), 2, 1, '2 hours on projectOne')
--test til krav 3)
insert into timeregistration values ((convert(datetime,'20-05-19 09:00:00 PM',5)), (convert(datetime,'13-05-19 11:00:00 PM',5)), 1, 2, '2 hours on projectOne')

--Ex5) ------------------------------------------------------------------
go
create or alter function dbo.antalhverdageiMinutter(@aar int ,@maaned int)
-- returnerer antal minutter i hverdage i den pågældende måned  
returns int
as
begin
declare @res int
declare @firstdayofmonth date = datefromparts(@aar,@maaned,1)
declare @firstdayofnextmonth date = dateadd(mm,1,@firstdayofmonth)
declare @lastdateofmonth date = dateadd(dd,-1,@firstdayofnextmonth)
if day(@lastdateofmonth) = 28
  set @res = 20
else
  if day(@lastdateofmonth) = 29 
    if datename(DW,@firstdayofmonth) in ('Saturday','Sunday')
      set @res = 20
    else
      set @res = 21
  else
    if day(@lastdateofmonth) = 30 
      if datename(DW,@firstdayofmonth) = 'Saturday'
        set @res = 20
      else
        if datename(DW,@firstdayofmonth) in ('Friday','Sunday')
          set @res = 21
       else set @res = 22
    else
      if day(@lastdateofmonth) = 31
        if datename(DW,@firstdayofmonth) in ('Friday','Saturday')
         set @res = 21
      else
        if datename(DW,@firstdayofmonth) in ('Thursday','Sunday')
         set @res = 22
        else set @res = 23
return 444*@res
end

go
create or alter proc updatetimeoff
	@monthParameter int,
	@yearParameter int
as
begin
	declare @employeeid int
	declare @timeoff int
	declare @starttime datetime
	declare @endtime datetime
	declare @lastid int = 1
	declare @minutes int = 0
	declare @finalminutes int = 0
	declare @numberOfRows int = 0

	declare MY_CURSOR cursor 
	local static forward_only
	for 
	select e.id, e.timeoff, t.starttime, t.endtime
	from employee e
	join timeregistration t on t.employeeid = e.id
	
	open MY_CURSOR
	fetch next from MY_CURSOR into  @employeeid, @timeoff, @starttime, @endtime
	while (@@FETCH_STATUS = 0)
	begin 
		if MONTH(@starttime) = @monthParameter and YEAR(@starttime) = @yearParameter
		begin
			if @lastid = @employeeid
			begin
				if DATENAME(DW, @starttime) = 'Sunday'
				begin
					select @minutes += DATEDIFF(MINUTE, @starttime, @endtime)
				end
				else
				begin
					select @minutes += (DATEDIFF(MINUTE, @starttime, @endtime)) * 2
				end
			end
			else
			begin
				update employee
				set timeoff += (@minutes - dbo.antalhverdageiMinutter(@yearParameter, @monthParameter))
				where id = @lastid
				set @lastid += 1
				set @minutes = 0
				set @finalminutes = 0
				if DATENAME(DW, @starttime) = 'Sunday'
				begin
					select @minutes += DATEDIFF(MINUTE, @starttime, @endtime)
				end
				else
				begin
					select @minutes += (DATEDIFF(MINUTE, @starttime, @endtime)) * 2
				end
			end
		end
		fetch next from MY_CURSOR into  @employeeid, @timeoff, @starttime, @endtime
	end
	
	close MY_CURSOR
end

exec updatetimeoff 5, 2019

select * from employee

-- Ex8) ---------------------------------
go
exec sp_addlogin 'admin','123'
exec sp_addlogin 'employee','123'

exec sp_grantdbaccess 'admin'
exec sp_grantdbaccess 'employee'

exec sp_addrole 'administrators'
exec sp_addrole 'employees'

exec sp_addrolemember 'administrators','admin'
exec sp_addrolemember 'employees','employee'

grant select, insert, update, delete
on database::dbeksamen
to [admin]

grant select, insert, update, delete
on object::dbeksamen.dbo.timeregistration
to [employee]