Untitled

DROP TABLE IF EXISTS #dealer_MO_KP

SELECT *
INTO #dealer_MO_KP FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_MO_final]

UNION ALL

SELECT *
FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_KP_final];
-----------------------

DROP TABLE IF EXISTS #CommonInn

SELECT client_inn_dil
INTO #CommonInn
FROM #dealer_MO_KP
GROUP BY client_inn_dil
HAVING COUNT(DISTINCT label) > 1;
-----------------------
ALTER TABLE #dealer_MO_KP
ALTER COLUMN label nvarchar(255);
UPDATE #dealer_MO_KP
SET label = 'dil_MO+KP'
WHERE client_inn_dil IN (SELECT client_inn_dil FROM #CommonInn);
-----------------------

DROP TABLE IF EXISTS #dealer_MO_KP_modified
SELECT
    transaction_date,
    agent_inn_dil,
    client_inn_dil,
	client_name_dil,
    client_name_GZPN,
    total_monthly_litres_dil,
    CASE WHEN label = 'dil_MO' THEN total_monthly_ton_GZPN ELSE 0 END AS MO_dealer_monthly_ton_GZPN,
    CASE WHEN label = 'dil_KP' THEN total_monthly_ton_GZPN ELSE 0 END AS KP_dealer_monthly_ton_GZPN,
	CASE WHEN label = 'dil_MO+KP' THEN total_monthly_ton_GZPN ELSE 0 END AS MO_KP_monthly_ton_GZPN,
    okved_client,
    label
INTO #dealer_MO_KP_modified
FROM #dealer_MO_KP;

--SELECT count(*) FROM #dealer_MO_KP_modified --3063
--SELECT count(*) FROM #dealer_MO_KP_modified WHERE label = 'dil_KP' --2603
--SELECT count(*) FROM #dealer_MO_KP_modified WHERE label = 'dil_MO' --276
--SELECT count(*) FROM #dealer_MO_KP_modified WHERE label = 'dil_MO+KP' --184


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Добавим кластер Прочие ( не попали ни в MO, ни в КП)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

----Конечные клиенты дилеров---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--Ранжирование конечных клиентов дилеров
	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 -- справочник клиентов
	
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----Транзакции конечных клиентов дилеров	(с фильтром на последнюю продажу)  

DROP TABLE IF EXISTS #vtt;

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, -- Дата первого дня месяца транзакции
    SUM(cast(vt.litres AS decimal(10,2))) AS monthly_litres, -- Суммарное количество литров за месяц для каждой пары клиент-агент
    MAX(vt.datetime_trn) AS MAX_DOC_date -- Максимальная дата транзакции для каждой пары клиент-контракт
INTO #vtt
FROM [DATAMARTS].[dpa].vw_transactions vt
GROUP BY 
	vt.client_inn,
    vt.agent_inn,
    vt.contract_name, 
    vt.region,
	YEAR(vt.datetime_trn),
	MONTH(vt.datetime_trn);


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	DROP TABLE IF EXISTS #vttr

	SELECT 
		t.client_inn, 
		t.agent_inn,
		t.contract_name, 
		t.region, 
		t.transaction_date,
		SUM(t.monthly_litres) AS total_monthly_litres,
		cast(t.MAX_DOC_date AS date) AS MAX_DOC_date
	INTO #vttr
	FROM #vtt 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_litres
	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_litres;