Untitled

avatar
unknown
plain_text
a year ago
32 kB
2
Indexable
Wholesales

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

--Ранжирование конечных клиентов дилеров
	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_client
INTO #KP_CommonClientsOkved
FROM #CommonClients ccl
LEFT JOIN [SANDBOX_DB_TEAM_21].[dbo].[Med_Okved] okved 
	ON 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 count(*) FROM #KP_CommonClientsOkved

SELECT * FROM #KP_CommonClientsOkved
SELECT count(*) FROM #KP_CommonClientsOkved WHERE agent_inn_dil IS NULL -- 27 пропусков в данных
SELECT count(*) FROM #KP_CommonClientsOkved WHERE client_name_dil IS NULL  -- 27 пропусков в данных

-------------------- попробуем восстановить---------------------------------------

-- сперва agent_inn_dil--

-- Шаг 1: Определение уникальных пар client_inn и agent_inn для каждого клиента уникальные агенты
WITH UniqueAgentPairs AS (
    SELECT 
        client_inn, 
        agent_inn
    FROM #vttr
    GROUP BY client_inn, agent_inn
),
-- Шаг 2: Подсчет количества уникальных agent_inn для каждого client_inn
UniqueAgentCount AS (
    SELECT 
        client_inn,
        COUNT(agent_inn) AS UniqueAgentCount
    FROM UniqueAgentPairs
    GROUP BY client_inn
    HAVING COUNT(agent_inn) = 1 -- Выбор client_inn с одним уникальным agent_inn - уникальное соотвествие 
),
-- Шаг 3: Подготовка к обновлению, выборка нужных client_inn и соответствующих им agent_inn
AgentsToUpdate AS (
    SELECT 
        uac.client_inn,
        uap.agent_inn
    FROM UniqueAgentCount uac
    JOIN UniqueAgentPairs uap ON uac.client_inn = uap.client_inn
)
-- Обновление #KP_CommonClientsOkved
UPDATE kpc
SET kpc.agent_inn_dil = atu.agent_inn
FROM #KP_CommonClientsOkved kpc
JOIN AgentsToUpdate atu ON kpc.client_inn_dil = atu.client_inn
WHERE kpc.agent_inn_dil IS NULL;


-- проверка 
SELECT * FROM #KP_CommonClientsOkved WHERE #KP_CommonClientsOkved.client_inn_dil = '0276906995'
SELECT * FROM #vttr WHERE #vttr.client_inn = '0276906995'
-- вроде ок

-- теперь client_name_dil--тут чуть проще, так как связь однозначная

UPDATE kpc
SET kpc.client_name_dil = reg.client_name
FROM #KP_CommonClientsOkved kpc
INNER JOIN #reg reg ON kpc.client_inn_dil = reg.client_inn
WHERE kpc.client_name_dil IS NULL


-- проверка 
SELECT * FROM #KP_CommonClientsOkved WHERE #KP_CommonClientsOkved.client_inn_dil = '0276906995'
SELECT * FROM #reg WHERE #reg.client_inn = '0276906995'
-- вроде ок


SELECT count(*) FROM #KP_CommonClientsOkved WHERE okved_client IS NULL --24 пустых оквэда

-- добавим информацию по ОКВЭДАМ из [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED]
 --хочу заменить пропуски в ИНДЕКС на значения из NAIMENOVANIE_OKVED для соответствующих инн-----------------------------------------------------
UPDATE kpc
SET kpc.okved_client = go.NAIMENOVANIE_OKVED
FROM #KP_CommonClientsOkved AS kpc
INNER JOIN [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED] AS go
    ON kpc.client_inn_dil = go.INN
WHERE go.VID_OKVED = 'Основной' 
  AND kpc.okved_client IS NULL;

SELECT * FROM #KP_CommonClientsOkved

--проверка сколько совпадающих инн есть между таблицами
--SELECT count(*) AS matching_inn
--FROM #KP_CommonClientsOkved AS kpc
--INNER JOIN [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED] AS go
--ON kpc.client_inn_dil = go.INN;

-- хояу заменить ИНДЕКС, который был добавленен из [Med_Okved] на описание из [GEN_OKVED], так как оно кажется более полным
UPDATE kpc
SET kpc.okved_client = go.NAIMENOVANIE_OKVED
FROM #KP_CommonClientsOkved AS kpc
INNER JOIN [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED] AS go
    ON kpc.client_inn_dil = go.INN
WHERE kpc.okved_client IS NULL OR kpc.okved_client <> go.NAIMENOVANIE_OKVED; -- обновляем только если ИНДЕКС пустой или отличается от NAIMENOVANIE_OKVED
------------------------------------------
SELECT * FROM #KP_CommonClientsOkved

	DROP TABLE IF EXISTS [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_MO]
	SELECT * 
	INTO [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_MO]
	FROM #KP_CommonClientsOkved



