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 -- справочник клиентов


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Транзакции конечных клиентов дилеров	(с фильтром на последнюю продажу)  

	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 #vttr
	SELECT 
		t.client_inn, 
		t.agent_inn, --upd: t.agent_inn добавил
		t.contract_name, 
		t.region, 
		t.MAX_DOC_date
	INTO #vttr
	FROM #vtt t
	GROUP BY  t.client_inn, t.agent_inn, t.contract_name, t.region, t.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
	INTO #dll
	FROM #vttr vttr 
	LEFT JOIN #cll cll
	ON CONCAT(cll.client_inn,cll.contract_name)=CONCAT(vttr.client_inn,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

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 --анализируемые инн продаж по клиентам мелкого опта из [GPN_WHOLESALE_TRANSACTIONS] за 2023 год 
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--SELECT TOP 5 * FROM [DATAMARTS].[FINAL].[GPN_WHOLESALE_TRANSACTIONS] WHERE GOD = '2024'

--SELECT DISTINCT KANAL_PRODAZH_VYVERENNYJ FROM [DATAMARTS].[FINAL].[GPN_WHOLESALE_TRANSACTIONS] -- розница, мелкий опт
--SELECT DISTINCT TIP_NEFTEPRODUKTA FROM [DATAMARTS].[FINAL].[GPN_WHOLESALE_TRANSACTIONS] -- бензин, дт, прочее, суг


	DROP TABLE IF EXISTS #wh;
	SELECT 
		wt.INN AS client_inn,
		wt.NAIMENOVANIEKONTRAGENTAVYVERENNOE AS client_name,
		wt.SALES_CONTRACT_NUMBER AS contract_number,
		wt.DATA_REALIZACII AS date_operation,
		wt.DOKUMENT_REGISTRATOR AS doc_reg_number,
		MAX(wt.DATA_REALIZACII)OVER(PARTITION BY wt.INN, wt.SALES_CONTRACT_NUMBER) AS MAX_DOC_DATE_MO
	INTO #wh
	FROM [DATAMARTS].[FINAL].[GPN_WHOLESALE_TRANSACTIONS] wt
	WHERE wt.KANAL_PRODAZH_VYVERENNYJ = '2.Мелкий ОПТ' and GOD='2023' AND KOLICHESTVO_TN > 0
	GROUP BY INN, NAIMENOVANIEKONTRAGENTAVYVERENNOE, SALES_CONTRACT_NUMBER, DATA_REALIZACII, DOKUMENT_REGISTRATOR

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	
	DROP TABLE IF EXISTS #whg
	SELECT
		w.client_inn,
		w.client_name,
		w.contract_number,
		w.doc_reg_number,
		CAST(w.MAX_DOC_DATE_MO AS date) AS MAX_DOC_DATE_MO
	INTO #whg
	FROM #wh w
	 GROUP BY 
		w.client_inn, 
		w.client_name, 
		w.contract_number, 
		w.doc_reg_number, 
		w.MAX_DOC_DATE_MO 

--SELECT count(*) FROM #whg --270114
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--SELECT TOP 5 * FROM [DATAMARTS].[ASKU_RP].[vw_counterparty_contract_segment]
--SELECT TOP 5 * FROM [asku_kp_cdd].[cdd].[organization_department]  --регион объекта управления

--SELECT TOP 5 * FROM #whg
--SELECT TOP 5 * FROM [DATAMARTS].[FINAL].[GPN_WHOLESALE_TRANSACTIONS] WHERE GOD = '2024'

--SELECT count(*) AS matching_inn
--FROM [DATAMARTS].[ASKU_RP].[vw_counterparty_contract_segment] AS kpc
--INNER JOIN #whg AS go
--ON kpc.inn = go.client_inn;



	 DROP TABLE IF EXISTS #reg --существующие клиенты наши (пропусков в данных нет) из МО
	 SELECT 
		s.client_inn, --инн клиента
		LOWER(s.client_name) AS client_name, -- наименование клиента
		s.contract_number, -- номер контракта
		s.doc_reg_number, --номер документа регистрации
		cc.organization_department AS department, -- отеделение
		s.MAX_DOC_DATE_MO
	 INTO #reg
	 FROM #whg s
	 LEFT JOIN  [DATAMARTS].[ASKU_RP].[vw_counterparty_contract_segment] cc --контракт
	 ON s.contract_number = cc.contract_number AND s.client_inn = cc.inn 

--SELECT count(*) FROM #reg --270146
--SELECT top 10 * from #reg
--SELECT TOP 10 * FROM [DATAMARTS].[ASKU_RP].[vw_counterparty_contract_segment]

---------- проверка на NULL----------
--SELECT count(*) 
--FROM #reg
--WHERE client_inn IS NULL
--	OR client_name IS NULL
--	OR contract_number IS NULL --41 NULL
--	OR doc_reg_number IS NULL
--	OR department IS NULL
--	OR MAX_DOC_DATE_MO IS NULL
------------------------------------------------


-- валидация на ложную подстановку--
--SELECT count(*) AS mismatch_count
--FROM #reg
--WHERE client_inn != client_inn_2 AND client_name != counterparty_name;
--0 несовпадений--
------------------------------------------------------------------------------------------------

---- Создаем временную таблицу для клиентов из GPN_WHOLESALE_TRANSACTIONS за 2023 год
--SELECT TOP 10 * FROM #reg
----Дилеры и их клиенты находятся из dll
--SELECT TOP 10 * FROM #dll

-- Создаем таблицу с клиентами, которые есть и у дилеров, и в GPN_WHOLESALE_TRANSACTIONS за 2023 год
DROP TABLE IF EXISTS #CommonClients;
SELECT 
    dl.agent_inn AS agent_inn_dil,
    dl.client_inn AS client_inn_dil,
	LOWER(dl.client_name) AS client_name_dil,
	r.department AS department,
	r.client_inn AS client_inn_GZPN,
	LOWER(r.client_name) AS client_name_GZPN

INTO #CommonClients
	FROM #dll dl
INNER JOIN #reg r 
	ON dl.client_inn = r.client_inn --61453 или 767 если -> 
	--AND dl.client_name=r.client_name

SELECT count(*) FROM #CommonClients --61453
SELECT count(*) FROM #CommonClients WHERE agent_inn_dil IS NOT null --54102

-- валидация на ложную подстановку--
--SELECT count(*) AS mismatch_count
--FROM #CommonClients
--WHERE client_inn_dil != client_inn_GZPN AND client_name_dil != client_name_GZPN;
--0 несовпадений--

---------- проверка на NULL--------------
--SELECT count(*) -- 
--FROM #CommonClients
--WHERE agent_inn_dil IS NULL -- 7351
--	OR client_inn_dil IS NULL
--	OR client_name_dil IS NULL 
--	OR client_inn_GZPN IS NULL
--	OR client_name_GZPN IS NULL
--	OR department IS NULL
	--Общий скрипт 7351 NULL
------------------------------------------------
--ИТОГО--

-- Всего клиентов (61453 клиентов)
SELECT count(*) FROM #CommonClients
-- Подсчет количества уникальных клиентов (116 клиентов)
SELECT COUNT(DISTINCT client_inn_dil) FROM #CommonClients;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--ОКВЭДЫ
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--SELECT TOP 10 * FROM [SANDBOX_DB_TEAM_21].[dbo].[Med_Okved] -- таблица с оквэдами

DROP TABLE IF EXISTS #KP_CommonClientsOkved; -- подтягиваем оквэды к таблице с клиентами, которые есть и у нас, и у дилеров
SELECT 
	ccl.agent_inn_dil, 
	ccl.client_inn_dil, 
	ccl.client_name_dil, 
	ccl.client_inn_GZPN,
	ccl.client_name_GZPN, 
	ccl.department, 
	okved.[ИНДЕКС] AS okved
INTO #KP_CommonClientsOkved
FROM #CommonClients ccl
LEFT JOIN [SANDBOX_DB_TEAM_21].[dbo].[Med_Okved] okved 
	ON ccl.client_inn_dil=okved.[ИНН] OR ccl.client_inn_GZPN=okved.[ИНН]
GROUP BY 
	ccl.agent_inn_dil,
	ccl.client_inn_dil, 
	ccl.client_name_dil, 
	ccl.client_inn_GZPN, 
	ccl.client_name_GZPN,
	ccl.department,
	okved.[ИНДЕКС]

SELECT top 10 * FROM #KP_CommonClientsOkved