USE master
GO

IF EXISTS (
    SELECT  name
    FROM    sys.DATABASES
    WHERE   name = N'GusarovVadim' )
ALTER DATABASE [GusarovVadim] SET single_user WITH ROLLBACK immediate
GO

IF EXISTS (
    SELECT  name
    FROM    sys.DATABASES
    WHERE   name = N'GusarovVadim' )
DROP DATABASE [GusarovVadim]
GO

CREATE DATABASE [GusarovVadim]
GO

USE [GusarovVadim]
GO

IF object_id('GusarovVadim.Tariffs', 'U') IS NOT NULL
    DROP TABLE  GusarovVadim.Tariffs
GO

CREATE TABLE Tariffs (
    name            VARCHAR(100),
    mounthCost      FLOAT,
    minutesCount    INT,
    overflowCost    FLOAT,
    CHECK (mounthCost >= 0 AND minutesCount >= 0 AND overflowCost >= 0 AND name != N'')
)
GO

INSERT INTO Tariffs(name, mounthCost, minutesCount, overflowCost) VALUES
    (N'Без абонентской платы',0, 0, 3),
    (N'Абонентский 50-минутный', 30, 50, 1),
    (N'Безлимитный',50, 0, 0)
GO

IF object_id( 'GusarovVadim.GetCost', 'F' ) IS NOT NULL
    DROP FUNCTION GusarovVadim.GetCost
GO

CREATE FUNCTION GetCost(@nameOfTariff VARCHAR(100), @COUNT FLOAT)
RETURNS FLOAT AS
BEGIN
    DECLARE @overflow FLOAT; DECLARE @tariffCost FLOAT; DECLARE @minuteCost FLOAT;
    SELECT @overflow = minutesCount FROM Tariffs WHERE @nameOfTariff=name
    SELECT @tariffCost = mounthCost FROM Tariffs WHERE @nameOfTariff=name
    SELECT @minuteCost = overflowCost FROM Tariffs WHERE @nameOfTariff=name
    IF (@COUNT < @overflow) RETURN @tariffCost
    RETURN @tariffCost + (@COUNT - @overflow) * @minuteCost
END
GO

IF object_id( 'GusarovVadim.Median', 'F' ) IS NOT NULL
    DROP FUNCTION GusarovVadim.Median
GO

CREATE FUNCTION Median(@name VARCHAR(100), @FIRST FLOAT, @SECOND FLOAT)
RETURNS FLOAT AS
BEGIN RETURN (dbo.GetCost(@name, @FIRST) + dbo.GetCost(@name, @SECOND)) / 2 END
GO

IF object_id( 'GusarovVadim.GetTariff', 'F' ) IS NOT NULL
    DROP PROCEDURE GusarovVadim.GetTariff
GO

CREATE PROCEDURE GetTariff(@minutes FLOAT)
AS
    DECLARE @nameOfTariff VARCHAR(100); DECLARE @MIN FLOAT;
    SELECT @MIN = MIN(dbo.Median(name, 0, @minutes)) FROM Tariffs
    SELECT @nameOfTariff = name FROM Tariffs WHERE @MIN = dbo.Median(name, 0, @minutes)
    print(@nameOfTariff)
GO

EXEC GetTariff 9

DECLARE @previous FLOAT = 0;
DECLARE @CURRENT FLOAT = 0;
DECLARE INTERVALСursor cursor FOR
SELECT DISTINCT round(b.minutesCount + a.mounthCost / b.overflowCost, 0)
FROM Tariffs a, Tariffs b
WHERE b.overflowCost != 0 AND round(b.minutesCount + a.mounthCost / b.overflowCost, 0) > 0
ORDER BY round(b.minutesCount + a.mounthCost / b.overflowCost, 0);

OPEN INTERVALСursor
fetch NEXT FROM INTERVALСursor
INTO @CURRENT

while @@fetch_status = 0
BEGIN
    DECLARE @MIN FLOAT = 0; DECLARE @minName VARCHAR(100);
    SELECT @MIN = MIN(dbo.Median(name, @previous, @CURRENT)) FROM Tariffs
    SELECT @minName = name FROM Tariffs WHERE @MIN = dbo.Median(name, @previous, @CURRENT)
    print(N'Интервал: '
            + CONVERT(VARCHAR(100), @previous)
            + ' -> '
            +  CONVERT(VARCHAR(100), @CURRENT)
            + ' - ' + @minName
    )
    SELECT @previous = @CURRENT;
    fetch NEXT FROM INTERVALСursor
    INTO @CURRENT
END

close INTERVALСursor;
deallocate INTERVALСursor;
GO