Untitled

avatar
unknown
plain_text
2 years ago
32 kB
8
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 -- справочник клиентов
	
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

	-- Определение плотности для разных типов топлива
WITH Density AS (
    SELECT
        'АИ-92' AS fuel, 0.760 AS density
	UNION ALL SELECT 'Аи-95', 0.750
    UNION ALL SELECT 'АИ-95', 0.750
	UNION ALL SELECT 'G-95', 0.750
    UNION ALL SELECT 'АИ-98', 0.780
	UNION ALL SELECT 'Аи-98', 0.750
	UNION ALL SELECT 'G-98', 0.750
    UNION ALL SELECT 'ДТ', 0.840
	UNION ALL SELECT 'Дт', 0.840
	UNION ALL SELECT 'Дизельное топливо', 0.840
    UNION ALL SELECT 'АИ-100', 0.750
	UNION ALL SELECT 'Аи-100', 0.750
    UNION ALL SELECT 'G-Drive', 0.750 
	UNION ALL SELECT 'G-100', 0.750
	UNION ALL SELECT 'бензин', 0.750
	UNION ALL SELECT 'Газ', 0.600

),

-- Отфильтрованные транзакции с расчетом тонн
	FilteredTransactions AS (
    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,
        vt.nomenclature,
        SUM(cast(vt.litres AS decimal(10,2))) AS monthly_litres,
        MAX(vt.datetime_trn) AS MAX_DOC_date
    FROM [DATAMARTS].[dpa].vw_transactions vt
    WHERE vt.nomenclature IN (
	'АИ-100 Плюс',
	'Аи-98',
	'Автомобильный бензин экологического класса К5 марки АИ-92-К5',
	'Топливо дизельное межсезонное',
	'ДТ ОПТИ',
	'АИ-95 ОПТИ',
	'Дизельное топливо (РФ)',
	'Бензин автомобильный АИ-95-К5',
	'G-ДТ',
	'Аи-95 Плюс',
	'Дизельное топливо экологического класса К5 (ДТ-З-К5)',
	'Бензин автомобильный АИ-92 (РФ)',
'Аи-95 Премиум',
	'ДТ Зимнее',
	'Дизельное топливо зимнее',
	'Газ',
	'ДТ Плюс',
	'Бензин автомобильный неэтилированный АИ-92',
	'Аи-92 Премиум',
	'АИ-100',
	'Топливо дизельное межсезонное экологического класса К5',
	'Дизельное топливо',
	'G-Drive 100',
	'Аи-92',
	'Бензин Премиум Евро-95 (АИ-95-К5) (ГПН)',
	'АИ-92 Плюс',
	'Бензин автомобильный АИ-92 экологического класса К5',
	'Аи-95',
	'Бензин автомобильный АИ-92-К5',
	'Бензин автомобильный АИ-95 экологического класса К5',
'СУГ',
	'Бензин автомобильный АИ-95 (РФ)',
	'Бензин автомобильный неэтилированный АИ-95',
	'G-98',
	'G-92',
	'ДТ Премиум',
	'Дизельное топливо ЕВРО, зимнее, экологического класса К5 (ДТ-З-К5)',
	'Бензин Регуляр-92 (АИ-92-К5) (ГПН)',
	'G-Drive 100 Москва',
	'Аи-98 Премиум',
	'ДТ З',
	'ДТ',
	'Жидкость AdBlue для системы SCR дизельных двигателей',
	'G-95',
	'АИ-92 ОПТИ'
    )
    GROUP BY 
        vt.client_inn,
        vt.agent_inn,
        vt.contract_name,
        vt.region,
        vt.nomenclature,
        YEAR(vt.datetime_trn),
        MONTH(vt.datetime_trn)
),