FullKP

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

--Ранжирование конечных клиентов дилеров
	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

	--SELECT TOP 5 * FROM [DATAMARTS].[dpa].vw_transactions
	--SELECT TOP 5 * FROM #vtt


	--SELECT count(*) FROM [DATAMARTS].[dpa].vw_transactions --17139622
	--SELECT count(*) FROM [DATAMARTS].[dpa].vw_transactions WHERE region IS NULL --1069432
	--SELECT count(*) FROM [DATAMARTS].[dpa].vw_transactions WHERE region IS not NULL --16070190
	
	--SELECT count(*) FROM #vtt --17011925
	--SELECT count(*) FROM #vtt WHERE region IS NULL --1034482
	--SELECT count(*) FROM #vtt WHERE region IS not NULL --15977443



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

	
	---- без t.agent_inn
	--SELECT COUNT(*) FROM #vttr --74678
	--SELECT count(*) FROM #vttr WHERE region IS NULL --4237
	--SELECT count(*) FROM #vttr WHERE region IS not NULL --70441

	---- с t.agent_inn
	--SELECT COUNT(*) FROM #vttr --75251
	--SELECT count(*) FROM #vttr WHERE region IS NULL --4324
	--SELECT count(*) FROM #vttr WHERE region IS not NULL --70927

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

    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

	----SELECT * FROM #dll WHERE #dll.contract_name IS NULL 

	---- #vttr check
	--SELECT TOP 5 * FROM #vttr

	---- #cll check
	--SELECT TOP 5 * FROM #cll

	---- #dll check
	--SELECT * FROM #dll

	--SELECT TOP 10 * FROM #dll --74887
	--SELECT COUNT(*) FROM #dll WHERE agent_inn IS not NULL --58639
	--SELECT COUNT(*) FROM #dll WHERE agent_inn IS NULL -- 16248

	--SELECT * FROM #dll WHERE agent_inn IS NULL AND client_inn='524900669020'
	--SELECT * FROM #vttr WHERE client_inn='524900669020'
	--SELECT * FROM #cll WHERE client_inn='524900669020'
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------	   
----мэппинг АЗС для КК

--	DROP TABLE IF EXISTS #coord 
--    SELECT o.c_code AS КОД_АСКУ, o.c_comment AS КОММЕНТАРИЙ_АСКУ, 
--	o.c_description AS ОПИСАНИЕ_АСКУ, ou.standart_code КОД_СТАНДАРТ, 
--	ou.standart_description AS ОПИСАНИЕ_СТАНДАРТ, em.COORD_EAST, em.COORD_NORTH
--	INTO #coord
--	FROM [asku_rp_kk_cdd].[cdd].[operating_unit] o
--	LEFT JOIN [DATAMARTS].[DICT].[vw_operation_unit] ou
--	ON o.id_rref=ou.ou_ku_id_asku_rp_kk
--	LEFT JOIN [DATAMARTS].[EMIS].[VW_AZSS_GPN] em
--	ON ou.standart_code_emis=em.GPNAZS_ID
--	GROUP BY o.c_code, o.c_comment, o.c_description, ou.standart_code, 
--	ou.standart_description, em.COORD_EAST, em.COORD_NORTH
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 --анализируемые инн, по которым прошли транзакции по топливным картам юридических лиц РФ у наших клиентов из [VW_REALIZACIYA_FULL_KP] за 2023 год 
	 DROP TABLE IF EXISTS #wee;
	 SELECT 
		s.KONTRAGENT_INN, 
		s.KONTRAGENT_NAIMENOVANIE, 
		s.DOGOVORKONTRAGENTA_DATA_DOGOVORA, 
		s.DOGOVORKONTRAGENTA_NOMER_DOGOVORA, 
		s.DOGOVORKONTRAGENTA_ID_ASKU, 
		s.DATA_OPERACII,
		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
	 --AND NOMENKLATURNAYA_GRUPPA_KRATKO='Нефтепродукты' 
	 --AND DOGOVORKONTRAGENTA_KLASSIFIKACIYA_KLIENTA='Дилеры'
	 GROUP BY KONTRAGENT_INN, KONTRAGENT_NAIMENOVANIE, s.DOGOVORKONTRAGENTA_DATA_DOGOVORA, s.DOGOVORKONTRAGENTA_NOMER_DOGOVORA, s.DOGOVORKONTRAGENTA_ID_ASKU, s.DATA_OPERACII

	 --SELECT TOP 10 * FROM [DATAMARTS].[KA].[VW_REALIZACIYA_FULL_KP] WHERE DOGOVORKONTRAGENTA_GOSKONTRAKT!=1
	 --SELECT TOP 10 * FROM #wee

	 --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, 
		CAST(a.MAX_DOC_DATE_KK AS date) AS MAX_DOC_DATE_KK
	 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.MAX_DOC_DATE_KK 

	 --SELECT TOP 10 * FROM [DATAMARTS].[KA].[VW_REALIZACIYA_FULL_KP]
	 --WHERE YEAR(DATA_OPERACII)='2023' AND KONTRAGENT_INN='5506039040'

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

