mDYqVQg7

1create function antalhverdage(@aar int ,@maaned int)
2-- returnerer antal hverdage i den pågældende måned
3returns int
4as
5begin
6declare @res int
7declare @firstdayofmonth date = datefromparts(@aar,@maaned,1)
8declare @firstdayofnextmonth date = dateadd(mm,1,@firstdayofmonth)
9declare @lastdateofmonth date = dateadd(dd,-1,@firstdayofnextmonth)
10if day(@lastdateofmonth) = 28
11set @res = 20
12else
13if day(@lastdateofmonth) = 29
14if datename(DW,@firstdayofmonth) in ('Saturday','Sunday')
15set @res = 20
16else
17set @res = 21
18else
19if day(@lastdateofmonth) = 30
20if datename(DW,@firstdayofmonth) = 'Saturday'
21set @res = 20
22else
23 if datename(DW,@firstdayofmonth) in ('Friday','Sunday')
24 set @res = 21
25 else set @res = 22
26else
27if day(@lastdateofmonth) = 31
28if datename(DW,@firstdayofmonth) in ('Friday','Saturday')
29set @res = 21
30else
31 if datename(DW,@firstdayofmonth) in ('Thursday','Sunday')
32 set @res = 22
33 else set @res = 23
34return @res
35end
36
37drop table timeReg;
38drop table Opgave;
39drop table Ansat;
40drop table Chef
41
42create table Chef(
43chefID int not null identity(1,1),
44f_navn varchar(50) not null,
45e_navn varchar(50) not null,
46primary key (chefID)
47)
48create table Ansat(
49ansatID int not null identity(1,1),
50f_navn varchar(50) not null,
51e_navn varchar(50) not null,
52timeSaldo decimal(6,2) not null default '0',
53chefID int foreign key references Chef(chefID),
54primary key (ansatID)
55)
56create table Opgave(
57opgaveID int not null identity(1,1),
58opgaveNavn varchar(80),
59aktiv bit not null
60primary key (opgaveID)
61)
62create table timeReg(
63ID int not null identity(1,1),
64dato date not null,
65tidsforbrug decimal(6,2) not null,
66ansatID int foreign key references Ansat(ansatID),
67opgaveID int foreign key references Opgave(opgaveID),
68primary key (ID)
69)
70insert into Chef values ( 'Kurt', 'Jensen'); 
71insert into Chef values ( 'Arne', 'Jørgensen');
72insert into Chef values ( 'Donald', 'Trumpf');
73insert into Ansat values ( 'Bill', 'Clinton', 0, 3);
74insert into Ansat values ( 'Per', 'Vers', 0, 2);
75insert into Ansat values ( 'Sidney', 'Lee', 0, 3);
76insert into Ansat values ( 'Egon', 'Olsen', 0, 1);
77insert into Opgave values ( 'Bankkup', 1);
78insert into Opgave values ( 'Byg dødsstjernen', 1);
79insert into Opgave values ( 'Mal Mona Lisa', 1);
80insert into Opgave values ( 'Genoptæl stemmer fra valget', 0);
81insert into timeReg values ( '2016-11-08', 12.3, 1, 4);
82insert into timeReg values ( '1506-03-15', 1.5, 3, 3);
83insert into timeReg values ( '1972-10-06', 7.4, 4, 1);
84insert into timeReg values ( '1972-10-06', 7.4, 3, 1);
85insert into timeReg values ( '2016-11-08', 12.3, 2, 4);
86insert into timeReg values ( '2016-11-08', 12.3, 4, 4);
87insert into timeReg values ( '2016-11-07', 12.3, 4, 4);
88--2.A
89select opgaveNavn, SUM(tidsforbrug) as Tidsforbrug
90from opgave o join timeReg t on (o.opgaveID = t.opgaveID)
91where YEAR(t.dato) = 2016
92group by opgaveNavn
93--2.B
94select opgaveNavn
95from opgave
96where opgaveID NOT IN (
97select opgaveID from timeReg
98)
99--3.
100create view ansatoversigt as
101select f_navn, e_navn, opgaveNavn, SUM(tidsforbrug) as Tidsforbrug
102from opgave o join timeReg t on (o.opgaveID = t.opgaveID) join Ansat a on (t.ansatID = a.ansatID)
103group by opgaveNavn, f_navn, e_navn
104
105--4.
106
107alter proc optaeltimer(@ansatID int) as
108declare @month int = dbo.antalhverdage(year(getdate()), month(getdate()))
109declare @dato date = dateadd(day,-@month,getdate())
110declare @antaltimer decimal(6,2) = 
111(select SUM(tidsforbrug) as tidsforbrug
112from timeReg 
113where ansatID = @ansatID and dato > @dato)
114
115update Ansat
116set timeSaldo = timeSaldo - ((@month*7.4) - @antaltimer)
117where ansatID = @ansatID
118
119exec optaeltimer 4
120
121-- 5.
122
123create trigger reglimit on timeReg
124for insert as
125if exists (select * from timeReg group by ansatID, opgaveID, dato having count(*) > 0)
126begin
127raiserror('Du har allerede tidsregistreret denne opgave i dag!',16,1)
128rollback tran
129return
130end
131go
132
133--6.