aaaa

USE KN302_PELEVINA
GO

-- Количество оперделенного товара за определенную дату
DECLARE @tovar VARCHAR(50);
SET @tovar = 'молоко';

DECLARE @DATE DATE;
SET @DATE = '2021-12-03';

DECLARE @postup INT;

SET @postup = (
    SELECT SUM(agr.kol_vo)
    FROM (
         SELECT t.kol_vo kol_vo
         FROM market_practice.tovar t
                  LEFT JOIN market_practice.tip_tovara tt
                            ON t.id_t = tt.id_t
         WHERE tt.t_name = @tovar
           AND t.priznak = 0
           AND t.post_prod <= @DATE
    ) agr
)

DECLARE @prod INT;

SET @prod = (
    SELECT SUM(agr.kol_vo)
    FROM (
         SELECT t.kol_vo kol_vo
         FROM market_practice.tovar t
                  LEFT JOIN market_practice.tip_tovara tt
                            ON t.id_t = tt.id_t
         WHERE tt.t_name = @tovar
           AND t.priznak = 1
           AND t.post_prod <= @DATE
    ) agr
)

DECLARE @RESULT INT;
SET @RESULT = @postup - @prod;

print(N'Товаров типа ' + CAST(@tovar AS NVARCHAR(30)) + N': ' + CAST(@RESULT AS nvarchar(30)))


-- Максимальная, минимальная и средняя цена за определенный товар
DECLARE @tovar VARCHAR(50);
SET @tovar = 'молоко';

SELECT
       CAST(MAX(t.price) AS DECIMAL(18,2)) "Максимальная цена",
       CAST(MIN(t.price) AS DECIMAL(18,2)) "Минимальная цена",
       CAST(avg(t.price) AS DECIMAL(18,2)) "Средняя цена"
FROM market_practice.tovar t
LEFT JOIN market_practice.tip_tovara tt
    ON t.id_t = tt.id_t
WHERE tt.t_name = @tovar
GO


-- Количество товаров в определенном магазине
DECLARE @market VARCHAR(20)
SET @market = 'Жизньмарт'

DECLARE @postup INT;
SET @postup = (
    SELECT SUM(agr.kol_vo)
    FROM (
         SELECT t.kol_vo kol_vo
         FROM market_practice.tovar t
         LEFT JOIN market_practice.market m
            ON t.id_m = m.id_m
         WHERE m.m_name = @market
           AND t.priznak = 0
    ) agr
)

DECLARE @prod INT;

SET @prod = (
    SELECT SUM(agr.kol_vo)
    FROM (
         SELECT t.kol_vo kol_vo
         FROM market_practice.tovar t
         LEFT JOIN market_practice.market m
            ON t.id_m = m.id_m
         WHERE m.m_name = @market
           AND t.priznak = 1
    ) agr
)

DECLARE @RESULT INT;
SET @RESULT = @postup - @prod;

print(N'Товаров в ' + CAST(@market AS nvarchar(20)) + N': ' + CAST(@RESULT AS nvarchar(20)))