------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, @wsp INT) RETURNS FLOAT(4)
BEGIN
    DECLARE @a INT, @b INT, @c INT, @d INT, @low FLOAT(4), @middle FLOAT(4), @high FLOAT(4), @return_value FLOAT(4)
    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
    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

SELECT e.id_employee, e.name, e.surname, e.earnings, dbo.fn_rozmyte(e.earnings, 1) AS low, dbo.fn_rozmyte(e.earnings, 2) AS middle, dbo.fn_rozmyte(e.earnings, 3) AS high
FROM hotel..employee e