sql_1_hw

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)))

SELECT @tovar 'Тип товара', @RESULT 'Количество', format(@DATE, 'dd MMMM yyyy') Дата


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

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_2
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)))

SELECT @market 'Магазин', @RESULT 'Количество'