Lab4

CREATE SCHEMA lab4
GO
drop table shop
drop function GetPrice
drop function GetPriceAndShop
drop function GetPoint
drop function GetSegments



create table shop
(
id int NOT NULL primary key IDENTITY(1,1),
name nvarchar(30) NOT NULL,
price int not null,
delivery int not null,
freeDelivery int
)
GO


insert into shop (name, price, delivery)
values (N'A', 280, 200)
insert into shop (name, price, delivery, freeDelivery)
values (N'B', 300, 270, 2000)

select * from shop
go

CREATE FUNCTION GetPrice(@ShopId INT, @Count float)
RETURNS INT
AS
BEGIN
    DECLARE @Cost INT;
    DECLARE @Delivery INT;
    DECLARE @freeDelivery INT;

    SELECT @Cost = price
    FROM shop
    WHERE id = @ShopId
    IF (@Cost IS NULL)
       SET @Cost = 0;
    SET @Cost = @Cost * @Count;

    SELECT @freeDelivery = freeDelivery
    FROM shop
    WHERE id = @ShopId

    SELECT @Delivery = delivery
    FROM shop
    WHERE id = @ShopId

    IF (@freeDelivery IS NULL)
        RETURN @Cost + @Delivery
    IF (@Cost > @freeDelivery)
        RETURN @Cost
    RETURN @Cost + @Delivery;
END;
GO

CREATE FUNCTION GetPriceAndShop(@Count float)
RETURNS @PriceAndShopInformation TABLE
(
    Наименование_магазина nvarchar(50) NULL,
    Стоимость float
)
AS
BEGIN
    DECLARE
        @MinCost float,
        @Наименование nvarchar(50)

    SELECT @MinCost = min(dbo.GetPrice(id, @Count))
    from shop

    SELECT @Наименование = name
    from shop
    where (@MinCost = dbo.GetPrice(id, @Count))

    INSERT @PriceAndShopInformation
        SELECT @Наименование, @MinCost
    return;
END;
GO

select * from dbo.GetPriceAndShop(7);
go
CREATE FUNCTION GetPoint(@ShopId INT)
RETURNS float
AS
BEGIN
    DECLARE @Count float;
    DECLARE @Price float;
    DECLARE @freeDelivery float;

    SELECT @freeDelivery = freeDelivery
    FROM shop
    WHERE id = @ShopId

    SELECT @Price = price
    FROM shop
    WHERE id = @ShopId

    set @Count = @freeDelivery / @Price
    return @Count;
END;
GO

CREATE FUNCTION GetSegments()
RETURNS @Segments TABLE
(
    from_point float,
    to_point float,
    shop nvarchar(50)
)
AS
BEGIN
    DECLARE @Points CURSOR
    SET @Points = CURSOR SCROLL
    FOR
    select distinct dbo.GetPoint(id) as point
            from shop
            where shop.freeDelivery is not null
            order by point;

    declare @point float,
        @tmpPoint float,
        @prevPoint float;
    set @prevPoint = 0;
    Open @Points;
      FETCH @Points INTO @point;
    WHILE @@FETCH_STATUS = 0
    begin
        set @tmpPoint = @point - 0.1
        INSERT @Segments
            select @prevPoint, @point, (select Наименование_магазина from dbo.GetPriceAndShop(@tmpPoint))
        set @prevPoint = @point
        FETCH @Points INTO @point;
    END
    set @tmpPoint = @point + 0.1
    INSERT @Segments
        select @prevPoint, 1.79E+308, (select Наименование_магазина from dbo.GetPriceAndShop(@tmpPoint))
    return;
END;
GO


select * from dbo.GetSegments();