Untitled
Wholesales ----Конечные клиенты дилеров--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Ранжирование конечных клиентов дилеров 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DROP TABLE IF EXISTS #vttr SELECT t.client_inn, t.agent_inn, --upd: 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Продаж конечных клиентов и справочника договоров 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --анализируемые инн продаж по клиентам мелкого опта из [GPN_WHOLESALE_TRANSACTIONS] за 2023 год ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --SELECT TOP 5 * FROM [DATAMARTS].[FINAL].[GPN_WHOLESALE_TRANSACTIONS] WHERE GOD = '2024' --SELECT DISTINCT KANAL_PRODAZH_VYVERENNYJ FROM [DATAMARTS].[FINAL].[GPN_WHOLESALE_TRANSACTIONS] -- розница, мелкий опт --SELECT DISTINCT TIP_NEFTEPRODUKTA FROM [DATAMARTS].[FINAL].[GPN_WHOLESALE_TRANSACTIONS] -- бензин, дт, прочее, суг DROP TABLE IF EXISTS #wh; SELECT wt.INN AS client_inn, wt.NAIMENOVANIEKONTRAGENTAVYVERENNOE AS client_name, wt.SALES_CONTRACT_NUMBER AS contract_number, wt.DATA_REALIZACII AS date_operation, 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 GROUP BY INN, NAIMENOVANIEKONTRAGENTAVYVERENNOE, SALES_CONTRACT_NUMBER, DATA_REALIZACII, DOKUMENT_REGISTRATOR ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DROP TABLE IF EXISTS #whg SELECT w.client_inn, w.client_name, w.contract_number, w.doc_reg_number, CAST(w.MAX_DOC_DATE_MO AS date) AS MAX_DOC_DATE_MO INTO #whg FROM #wh w GROUP BY w.client_inn, w.client_name, w.contract_number, w.doc_reg_number, w.MAX_DOC_DATE_MO --SELECT count(*) FROM #whg --270114 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --SELECT TOP 5 * FROM [DATAMARTS].[ASKU_RP].[vw_counterparty_contract_segment] --SELECT TOP 5 * FROM [asku_kp_cdd].[cdd].[organization_department] --регион объекта управления --SELECT TOP 5 * FROM #whg --SELECT TOP 5 * FROM [DATAMARTS].[FINAL].[GPN_WHOLESALE_TRANSACTIONS] WHERE GOD = '2024' --SELECT count(*) AS matching_inn --FROM [DATAMARTS].[ASKU_RP].[vw_counterparty_contract_segment] AS kpc --INNER JOIN #whg AS go --ON kpc.inn = go.client_inn; 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.MAX_DOC_DATE_MO 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 --270146 --SELECT top 10 * from #reg --SELECT TOP 10 * FROM [DATAMARTS].[ASKU_RP].[vw_counterparty_contract_segment] ---------- проверка на NULL---------- --SELECT count(*) --FROM #reg --WHERE client_inn IS NULL -- OR client_name IS NULL -- OR contract_number IS NULL --41 NULL -- OR doc_reg_number IS NULL -- OR department IS NULL -- OR MAX_DOC_DATE_MO IS NULL ------------------------------------------------ -- валидация на ложную подстановку-- --SELECT count(*) AS mismatch_count --FROM #reg --WHERE client_inn != client_inn_2 AND client_name != counterparty_name; --0 несовпадений-- ------------------------------------------------------------------------------------------------ ---- Создаем временную таблицу для клиентов из GPN_WHOLESALE_TRANSACTIONS за 2023 год --SELECT TOP 10 * FROM #reg ----Дилеры и их клиенты находятся из dll --SELECT TOP 10 * FROM #dll -- Создаем таблицу с клиентами, которые есть и у дилеров, и в GPN_WHOLESALE_TRANSACTIONS за 2023 год DROP TABLE IF EXISTS #CommonClients; SELECT dl.agent_inn AS agent_inn_dil, dl.client_inn AS client_inn_dil, LOWER(dl.client_name) AS client_name_dil, r.department AS department, r.client_inn AS client_inn_GZPN, LOWER(r.client_name) AS client_name_GZPN INTO #CommonClients FROM #dll dl INNER JOIN #reg r ON dl.client_inn = r.client_inn --61453 или 767 если -> --AND dl.client_name=r.client_name SELECT count(*) FROM #CommonClients --61453 SELECT count(*) FROM #CommonClients WHERE agent_inn_dil IS NOT null --54102 -- валидация на ложную подстановку-- --SELECT count(*) AS mismatch_count --FROM #CommonClients --WHERE client_inn_dil != client_inn_GZPN AND client_name_dil != client_name_GZPN; --0 несовпадений-- ---------- проверка на NULL-------------- --SELECT count(*) -- --FROM #CommonClients --WHERE agent_inn_dil IS NULL -- 7351 -- OR client_inn_dil IS NULL -- OR client_name_dil IS NULL -- OR client_inn_GZPN IS NULL -- OR client_name_GZPN IS NULL -- OR department IS NULL --Общий скрипт 7351 NULL ------------------------------------------------ --ИТОГО-- -- Всего клиентов (61453 клиентов) SELECT count(*) FROM #CommonClients -- Подсчет количества уникальных клиентов (116 клиентов) SELECT COUNT(DISTINCT client_inn_dil) FROM #CommonClients; -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --ОКВЭДЫ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --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_inn_GZPN, ccl.client_name_GZPN, ccl.department, okved.[ИНДЕКС] AS okved_client INTO #KP_CommonClientsOkved FROM #CommonClients ccl LEFT JOIN [SANDBOX_DB_TEAM_21].[dbo].[Med_Okved] okved ON ccl.client_inn_GZPN=okved.[ИНН] GROUP BY ccl.agent_inn_dil, ccl.client_inn_dil, ccl.client_name_dil, ccl.client_inn_GZPN, ccl.client_name_GZPN, ccl.department, okved.[ИНДЕКС] SELECT count(*) FROM #KP_CommonClientsOkved SELECT * FROM #KP_CommonClientsOkved SELECT count(*) FROM #KP_CommonClientsOkved WHERE agent_inn_dil IS NULL -- 27 пропусков в данных SELECT count(*) FROM #KP_CommonClientsOkved WHERE client_name_dil IS NULL -- 27 пропусков в данных -------------------- попробуем восстановить--------------------------------------- -- сперва 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 #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 = '0276906995' SELECT * FROM #vttr WHERE #vttr.client_inn = '0276906995' -- вроде ок -- теперь client_name_dil--тут чуть проще, так как связь однозначная UPDATE kpc SET kpc.client_name_dil = reg.client_name FROM #KP_CommonClientsOkved kpc INNER JOIN #reg reg ON kpc.client_inn_dil = reg.client_inn WHERE kpc.client_name_dil IS NULL -- проверка SELECT * FROM #KP_CommonClientsOkved WHERE #KP_CommonClientsOkved.client_inn_dil = '0276906995' SELECT * FROM #reg WHERE #reg.client_inn = '0276906995' -- вроде ок SELECT count(*) FROM #KP_CommonClientsOkved WHERE okved_client IS NULL --24 пустых оквэда -- добавим информацию по ОКВЭДАМ из [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED] --хочу заменить пропуски в ИНДЕКС на значения из NAIMENOVANIE_OKVED для соответствующих инн----------------------------------------------------- UPDATE kpc SET kpc.okved_client = go.NAIMENOVANIE_OKVED FROM #KP_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; SELECT * FROM #KP_CommonClientsOkved --проверка сколько совпадающих инн есть между таблицами --SELECT count(*) AS matching_inn --FROM #KP_CommonClientsOkved AS kpc --INNER JOIN [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED] AS go --ON kpc.client_inn_dil = go.INN; -- хояу заменить ИНДЕКС, который был добавленен из [Med_Okved] на описание из [GEN_OKVED], так как оно кажется более полным UPDATE kpc SET kpc.okved_client = go.NAIMENOVANIE_OKVED FROM #KP_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 ------------------------------------------ SELECT * FROM #KP_CommonClientsOkved DROP TABLE IF EXISTS [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_MO] SELECT * INTO [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_MO] FROM #KP_CommonClientsOkved FullKP ----Конечные клиенты дилеров--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Ранжирование конечных клиентов дилеров 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 TOP 5 * FROM [DATAMARTS].[dpa].vw_transactions --SELECT TOP 5 * FROM #vtt --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, --upd: 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 ---- без t.agent_inn --SELECT COUNT(*) FROM #vttr --74678 --SELECT count(*) FROM #vttr WHERE region IS NULL --4237 --SELECT count(*) FROM #vttr WHERE region IS not NULL --70441 ---- с t.agent_inn --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 * FROM #dll WHERE #dll.contract_name IS NULL ---- #vttr check --SELECT TOP 5 * FROM #vttr ---- #cll check --SELECT TOP 5 * FROM #cll ---- #dll check --SELECT * FROM #dll --SELECT TOP 10 * FROM #dll --74887 --SELECT COUNT(*) FROM #dll WHERE agent_inn IS not NULL --58639 --SELECT COUNT(*) FROM #dll WHERE agent_inn IS NULL -- 16248 --SELECT * FROM #dll WHERE agent_inn IS NULL AND client_inn='524900669020' --SELECT * FROM #vttr WHERE client_inn='524900669020' --SELECT * FROM #cll WHERE client_inn='524900669020' ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----мэппинг АЗС для КК -- DROP TABLE IF EXISTS #coord -- SELECT o.c_code AS КОД_АСКУ, o.c_comment AS КОММЕНТАРИЙ_АСКУ, -- o.c_description AS ОПИСАНИЕ_АСКУ, ou.standart_code КОД_СТАНДАРТ, -- ou.standart_description AS ОПИСАНИЕ_СТАНДАРТ, em.COORD_EAST, em.COORD_NORTH -- INTO #coord -- FROM [asku_rp_kk_cdd].[cdd].[operating_unit] o -- LEFT JOIN [DATAMARTS].[DICT].[vw_operation_unit] ou -- ON o.id_rref=ou.ou_ku_id_asku_rp_kk -- LEFT JOIN [DATAMARTS].[EMIS].[VW_AZSS_GPN] em -- ON ou.standart_code_emis=em.GPNAZS_ID -- GROUP BY o.c_code, o.c_comment, o.c_description, ou.standart_code, -- ou.standart_description, em.COORD_EAST, em.COORD_NORTH ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --анализируемые инн, по которым прошли транзакции по топливным картам юридических лиц РФ у наших клиентов из [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 count(*) FROM #reg --74318 --ИТОГ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 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 AND l.client_name=e.KONTRAGENT_NAIMENOVANIE --upd: добавил новое соответствие 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 TOP 5 * FROM #match_date WHERE agent_inn IS NOT NULL --SELECT TOP 5 * FROM #dll WHERE agent_inn IS NOT NULL --SELECT TOP 5 * FROM #reg --SELECT COUNT(DISTINCT agent_inn) FROM #match_date --75047 --SELECT COUNT(*) FROM #match_date WHERE agent_inn IS not NULL --58799 --SELECT COUNT(*) FROM #match_date WHERE agent_inn IS NULL -- 16248 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 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 -- Создаем таблицу с клиентами, которые есть и у дилеров, и в 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 --8455 или 417 если -> AND dl.client_name=r.KONTRAGENT_NAIMENOVANIE --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 ---- Всего клиентов (8455 клиентов) --SELECT count(*) FROM #CommonClients ---- Подсчет количества уникальных клиентов (906 клиентов) --SELECT COUNT(DISTINCT client_inn_dil) FROM #CommonClients; -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --ОКВЭДЫ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --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.agent_inn_dil, ccl.client_inn_dil, ccl.client_name_dil, ccl.client_name_GZPN, okved.[ИНДЕКС] -- посмотрим на получившуюся таблицу, видим много пропусков --SELECT * FROM #CommonClients --GROUP BY agent_inn_dil, client_inn_dil, client_name_dil, client_name_GZPN --SELECT count(client_inn_dil) FROM #KP_CommonClientsOkved --1083 строк --SELECT count(DISTINCT client_inn_dil) FROM #KP_CommonClientsOkved -- 906 уникальных инн --SELECT count(*) FROM #KP_CommonClientsOkved WHERE agent_inn_dil IS NULL OR 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 -- Выбор 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 -- проверка --SELECT * FROM #KP_CommonClientsOkved WHERE #KP_CommonClientsOkved.client_inn_dil = '0275918765' --SELECT * FROM #reg WHERE #reg.KONTRAGENT_INN = '0275918765' -- вроде ок -- добавим информацию по ОКВЭДАМ из [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED] --хочу заменить пропуски в ИНДЕКС на значения из NAIMENOVANIE_OKVED для соответствующих инн----------------------------------------------------- UPDATE kpc SET kpc.ИНДЕКС = LEFT(go.NAIMENOVANIE_OKVED, 255) FROM #KP_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.ИНДЕКС IS NULL; --проверка сколько совпадающих инн есть между таблицами SELECT count(*) AS matching_inn FROM #KP_CommonClientsOkved AS kpc INNER JOIN [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED] AS go ON kpc.client_inn_dil = go.INN; -- хочу заменить ИНДЕКС, который был добавленен из [Med_Okved] на описание из [GEN_OKVED], так как оно кажется более полным UPDATE kpc SET kpc.ИНДЕКС = go.NAIMENOVANIE_OKVED FROM #KP_CommonClientsOkved 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 ------------------------------------------ SELECT * FROM #KP_CommonClientsOkved DROP TABLE IF EXISTS [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_FULLKP] SELECT * INTO [SANDBOX_DB_TEAM_21].[dbo].[TIT_dealer_FULLKP] FROM #KP_CommonClientsOkved SELECT TOP 10 * FROM #reg
Leave a Comment