-- Присоединяем плотности и пересчитываем литры в тонны
TransactionsWithTons AS (
    SELECT
        ft.*,
        d.density,
        (ft.monthly_litres * d.density) / 1000 AS monthly_tons -- Расчет тонн
    FROM FilteredTransactions ft
    LEFT JOIN Density d ON ft.nomenclature LIKE '%' + d.fuel + '%'
)


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	--DROP TABLE IF EXISTS #vttr

	SELECT 
		t.client_inn, 
		t.agent_inn,
		t.contract_name, 
		t.region, 
		t.transaction_date,
		SUM(t.monthly_tons) AS total_monthly_tons,
		MAX(t.MAX_DOC_date) AS MAX_DOC_date
	INTO #vttr
	FROM TransactionsWithTons t
	GROUP BY 
		t.client_inn, 
		t.agent_inn, 
		t.contract_name, 
		t.region, 
		t.transaction_date,
		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,
		vttr.transaction_date,
		vttr.total_monthly_tons
	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_tons

	--SELECT count(*) FROM #dll --998985
	--SELECT count(DISTINCT agent_inn) FROM #dll --51 агентов
	--SELECT count(DISTINCT client_inn) FROM #dll --16456 клиентов
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 --анализируемые инн продаж по клиентам мелкого опта из [GPN_WHOLESALE_TRANSACTIONS] за 2023 год 
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

	DROP TABLE IF EXISTS #wh;
	SELECT 
		wt.INN AS client_inn,
		wt.NAIMENOVANIEKONTRAGENTAVYVERENNOE AS client_name,
		wt.KOLICHESTVO_TN AS volume_ton,
		DATEFROMPARTS(YEAR(wt.DATA_REALIZACII), MONTH(wt.DATA_REALIZACII), 1) AS date_operation,
		wt.SALES_CONTRACT_NUMBER AS contract_number,
		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

	--SELECT TOP 10 * FROM [DATAMARTS].[FINAL].[GPN_WHOLESALE_TRANSACTIONS]
	--SELECT count(*) FROM #wh --2477827
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	
	DROP TABLE IF EXISTS #whg
	SELECT
		w.client_inn,
		w.client_name,
		w.contract_number,
		w.doc_reg_number,
		w.date_operation,
		SUM(w.volume_ton) AS total_volume_ton
	INTO #whg
	FROM #wh w
	 GROUP BY 
		w.client_inn, 
		w.client_name, 
		w.contract_number, 
		w.doc_reg_number, 
		w.date_operation 

	--SELECT * FROM #whg WHERE client_inn = '7734665315'
	--SELECT count(*) FROM #whg --270140

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

	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.date_operation,
		cast(s.total_volume_ton AS decimal(19,2)) AS total_volume_ton
	 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 --270172
	--SELECT count(DISTINCT client_inn) FROM #reg --3743

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

-- Создаем таблицу с клиентами, которые есть и у дилеров, и в GPN_WHOLESALE_TRANSACTIONS за 2023 год
DROP TABLE IF EXISTS #CommonClients_MO;
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_tons AS monthly_ton_dil,
	LOWER(r.client_name) AS client_name_GZPN,
	r.total_volume_ton AS monthly_ton_GZPN,
	r.date_operation AS transaction_date

INTO #CommonClients_MO
	FROM #dll dl
INNER JOIN #reg r 
	ON dl.client_inn = r.client_inn AND dl.transaction_date=r.date_operation 


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

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

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

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

INTO #Mo_CommonClientsOkved
FROM #CommonClients_MO 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.[ИНДЕКС],
	ccl.transaction_date, 
	ccl.monthly_ton_dil, 
	ccl.monthly_ton_GZPN

--SELECT count(client_inn_dil) FROM #Mo_CommonClientsOkved --26185 строк

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

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

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

-- сперва 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 #Mo_CommonClientsOkved kpc
JOIN AgentsToUpdate atu ON kpc.client_inn_dil = atu.client_inn
WHERE kpc.agent_inn_dil IS NULL;

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

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

UPDATE kpc
SET kpc.client_name_dil = reg.client_name
FROM #Mo_CommonClientsOkved kpc
INNER JOIN #reg reg ON kpc.client_inn_dil = reg.client_inn
WHERE kpc.client_name_dil IS NULL
-- проверка 
--SELECT * FROM #Mo_CommonClientsOkved WHERE #Mo_CommonClientsOkved.client_inn_dil = '027416532554'
--SELECT * FROM #reg WHERE #reg.client_inn = '027416532554'
-- вроде ок

--SELECT count(*) FROM #Mo_CommonClientsOkved WHERE okved_client IS NULL --1172

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

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


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

DROP TABLE IF EXISTS #final_MO

SELECT
	transaction_date,
	agent_inn_dil,
	client_inn_dil,
	client_name_dil,
	client_name_GZPN,
	SUM(ISNULL(monthly_ton_dil, 0)) AS total_monthly_ton_dil,
	SUM(ISNULL(monthly_ton_GZPN, 0)) AS total_monthly_ton_GZPN,
	okved_client,
	'dil_MO' AS label

INTO #final_MO
FROM #Mo_CommonClientsOkved
GROUP BY transaction_date, agent_inn_dil, client_inn_dil, client_name_dil, client_name_GZPN, okved_client;

SELECT * FROM #final_MO

SELECT count(*) FROM #final_MO --80 клиентов уникальных

	DROP TABLE IF EXISTS [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_MO_final]
	SELECT * 
	INTO [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_MO_final]
	FROM #final_MO

--SELECT * from [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_MO_final]

--------------------------------------------------------------------------------------------
--KP клиенты
--------------------------------------------------------------------------------------------
--анализируемые инн, по которым прошли транзакции по топливным картам юридических лиц РФ у наших клиентов из [VW_REALIZACIYA_FULL_KP] за 2023 год 
	 DROP TABLE IF EXISTS #wee;
	 SELECT 
		s.KONTRAGENT_INN, 
		s.KONTRAGENT_NAIMENOVANIE, 
		s.KOLICHESTVO,
		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
	 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	
	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) as TOTAL_VOLUME
	 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 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	 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_VOLUME AS decimal(19,2)) AS TOTAL_VOLUME
	 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 --регион объекта управления
-------------------------------------------------------------------------------------------------------------------------------------------------
	-- Создаем таблицу с клиентами, которые есть и у дилеров, и в 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_tons AS monthly_ton_dil,
	lower(r.KONTRAGENT_NAIMENOVANIE) AS client_name_GZPN,
	r.TOTAL_VOLUME AS monthly_ton_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
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--ОКВЭДЫ
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--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_ton_dil,
	ccl.client_name_GZPN,
	ccl.monthly_ton_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_ton_dil,
	monthly_ton_GZPN

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

-- сперва 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;
--(934 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
--(2286 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]
--хочу заменить пропуски в ИНДЕКС на значения из 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;
--(3184 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
----(6917 rows affected)
------------------------------------------

DROP TABLE IF EXISTS #final_KP

SELECT
	transaction_date,
	agent_inn_dil,
	client_inn_dil,
	client_name_dil,
	client_name_GZPN,
	SUM(ISNULL(monthly_ton_dil, 0)) AS total_monthly_ton_dil,
	SUM(ISNULL(monthly_ton_GZPN, 0)) AS total_monthly_ton_GZPN,
	ИНДЕКС AS okved_client,
	'dil_KP' AS label
INTO #final_KP
FROM #KP_CommonClientsOkved_KP
GROUP BY transaction_date, agent_inn_dil, client_inn_dil, client_name_dil, client_name_GZPN, ИНДЕКС;

--SELECT count(*) FROM #final_KP --2720
--SELECT count(*) FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_KP_final] --2706

--SELECT count(DISTINCT client_inn_dil) FROM #final_KP --465
--SELECT count(DISTINCT client_inn_dil) FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_KP_final] --467

--SELECT sum(total_monthly_ton_dil) AS sumaa FROM #final_KP --55242.086265
--SELECT sum(total_monthly_litres_dil) AS sumaa FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_KP_final] --37088039.58




	DROP TABLE IF EXISTS [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_KP_final]
	SELECT * 
	INTO [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_KP_final]
	FROM #final_KP


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




DROP TABLE IF EXISTS #dealer_MO_KP

SELECT *
INTO #dealer_MO_KP FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_MO_final]

UNION ALL

SELECT *
FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_KP_final];
-----------------------

DROP TABLE IF EXISTS #CommonInn