--ИТОГ-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    DROP TABLE IF EXISTS #match_date
	SELECT l.agent_inn, l.agent_name, l.client_inn, l.client_name, l.region, l.contract_name, l.MIN_DOC_date, l.MAX_DOC_date, 
	e.OTDELENIE, e.DOGOVORKONTRAGENTA_NOMER_DOGOVORA, e.DOGOVORKONTRAGENTA_DATA_DOGOVORA, MAX_DOC_DATE_KK
	INTO #match_date
	FROM #dll l -- Продаж конечных клиентов и справочника договоров
	LEFT JOIN #reg e --существующие клиенты наши
	ON l.client_inn=e.KONTRAGENT_INN AND l.client_name=e.KONTRAGENT_NAIMENOVANIE --upd: добавил новое соответствие
	GROUP BY l.agent_inn, l.agent_name, l.client_inn, l.client_name, l.region, l.contract_name, l.MIN_DOC_date, l.MAX_DOC_date, 
	e.OTDELENIE, e.DOGOVORKONTRAGENTA_NOMER_DOGOVORA, e.DOGOVORKONTRAGENTA_DATA_DOGOVORA, MAX_DOC_DATE_KK

	--SELECT TOP 5 * FROM #match_date WHERE agent_inn IS NOT NULL 
	--SELECT TOP 5 * FROM #dll WHERE agent_inn IS NOT NULL 
	--SELECT TOP 5 * FROM #reg 

	--SELECT COUNT(DISTINCT agent_inn) FROM #match_date --75047
	--SELECT COUNT(*) FROM #match_date WHERE agent_inn IS not NULL --58799
	--SELECT COUNT(*) FROM #match_date WHERE agent_inn IS NULL -- 16248
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	DROP TABLE IF EXISTS [SANDBOX_DB_TEAM_21].[dbo].[GEN_dealer]
	SELECT * 
	INTO [SANDBOX_DB_TEAM_21].[dbo].[GEN_dealer]
	FROM #match_date
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

		SELECT distinct d.agent_inn, 
		--d.agent_name, ag.NAIMENOVANIE_OKVED AS agent_okved,
		d.client_inn
		--, 
		--d.client_name, cl.NAIMENOVANIE_OKVED AS client_okved, 
		--d.region, d.contract_name, d.MIN_DOC_date, d.MAX_DOC_date, d.OTDELENIE, 
		--d.DOGOVORKONTRAGENTA_NOMER_DOGOVORA, d.DOGOVORKONTRAGENTA_DATA_DOGOVORA,
		--d.MAX_DOC_DATE_KK 
		FROM [SANDBOX_DB_TEAM_21].[dbo].[GEN_dealer] d
		LEFT JOIN [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED] ag
		ON d.agent_inn=ag.INN
		LEFT JOIN [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED] cl
		ON d.client_inn=cl.INN
		WHERE ag.NAIMENOVANIE_OKVED IS NULL 
		OR cl.NAIMENOVANIE_OKVED IS NULL 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

-- Создаем таблицу с клиентами, которые есть и у дилеров, и в VW_REALIZACIYA_FULL_KP за 2023 год
DROP TABLE IF EXISTS #CommonClients;
SELECT 
    dl.agent_inn AS agent_inn_dil,
    dl.client_inn AS client_inn_dil,
	dl.client_name AS client_name_dil,
	r.KONTRAGENT_NAIMENOVANIE AS client_name_GZPN
INTO #CommonClients
	FROM #dll dl
INNER JOIN #reg r 
	ON dl.client_inn = r.KONTRAGENT_INN --8455 или 417 если -> AND dl.client_name=r.KONTRAGENT_NAIMENOVANIE


--SELECT TOP 5 * FROM #reg
--SELECT TOP 5 * FROM #dll WHERE client_inn is not NULL AND agent_inn is not NULL 

--SELECT * FROM #CommonClients
---- Всего клиентов (8455 клиентов)
--SELECT count(*) FROM #CommonClients
---- Подсчет количества уникальных клиентов (906 клиентов)
--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_name_GZPN, okved.[ИНДЕКС]
INTO #KP_CommonClientsOkved
FROM #CommonClients ccl
LEFT JOIN [SANDBOX_DB_TEAM_21].[dbo].[Med_Okved] okved 
	ON ccl.client_inn_dil=okved.[ИНН]
GROUP BY 
	ccl.agent_inn_dil,
	ccl.client_inn_dil, 
	ccl.client_name_dil, 
	ccl.client_name_GZPN, 
	okved.[ИНДЕКС]

