· 9 years ago · Jan 08, 2017, 06:22 PM
1
2-- 4 zadatak
3
4create view rukovodioci as
5select r.idzaposlenog, r.ime, rac.idracuna, rac.datum, rac.vrednost
6from radnik r join racun rac on rac.idzaposlenog = r.idzaposlenog
7where idrukovodioca is null
8
9create view radnici as
10select r.idzaposlenog, r.ime, rac.idracuna, rac.datum, rac.vrednost
11from radnik r join racun rac on rac.idzaposlenog = r.idzaposlenog
12where idrukovodioca is not null
13
14
15
16create function konkurencija(@godina int)
17returns table
18as
19return
20(
21 select r.idzaposlenog as idBEST, SUM(r.vrednost) as zarada
22 from radnici r
23 where DATEPART(YEAR, r.datum) = @godina
24 group by r.idzaposlenog
25)
26
27select * from dbo.konkurencija(2017)
28
29
30create function dajBrojRadnika(@idRukovodioca int)
31returns int
32as
33begin
34 declare @broj int
35 select distinct @broj=COUNT(idzaposlenog)
36 from radnik
37 where idrukovodioca = @idRukovodioca
38 group by idrukovodioca
39
40 return @broj
41end
42
43
44create function najlosijiRukovodioc(@godina int)
45returns int
46as
47begin
48 declare @id int
49 declare @minZarada decimal(10,2)
50
51 select @minZarada=MIN(t.zarada)
52 from
53 (
54 select idzaposlenog, SUM(vrednost) as zarada
55 from rukovodioci
56 where DATEPART(year, datum) = @godina
57 group by idzaposlenog
58 ) t
59
60 select @id=t.idzaposlenog
61 from
62 (
63 select idzaposlenog
64 from rukovodioci
65 where DATEPART(year, datum) = @godina
66 group by idzaposlenog
67 having SUM(vrednost) = @minZarada
68 ) t
69
70
71 if exists
72 (
73 select a.idzaposlenog
74 from
75 (
76 select idzaposlenog, SUM(vrednost) as zarada
77 from rukovodioci
78 where DATEPART(year, datum) = @godina
79 group by idzaposlenog
80 ) a join
81 (
82 select idzaposlenog, SUM(vrednost) as zarada
83 from rukovodioci
84 where DATEPART(year, datum) = @godina
85 group by idzaposlenog
86 ) b on (a.zarada = b.zarada and a.idzaposlenog <> b.idzaposlenog)
87 or
88 (a.zarada = b.zarada and a.idzaposlenog <> b.idzaposlenog and dbo.dajBrojRadnika(a.idzaposlenog) > dbo.dajBrojRadnika(b.idzaposlenog))
89 or
90 (a.zarada = b.zarada and a.idzaposlenog <> b.idzaposlenog and dbo.dajBrojRadnika(a.idzaposlenog) >= dbo.dajBrojRadnika(b.idzaposlenog) and dbo.Staz(a.idzaposlenog, @godina) < dbo.Staz(b.idzaposlenog, @godina))
91 or
92 (a.zarada = b.zarada and a.idzaposlenog <> b.idzaposlenog and dbo.dajBrojRadnika(a.idzaposlenog) >= dbo.dajBrojRadnika(b.idzaposlenog) and dbo.Staz(a.idzaposlenog, @godina) <= dbo.Staz(b.idzaposlenog, @godina) and a.idzaposlenog < b.idzaposlenog)
93 )
94 begin
95 select @id=a.idzaposlenog
96 from
97 (
98 select idzaposlenog, SUM(vrednost) as zarada
99 from rukovodioci
100 where DATEPART(year, datum) = @godina
101 group by idzaposlenog
102 ) a join
103 (
104 select idzaposlenog, SUM(vrednost) as zarada
105 from rukovodioci
106 where DATEPART(year, datum) = @godina
107 group by idzaposlenog
108 ) b on (a.zarada = b.zarada and a.idzaposlenog <> b.idzaposlenog)
109 or
110 (a.zarada = b.zarada and a.idzaposlenog <> b.idzaposlenog and dbo.dajBrojRadnika(a.idzaposlenog) > dbo.dajBrojRadnika(b.idzaposlenog))
111 or
112 (a.zarada = b.zarada and a.idzaposlenog <> b.idzaposlenog and dbo.dajBrojRadnika(a.idzaposlenog) >= dbo.dajBrojRadnika(b.idzaposlenog) and dbo.Staz(a.idzaposlenog, @godina) < dbo.Staz(b.idzaposlenog, @godina))
113 or
114 (a.zarada = b.zarada and a.idzaposlenog <> b.idzaposlenog and dbo.dajBrojRadnika(a.idzaposlenog) >= dbo.dajBrojRadnika(b.idzaposlenog) and dbo.Staz(a.idzaposlenog, @godina) <= dbo.Staz(b.idzaposlenog, @godina) and a.idzaposlenog < b.idzaposlenog)
115 end
116
117 return @id
118end
119
120
121create procedure Unapredjenje(@godina int)
122as
123begin
124 declare @IDNajboljegRadnika int
125 declare @maxZaradaRadnika decimal(10,2)
126
127 select @maxZaradaRadnika=MAX(t.zarada)
128 from
129 (
130 select r.idzaposlenog as idBEST, SUM(r.vrednost) as zarada
131 from radnici r
132 where DATEPART(YEAR, r.datum) = @godina
133 group by r.idzaposlenog
134 ) t
135
136 select @IDNajboljegRadnika = t.idBEST
137 from
138 (
139 select r.idzaposlenog as idBEST
140 from radnici r
141 where DATEPART(YEAR, r.datum) = @godina
142 group by r.idzaposlenog
143 having SUM(r.vrednost) = @maxZaradaRadnika
144 ) t
145
146 select @IDNajboljegRadnika = MIN(tt.idBEST)
147 from
148 (
149 select a.zarada, COUNT(a.zarada) as saIstomZaradom
150 from dbo.konkurencija(@godina) as a left join dbo.konkurencija(@godina) as b on a.idBEST <> b.idBEST and a.zarada <= b.zarada
151 where b.zarada is null
152 group by a.zarada
153 ) t join dbo.konkurencija(@godina) tt on t.zarada = tt.zarada
154
155 declare @IDNajlosijegRukovodioca int
156 declare @minZaradaRukovodioca decimal(10,2)
157
158 select @minZaradaRukovodioca=MIN(t.zarada)
159 from
160 (
161 select idzaposlenog, SUM(vrednost) as zarada
162 from rukovodioci
163 where DATEPART(year, datum) = @godina
164 group by idzaposlenog
165 ) t
166
167
168 set @IDNajlosijegRukovodioca = dbo.najlosijiRukovodioc(@godina)
169
170
171 /* treba da se update tabela */
172
173end