Untitled
unknown
plain_text
a year ago
32 kB
4
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