Untitled

	DROP TABLE IF EXISTS #cll
	SELECT DISTINCT 
		cl.agent_inn, 
		cl.agent_name, 
		cl.client_inn, 
		cl.client_name, 
		contract_date_begin, 
		contract_name
	INTO #cll
FROM [DATAMARTS].[dpa].[vw_clients] cl -- справочник клиентов
	
-------------------------------------------------------------------------------------------------------------------------------------------------------------------


	-- Определение плотности для разных типов топлива
WITH Density AS (
    SELECT
        'АИ-92' AS fuel, 0.760 AS density
	UNION ALL SELECT 'Аи-95', 0.750
    UNION ALL SELECT 'АИ-95', 0.750
	UNION ALL SELECT 'G-95', 0.750
    UNION ALL SELECT 'АИ-98', 0.780
	UNION ALL SELECT 'Аи-98', 0.750
	UNION ALL SELECT 'G-98', 0.750
    UNION ALL SELECT 'ДТ', 0.840
	UNION ALL SELECT 'Дт', 0.840
	UNION ALL SELECT 'Дизельное топливо', 0.840
    UNION ALL SELECT 'АИ-100', 0.750
	UNION ALL SELECT 'Аи-100', 0.750
    UNION ALL SELECT 'G-Drive', 0.750 
	UNION ALL SELECT 'G-100', 0.750
	UNION ALL SELECT 'бензин', 0.750
	UNION ALL SELECT 'Газ', 0.600

),

-- Отфильтрованные транзакции с расчетом тонн
	FilteredTransactions AS (
    SELECT
        vt.client_inn,
        vt.agent_inn,
        vt.contract_name,
        vt.region,
        DATEFROMPARTS(YEAR(vt.datetime_trn), MONTH(vt.datetime_trn), 1) AS transaction_date,
        vt.nomenclature,
        SUM(cast(vt.litres AS decimal(10,2))) AS monthly_litres,
        MAX(vt.datetime_trn) AS MAX_DOC_date
    FROM [DATAMARTS].[dpa].vw_transactions vt
    WHERE vt.nomenclature IN (
	'АИ-100 Плюс',
	'Аи-98',
	'Автомобильный бензин экологического класса К5 марки АИ-92-К5',
	'Топливо дизельное межсезонное',
	'ДТ ОПТИ',
	'АИ-95 ОПТИ',
	'Дизельное топливо (РФ)',
	'Бензин автомобильный АИ-95-К5',
	'G-ДТ',
	'Аи-95 Плюс',
	'Дизельное топливо экологического класса К5 (ДТ-З-К5)',
	'Бензин автомобильный АИ-92 (РФ)',
'Аи-95 Премиум',
	'ДТ Зимнее',
	'Дизельное топливо зимнее',
	'Газ',
	'ДТ Плюс',
	'Бензин автомобильный неэтилированный АИ-92',
	'Аи-92 Премиум',
	'АИ-100',
	'Топливо дизельное межсезонное экологического класса К5',
	'Дизельное топливо',
	'G-Drive 100',
	'Аи-92',
	'Бензин Премиум Евро-95 (АИ-95-К5) (ГПН)',
	'АИ-92 Плюс',
	'Бензин автомобильный АИ-92 экологического класса К5',
	'Аи-95',
	'Бензин автомобильный АИ-92-К5',
	'Бензин автомобильный АИ-95 экологического класса К5',
'СУГ',
	'Бензин автомобильный АИ-95 (РФ)',
	'Бензин автомобильный неэтилированный АИ-95',
	'G-98',
	'G-92',
	'ДТ Премиум',
	'Дизельное топливо ЕВРО, зимнее, экологического класса К5 (ДТ-З-К5)',
	'Бензин Регуляр-92 (АИ-92-К5) (ГПН)',
	'G-Drive 100 Москва',
	'Аи-98 Премиум',
	'ДТ З',
	'ДТ',
	'Жидкость AdBlue для системы SCR дизельных двигателей',
	'G-95',
	'АИ-92 ОПТИ'
    )
    GROUP BY 
        vt.client_inn,
        vt.agent_inn,
        vt.contract_name,
        vt.region,
        vt.nomenclature,
        YEAR(vt.datetime_trn),
        MONTH(vt.datetime_trn)
),

-- Присоединяем плотности и пересчитываем литры в тонны
TransactionsWithTons AS (
    SELECT
        ft.*,
        d.density,
        (ft.monthly_litres * d.density) / 1000 AS monthly_tons -- Расчет тонн
    FROM FilteredTransactions ft
    LEFT JOIN Density d ON ft.nomenclature LIKE '%' + d.fuel + '%'
)

	DROP TABLE IF EXISTS #vttr

	SELECT 
		t.client_inn, 
		t.agent_inn,
		t.contract_name, 
		t.region, 
		t.transaction_date,
		SUM(t.monthly_tons) AS total_monthly_tons,
		cast(t.MAX_DOC_date AS date) AS MAX_DOC_date
	INTO #vttr
	FROM TransactionsWithTons t
	GROUP BY 
		t.client_inn, 
		t.agent_inn, 
		t.contract_name, 
		t.region, 
		t.transaction_date,
		MAX_DOC_date

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Продаж конечных клиентов и справочника договоров

    DROP TABLE IF EXISTS #dll
	SELECT 
		cll.agent_inn, 
		cll.agent_name, 
		vttr.client_inn, 
		cll.client_name, 
		vttr.region,
		cll.contract_name, 
		CAST(cll.contract_date_begin AS DATE) AS MIN_DOC_date, 
		vttr.MAX_DOC_date,
		vttr.transaction_date,
		vttr.total_monthly_tons
	INTO #dll
	FROM #vttr vttr 
	LEFT JOIN #cll cll ON cll.client_inn = vttr.client_inn and lower(cll.contract_name)=lower(vttr.contract_name)
	GROUP BY 
		cll.agent_inn, 
		cll.agent_name, 
		vttr.client_inn, 
		cll.client_name, 
		vttr.region,
		cll.contract_name, 
		cll.contract_date_begin, 
		vttr.MAX_DOC_date,
		vttr.transaction_date,
		vttr.total_monthly_tons