-- посмотрим на получившуюся таблицу, видим много пропусков
--SELECT * FROM #CommonClients
--GROUP BY agent_inn_dil, client_inn_dil, client_name_dil, client_name_GZPN

--SELECT count(client_inn_dil) FROM #KP_CommonClientsOkved --1083 строк

--SELECT count(DISTINCT client_inn_dil) FROM #KP_CommonClientsOkved -- 906 уникальных инн

--SELECT count(*) FROM #KP_CommonClientsOkved WHERE agent_inn_dil IS NULL OR client_name_dil IS NULL -- 297 пропусков в данных

-------------------- попробуем восстановить--------------------

-- сперва agent_inn--

-- Шаг 1: Определение уникальных пар client_inn и agent_inn для каждого клиента уникальные агенты
WITH UniqueAgentPairs AS (
    SELECT 
        client_inn, 
        agent_inn
    FROM #vttr
    GROUP BY client_inn, agent_inn
),
-- Шаг 2: Подсчет количества уникальных agent_inn для каждого client_inn
UniqueAgentCount AS (
    SELECT 
        client_inn,
        COUNT(agent_inn) AS UniqueAgentCount
    FROM UniqueAgentPairs
    GROUP BY client_inn
    HAVING COUNT(agent_inn) = 1 -- Выбор client_inn с одним уникальным agent_inn - уникальное соотвествие 
),
-- Шаг 3: Подготовка к обновлению, выборка нужных client_inn и соответствующих им agent_inn
AgentsToUpdate AS (
    SELECT 
        uac.client_inn,
        uap.agent_inn
    FROM UniqueAgentCount uac
    JOIN UniqueAgentPairs uap ON uac.client_inn = uap.client_inn
)
-- Обновление #KP_CommonClientsOkved
UPDATE kpc
SET kpc.agent_inn_dil = atu.agent_inn
FROM #KP_CommonClientsOkved kpc
JOIN AgentsToUpdate atu ON kpc.client_inn_dil = atu.client_inn
WHERE kpc.agent_inn_dil IS NULL;


-- проверка 
--SELECT * FROM #KP_CommonClientsOkved WHERE #KP_CommonClientsOkved.client_inn_dil = '0275918765'
--SELECT * FROM #vttr WHERE #vttr.client_inn = '0275918765'
-- вроде ок

-- теперь client_name_dil--тут чуть проще, так как связь однозначная

UPDATE kpc
SET kpc.client_name_dil = reg.KONTRAGENT_NAIMENOVANIE
FROM #KP_CommonClientsOkved kpc
INNER JOIN #reg reg ON kpc.client_inn_dil = reg.KONTRAGENT_INN
WHERE kpc.client_name_dil IS NULL

-- проверка 
--SELECT * FROM #KP_CommonClientsOkved WHERE #KP_CommonClientsOkved.client_inn_dil = '0275918765'
--SELECT * FROM #reg WHERE #reg.KONTRAGENT_INN = '0275918765'
-- вроде ок

-- добавим информацию по ОКВЭДАМ из [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED]
 --хочу заменить пропуски в ИНДЕКС на значения из NAIMENOVANIE_OKVED для соответствующих инн-----------------------------------------------------
UPDATE kpc
SET kpc.ИНДЕКС = LEFT(go.NAIMENOVANIE_OKVED, 255)
FROM #KP_CommonClientsOkved AS kpc
INNER JOIN [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED] AS go
ON kpc.client_inn_dil = go.INN
WHERE go.VID_OKVED = 'Основной' and kpc.ИНДЕКС IS NULL;



--проверка сколько совпадающих инн есть между таблицами
SELECT count(*) AS matching_inn
FROM #KP_CommonClientsOkved AS kpc
INNER JOIN [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED] AS go
ON kpc.client_inn_dil = go.INN;

-- хочу заменить ИНДЕКС, который был добавленен из [Med_Okved] на описание из [GEN_OKVED], так как оно кажется более полным
UPDATE kpc
SET kpc.ИНДЕКС = go.NAIMENOVANIE_OKVED
FROM #KP_CommonClientsOkved AS kpc
INNER JOIN [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED] AS go
    ON kpc.client_inn_dil = go.INN
WHERE kpc.ИНДЕКС IS NULL OR kpc.ИНДЕКС <> go.NAIMENOVANIE_OKVED; -- обновляем только если ИНДЕКС пустой или отличается от NAIMENOVANIE_OKVED
------------------------------------------
SELECT * FROM #KP_CommonClientsOkved

	DROP TABLE IF EXISTS [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_FULLKP]
	SELECT * 
	INTO [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_FULLKP]
	FROM #KP_CommonClientsOkved


	SELECT TOP 10 * FROM #reg
Leave a Comment