SELECT client_inn_dil
INTO #CommonInn
FROM #dealer_MO_KP
GROUP BY client_inn_dil
HAVING COUNT(DISTINCT label) > 1;
-----------------------
ALTER TABLE #dealer_MO_KP
ALTER COLUMN label nvarchar(255);
UPDATE #dealer_MO_KP
SET label = 'dil_MO+KP'
WHERE client_inn_dil IN (SELECT client_inn_dil FROM #CommonInn);
-----------------------

SELECT TOP 10 * FROM #dealer_MO_KP WHERE label='dil_MO+KP'

DROP TABLE IF EXISTS #dealer_MO_KP_modified
SELECT
    transaction_date,
    agent_inn_dil,
    client_inn_dil,
	client_name_dil,
    client_name_GZPN,
    total_monthly_ton_dil,
    CASE WHEN label = 'dil_MO' THEN total_monthly_ton_GZPN ELSE 0 END AS MO_dealer_monthly_ton_GZPN,
    CASE WHEN label = 'dil_KP' THEN total_monthly_ton_GZPN ELSE 0 END AS KP_dealer_monthly_ton_GZPN,
	CASE WHEN label = 'dil_MO+KP' THEN total_monthly_ton_GZPN ELSE 0 END AS MO_KP_monthly_ton_GZPN,
    okved_client,
    label
INTO #dealer_MO_KP_modified
FROM #dealer_MO_KP;


	-- Создаем временную таблицу для новых клиентов, которых нет в dealer_MO_KP_modified
DROP TABLE IF EXISTS #NewClients;

	SELECT
		vttr.transaction_date,
		vttr.agent_inn AS agent_inn_dil,
		vttr.client_inn AS client_inn_dil,
		dll.client_name AS client_name_dil, -- Используем client_name из #dll для client_name_dil
		'Нет данных' AS client_name_GZPN,
		vttr.total_monthly_tons AS total_monthly_ton_dil,
		0 AS MO_dealer_monthly_ton_GZPN, -- Поскольку это новые клиенты, значения для ton_GZPN ставим 0
		0 AS KP_dealer_monthly_ton_GZPN,
		0 AS MO_KP_dealer_monthly_ton_GZPN,
		'Прочие' AS label -- Устанавливаем label в 'Прочие', так как они не попали в категории MO, KP или MO+KP
	INTO #NewClients
	FROM #dll AS dll
	LEFT JOIN #vttr AS vttr ON dll.client_inn = vttr.client_inn
	WHERE NOT EXISTS (
		SELECT 1
		FROM #dealer_MO_KP_modified AS modi
		WHERE modi.client_inn_dil = dll.client_inn
	)
	AND year(vttr.transaction_date) = 2023
	GROUP BY vttr.transaction_date, vttr.agent_inn, vttr.client_inn, dll.client_name, vttr.total_monthly_tons;

	--SELECT count(*) FROM #NewClients --368154

	DROP TABLE IF EXISTS #NewClients_Okved; -- подтягиваем оквэды к таблице с клиентами
SELECT 
	ccl.transaction_date,
	ccl.agent_inn_dil, 
	ccl.client_inn_dil, 
	ccl.client_name_dil,
	ccl.client_name_GZPN, 
	ccl.total_monthly_ton_dil,
	ccl.MO_dealer_monthly_ton_GZPN,
	ccl.KP_dealer_monthly_ton_GZPN,
	ccl.MO_KP_dealer_monthly_ton_GZPN,
	okved.[ИНДЕКС] AS okved_client,
	ccl.label

INTO #NewClients_Okved
FROM #NewClients 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.[ИНДЕКС],
	ccl.label,
	transaction_date,
	total_monthly_ton_dil,
	MO_dealer_monthly_ton_GZPN,
	KP_dealer_monthly_ton_GZPN,
	MO_KP_dealer_monthly_ton_GZPN

SELECT count(*) FROM #NewClients_Okved WHERE okved_client IS NULL --110806
-- добавим информацию по ОКВЭДАМ из [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED]

--проверка сколько совпадающих инн есть между таблицами
--SELECT count(*) AS matching_inn
--FROM #NewClients_Okved 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
--41824

 --хочу заменить пропуски в okved_client на значения из NAIMENOVANIE_OKVED для соответствующих инн-----------------------------------------------------
UPDATE kpc
SET kpc.okved_client = left(go.NAIMENOVANIE_OKVED, 255)
FROM #NewClients_Okved 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;
--(41824 rows affected)

--добавим все во временную таблицу all_labeled_clients
DROP TABLE IF EXISTS #all_labeled_clients
SELECT * 
INTO #all_labeled_clients
FROM (
	SELECT * FROM #NewClients_Okved
	UNION ALL
	SELECT * FROM #dealer_MO_KP_modified) AS combined_data



	DROP TABLE IF EXISTS [SANDBOX_DB_TEAM_21].[dbo].[clients_dealer_labeled_final]
	SELECT * 
	INTO [SANDBOX_DB_TEAM_21].[dbo].[clients_dealer_labeled_final]
	FROM #all_labeled_clients
----------------------------------------------------------------------------
DROP TABLE IF EXISTS #test

select
	f.transaction_date,
	f.agent_inn_dil,
	f.client_inn_dil,
	f.client_name_dil,
	f.client_name_GZPN,
	SUM(f.total_monthly_ton_dil) AS total_monthly_ton_dil, 
	SUM(f.MO_dealer_monthly_ton_GZPN) AS MO_dealer_monthly_ton_GZPN,
	SUM(f.KP_dealer_monthly_ton_GZPN) AS KP_dealer_monthly_ton_GZPN,
	SUM(f.MO_KP_dealer_monthly_ton_GZPN) AS MO_KP_dealer_monthly_ton_GZPN,
	f.okved_client,
	f.label
--INTO #test
FROM [SANDBOX_DB_TEAM_21].[dbo].[clients_dealer_labeled_final]  AS f
WHERE client_inn_dil IS NOT NULL AND ((client_name_dil IS NOT NULL and client_name_dil != 'Нет данных') or (client_name_GZPN IS NOT NULL AND client_name_GZPN != 'Нет данных'))
GROUP BY transaction_date, agent_inn_dil, client_inn_dil, client_name_dil, client_name_GZPN, okved_client, label
ORDER BY transaction_date
Editor is loading...
Leave a Comment