Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ------3 zmniejsz place pracownikow pracujacych w danym dziale o okreslony procent w danym hotelu, wyswietl odpowiedni komunikat
- --IF EXISTS(SELECT * FROM sys.objects WHERE TYPE='P' AND name='rozmyte')
- --DROP PROCEDURE rozmyte
- --GO
- --CREATE PROCEDURE rozmyte @id_pracownika INT=NULL, @low FLOAT=NULL OUTPUT, @middle FLOAT=NULL OUTPUT, @high FLOAT=NULL OUTPUT
- --AS
- --BEGIN
- -- DECLARE @a INT, @b INT, @c INT, @d INT, @earning INT
- -- SET @earning = (SELECT e.earnings FROM hotel..employee e WHERE e.id_employee = @id_pracownika)
- -- SET @a = 1500
- -- SET @b = 3000
- -- SET @c = 4500
- -- SET @d = 6000
- -- BEGIN
- -- if (@earning <= @a)
- -- begin
- -- SET @low = 1.0
- -- set @middle = 0.0
- -- set @high = 0.0
- -- end
- -- if (@earning >= @b and @earning <= @c)
- -- begin
- -- SET @low = 0.0
- -- set @middle = 1.0
- -- set @high = 0.0
- -- end
- -- if (@earning >= @d)
- -- begin
- -- set @low = 0.0
- -- set @middle = 0.0
- -- set @high = 1.0
- -- end
- -- if (@earning > @a and @earning < @b)
- -- begin
- -- declare @first float, @second float
- -- set @first = 1.0 / (@a - @b)
- -- set @second = -@first * @b
- -- set @low = @first * @earning + @second
- -- set @middle = 1.0 - @low
- -- set @high = 0.0
- -- end
- -- if (@earning > @c and @earning < @d)
- -- begin
- -- set @first = 1.0 / (@c - @d)
- -- set @second = -@first * @d
- -- set @low = 0.0
- -- set @middle = @first * @earning + @second
- -- set @high = 1.0 - @middle
- -- end
- -- END
- --END
- --GO
- --BEGIN
- -- DECLARE @low float, @middle float, @high float
- -- EXEC rozmyte 45, @low output, @middle output, @high output
- -- PRINT 'k'
- -- print cast(@low as varchar(10)) + ' ' + cast(@middle as varchar(10)) + ' ' + cast(@high as varchar(10))
- --END
- --GO
- IF EXISTS(SELECT * FROM sys.objects WHERE type='FN' AND name='fn_rozmyte')
- DROP FUNCTION fn_rozmyte
- GO
- CREATE FUNCTION fn_rozmyte(@earning INT, @a INT, @b INT, @c INT, @d INT, @wsp INT) RETURNS FLOAT(4)
- BEGIN
- DECLARE @low FLOAT(4), @middle FLOAT(4), @high FLOAT(4), @return_value FLOAT(4)
- BEGIN
- if (@earning <= @a)
- begin
- SET @low = 1.0
- set @middle = 0.0
- set @high = 0.0
- end
- if (@earning >= @b and @earning <= @c)
- begin
- SET @low = 0.0
- set @middle = 1.0
- set @high = 0.0
- end
- if (@earning >= @d)
- begin
- set @low = 0.0
- set @middle = 0.0
- set @high = 1.0
- end
- if (@earning > @a and @earning < @b)
- begin
- declare @first float, @second float
- set @first = 1.0 / (@a - @b)
- set @second = -@first * @b
- set @low = @first * @earning + @second
- set @middle = 1.0 - @low
- set @high = 0.0
- end
- if (@earning > @c and @earning < @d)
- begin
- set @first = 1.0 / (@c - @d)
- set @second = -@first * @d
- set @low = 0.0
- set @middle = @first * @earning + @second
- set @high = 1.0 - @middle
- end
- END
- if (@wsp = 1)
- set @return_value = @low
- if (@wsp = 2)
- set @return_value = @middle
- if (@wsp = 3)
- set @return_value = @high
- RETURN @return_value
- END
- GO
- DECLARE @a INT, @b INT, @c INT, @d INT
- SET @a = 1500
- SET @b = 3000
- SET @c = 4500
- SET @d = 6000
- SELECT e.id_employee, e.name, e.surname, e.earnings, dbo.fn_rozmyte(e.earnings, @a, @b, @c, @d, 1) AS low, dbo.fn_rozmyte(e.earnings, @a, @b, @c, @d, 2) AS middle, dbo.fn_rozmyte(e.earnings, @a, @b, @c, @d, 3) AS high
- FROM hotel..employee e
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement