Untitled
unknown
plain_text
a year ago
14 kB
3
Indexable
----Конечные клиенты дилеров---------------------------------------------------------------------------------------------------------------------------------------
--Ранжирование конечных клиентов дилеров
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
Editor is loading...
Leave a Comment