Untitled

mail@pastecode.io avatar
unknown
plain_text
22 days ago
14 kB
1
Indexable
Never

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

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


	--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 COUNT(*) FROM #dll --74887
	--SELECT COUNT(*) FROM #dll WHERE agent_inn IS not NULL --58639
	--SELECT COUNT(*) FROM #dll WHERE agent_inn IS NULL -- 16248

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

  --анализируемые инн, по которым прошли транзакции по топливным картам юридических лиц РФ у наших клиентов из [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 TOP 5 * FROM #reg

--ИТОГ-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    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
	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 * FROM #match_date
	SELECT COUNT(*) FROM #match_date --78586
	SELECT COUNT(*) FROM #match_date WHERE agent_inn IS not NULL --61563
	SELECT COUNT(*) FROM #match_date WHERE agent_inn IS NULL -- 17023
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	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

SELECT TOP 10 * from #reg WHERE #reg.KONTRAGENT_INN = '5007041598'
SELECT TOP 10 * FROM #dll WHERE #dll.client_inn = '5007041598'

-- Создаем таблицу с клиентами, которые есть и у дилеров, и в 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;

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

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

--SELECT client_inn_dil, agent_inn_dil,  client_name_dil, client_name_GZPN FROM #CommonClients
--GROUP BY client_inn_dil, agent_inn_dil,  client_name_dil, client_name_GZPN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
--ОКВЭДЫ
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
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.client_inn_dil, ccl.agent_inn_dil, ccl.client_name_dil, ccl.client_name_GZPN, okved.[ИНДЕКС]

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

SELECT count(*) FROM #KP_CommonClientsOkved WHERE #KP_CommonClientsOkved.agent_inn_dil IS NULL OR #KP_CommonClientsOkved.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 -- Шаг 3: Выбор 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 OR kpc.client_name_dil<>reg.KONTRAGENT_NAIMENOVANIE

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