Untitled

avatar
unknown
plain_text
2 years ago
16 kB
7
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 -- справочник клиентов
	
	
	--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(cast(vt.litres AS decimal(10,2))) 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
------------------------------------------------


 --   DROP TABLE IF EXISTS #dll_2
	--SELECT 
	--	agent_inn, 
	--	agent_name, 
	--	client_inn, 
	--	client_name, 
	--	region,
	--	contract_name,
	--	SUM(total_monthly_litres) AS total_litres,
	--	MIN(MIN_DOC_date) AS MIN_DOC_date, 
	--	MAX(MAX_DOC_date) AS MAX_DOC_date
	--INTO #dll_2
	--FROM #dll
	--GROUP BY 
	--	agent_inn, 
	--	agent_name, 
	--	client_inn, 
	--	client_name, 
	--	region,
	--	contract_name;

--SELECT TOP 10 * FROM #dll_2
--SELECT count(*) FROM #dll
--SELECT count(*) FROM #dll_2

--SELECT count(DISTINCT client_inn) FROM #dll
--SELECT count(DISTINCT client_inn) FROM #dll_2

--SELECT count(DISTINCT agent_inn) FROM #dll
--SELECT count(DISTINCT agent_inn) FROM #dll_2

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

	 SELECT TOP 10 * FROM #wwe
	 SELECT count(*) FROM #wwe --659547
	 SELECT * FROM #wwe WHERE KONTRAGENT_INN='5074068130'

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	 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,
		cast(w.TOTAL_LITRES AS decimal(19,2)) AS 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 --регион объекта управления

	--SELECT TOP 10 * FROM [asku_kp_cdd].[cdd].[organization_department]
	--SELECT TOP 10 * FROM [asku_rp_kk_cdd].[cdd].[counterparty_contract]
	
	SELECT count(*) FROM #reg --659547
	SELECT count(DISTINCT KONTRAGENT_INN) FROM #reg --65256


	SELECT TOP 5 * FROM #reg WHERE #reg.KONTRAGENT_INN='2222874370'
-------------------------------------------------------------------------------------------------------------------------------------------------
	-- Создаем таблицу с клиентами, которые есть и у дилеров, и в 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 

SELECT TOP 10 * FROM #CommonClients_KP
---- Всего строк (9574 клиентов)
SELECT count(*) FROM #CommonClients_KP
---- Подсчет количества уникальных агентов (33 агента)
SELECT COUNT(DISTINCT agent_inn_dil) FROM #CommonClients_KP;
---- Подсчет количества уникальных клиентов (467 клиентов)
SELECT COUNT(DISTINCT client_inn_dil) FROM #CommonClients_KP;

--SELECT min(transaction_date) FROM #dll --2017-05-01
--SELECT min(DATA_OPERACII) FROM #reg --2023-01-01

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

DROP TABLE IF EXISTS #KP_CommonClientsOkved_KP; -- подтягиваем оквэды к таблице с клиентами, которые есть и у нас, и у дилеров
SELECT 
	ccl.transaction_date,
	ccl.agent_inn_dil, 
	ccl.client_inn_dil, 
	ccl.client_name_dil,
	ccl.monthly_litres_dil,
	ccl.client_name_GZPN,
	ccl.monthly_litres_GZPN,
	okved.[ИНДЕКС]

INTO #KP_CommonClientsOkved_KP
FROM #CommonClients_KP 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.[ИНДЕКС],
	transaction_date,
	monthly_litres_dil,
	monthly_litres_GZPN


SELECT count(client_inn_dil) FROM #KP_CommonClientsOkved_KP --9498 строк

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

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

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

-- сперва 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_KP
UPDATE kpc
SET kpc.agent_inn_dil = atu.agent_inn
FROM #KP_CommonClientsOkved_KP kpc
JOIN AgentsToUpdate atu ON kpc.client_inn_dil = atu.client_inn
WHERE kpc.agent_inn_dil IS NULL;
--(2399 rows affected)

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

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

UPDATE kpc
SET kpc.client_name_dil = reg.KONTRAGENT_NAIMENOVANIE
FROM #KP_CommonClientsOkved_KP kpc
INNER JOIN #reg reg ON kpc.client_inn_dil = reg.KONTRAGENT_INN
WHERE kpc.client_name_dil IS NULL
--(3407 rows affected)

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

-- добавим информацию по ОКВЭДАМ из [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED]

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

 --хочу заменить пропуски в ИНДЕКС на значения из NAIMENOVANIE_OKVED для соответствующих инн-----------------------------------------------------
UPDATE kpc
SET kpc.ИНДЕКС = go.NAIMENOVANIE_OKVED
FROM #KP_CommonClientsOkved_KP 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;
--(2033 rows affected)

-- хочу заменить ИНДЕКС, который был добавленен из [Med_Okved] на описание из [GEN_OKVED], так как оно кажется более полным
UPDATE kpc
SET kpc.ИНДЕКС = go.NAIMENOVANIE_OKVED
FROM #KP_CommonClientsOkved_KP 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
--(6961 rows affected)
------------------------------------------
SELECT * FROM #KP_CommonClientsOkved_KP
GROUP BY #KP_CommonClientsOkved_KP.transaction_date

-- валидация на ложную подстановку--
SELECT * 
FROM #KP_CommonClientsOkved_KP
WHERE client_name_dil != lower(client_name_GZPN)
--0 несовпадений--

DROP TABLE IF EXISTS #final_KP

SELECT
	transaction_date,
	agent_inn_dil,
	client_inn_dil,
	client_name_dil,
	SUM(monthly_litres_dil) AS total_monthly_litres_dil,
	SUM(monthly_litres_GZPN) AS total_monthly_litres_GZPN,
	ИНДЕКС
FROM #KP_CommonClientsOkved_KP
GROUP BY transaction_date, agent_inn_dil, client_inn_dil, client_name_dil, ИНДЕКС

SELECT * FROM #final_KP
Editor is loading...
Leave a Comment