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 -- справочник клиентов
	
	
	--SELECT TOP 10 * FROM #cll
	--SELECT TOP 10 * FROM [DATAMARTS].[dpa].[vw_clients]
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----Транзакции конечных клиентов дилеров	(с фильтром на последнюю продажу)  

--	DROP TABLE IF EXISTS #vtt
--	SELECT 
--		vt.client_inn, 
--		vt.agent_inn, --UPD Добавил Антон
--		vt.contract_name, 
--		vt.region, 
--		CAST(MAX(vt.datetime_trn)OVER(PARTITION BY vt.client_inn,vt.contract_name) AS DATE) AS MAX_DOC_date -- для каждой пары client_inn и contract_name берем макс знач datetime_trn, приводится к date и назначается MAX_DOC_date
--	INTO #vtt
--	FROM [DATAMARTS].[dpa].vw_transactions vt -- продажи конечных клиентов 
--	GROUP BY vt.datetime_trn, vt.client_inn, vt.agent_inn, vt.contract_name, vt.region

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(vt.litres) 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);

	--SELECT count(*) FROM #vtt
	--SELECT TOP 10 * FROM [DATAMARTS].[dpa].vw_transactions

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

	SELECT 
		t.client_inn, 
		t.agent_inn, --upd: 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

	--SELECT count(*) FROM #vttr
	--SELECT TOP 10 * FROM #vttr
	--SELECT * FROM #vttr WHERE client_inn = '6671328672'

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

    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;

	--SELECT count(*) FROM #dll --552774
	--SELECT count(DISTINCT agent_inn) FROM #dll --46 агентов
	--SELECT count(DISTINCT client_inn) FROM #dll --16254 клиентов


---------- проверка на NULL----------
--SELECT count(*) --164234
--FROM #dll
--WHERE agent_inn IS NULL
--	OR agent_name IS NULL
--	OR client_name IS NULL 
--	OR contract_name IS NULL
--  OR MIN_DOC_date IS NULL
--	OR MAX_DOC_date IS NULL
--	OR region IS NULL
------------------------------------------------



 --анализируемые инн, по которым прошли транзакции по топливным картам юридических лиц РФ у наших клиентов из [VW_REALIZACIYA_FULL_KP] за 2023 год 
	 DROP TABLE IF EXISTS #wee;
	 SELECT 
		s.KONTRAGENT_INN, 
		s.KONTRAGENT_NAIMENOVANIE, 
		s.KOLICHESTVO_L,
		DATEFROMPARTS(YEAR(s.DATA_OPERACII), MONTH(s.DATA_OPERACII), 1) AS DATA_OPERACII,
		s.DOGOVORKONTRAGENTA_DATA_DOGOVORA, 
		s.DOGOVORKONTRAGENTA_NOMER_DOGOVORA, 
		s.DOGOVORKONTRAGENTA_ID_ASKU, 
		MAX(s.DATA_OPERACII)OVER(PARTITION BY KONTRAGENT_INN,DOGOVORKONTRAGENTA_NOMER_DOGOVORA) AS MAX_DOC_DATE_KK
	 INTO #wee
	 FROM [DATAMARTS].[KA].[VW_REALIZACIYA_FULL_KP] s
	 WHERE YEAR(DATA_OPERACII)='2023' -- DATA_OPERACII>='2023-07-01' and DATA_OPERACII<='2023-12-31'
	 AND KOLICHESTVO_L>0

	 
	 SELECT TOP 10 * FROM [DATAMARTS].[KA].[VW_REALIZACIYA_FULL_KP]

	 SELECT TOP 10 * FROM #wee
	 SELECT count(*) FROM #wee --71928922
	 SELECT * FROM #wee WHERE #wee.KONTRAGENT_INN='5074068130'
								

	 --SELECT DISTINCT NOMENKLATURNAYA_GRUPPA_KRATKO FROM [DATAMARTS].[KA].[VW_REALIZACIYA_FULL_KP]
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	
	DROP TABLE IF EXISTS #wwe
	 SELECT 
		a.KONTRAGENT_INN, 
		a.KONTRAGENT_NAIMENOVANIE,
		a.DOGOVORKONTRAGENTA_DATA_DOGOVORA, 
		a.DOGOVORKONTRAGENTA_NOMER_DOGOVORA, 
		a.DOGOVORKONTRAGENTA_ID_ASKU, 
		DATA_OPERACII,
		SUM(KOLICHESTVO_L) as TOTAL_LITRES
	 INTO #wwe
	 FROM #wee a
	 GROUP BY 
		a.KONTRAGENT_INN, 
		a.KONTRAGENT_NAIMENOVANIE, 
		a.DOGOVORKONTRAGENTA_DATA_DOGOVORA, 
		a.DOGOVORKONTRAGENTA_NOMER_DOGOVORA, 
		a.DOGOVORKONTRAGENTA_ID_ASKU, 
		a.DATA_OPERACII 


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	 DROP TABLE IF EXISTS #reg --существующие клиенты наши (пропусков в данных нет)
	 SELECT 
		w.KONTRAGENT_INN, 
		w.KONTRAGENT_NAIMENOVANIE, 
		w.DOGOVORKONTRAGENTA_DATA_DOGOVORA, 
		w.DOGOVORKONTRAGENTA_NOMER_DOGOVORA, 
		r.c_description AS OTDELENIE, 
		w.DATA_OPERACII,
		w.TOTAL_LITRES
	 INTO #reg
	 FROM #wwe w
	 LEFT JOIN  [asku_rp_kk_cdd].[cdd].[counterparty_contract] cc ON w.DOGOVORKONTRAGENTA_ID_ASKU = cc.id_rref --контракт
	 LEFT JOIN  [asku_kp_cdd].[cdd].[organization_department] r  ON cc.organization_department_rref=r.id_rref --регион объекта управления


-------------------------------------------------------------------------------------------------------------------------------------------------
	-- Создаем таблицу с клиентами, которые есть и у дилеров, и в VW_REALIZACIYA_FULL_KP за 2023 год
DROP TABLE IF EXISTS #CommonClients_KP;
SELECT 
    dl.agent_inn AS agent_inn_dil,
    dl.client_inn AS client_inn_dil,
	lower(dl.client_name) AS client_name_dil,
	dl.total_monthly_litres AS monthly_litres_dil,
	lower(r.KONTRAGENT_NAIMENOVANIE) AS client_name_GZPN,
	r.TOTAL_LITRES AS monthly_litres_GZPN,
	r.DATA_OPERACII AS transaction_date

INTO #CommonClients_KP
	FROM #dll dl
INNER JOIN #reg r 
	ON dl.client_inn = r.KONTRAGENT_INN AND dl.transaction_date=r.DATA_OPERACII