Untitled
unknown
mysql
a year ago
11 kB
2
Indexable
----Переменные----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- --анализируемый период
-- DECLARE @date_begin AS date = '2023-08-01' --анализируемый_период_начало
--DECLARE @date_end AS date = '2023-08-31' --анализируемый_период_конец
-- --анализируемые инн
--DECLARE @inn_1 AS nvarchar(20) = '7730555713' --первый-анализируемый_инн
--DECLARE @inn_2 AS nvarchar(20) = '4028034085' --второй-анализируемый_инн
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--DROP TABLE IF EXISTS [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED]
--CREATE TABLE [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED]
-- (INN [nvarchar](255) NULL,
-- KOD_OKVED [nvarchar](255) NULL,
-- VID_OKVED [nvarchar](255) NULL,
-- NAIMENOVANIE_OKVED [nvarchar](300) NULL)
-- BULK INSERT [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED]
--FROM '\\gazprom-neft.local\DFS\Газпром нефть\Проекты\DataLakeDRP_SandBox\TEAM_21\Юля_БКД\Справочники\ОКВЭД\Катя выгрузка Дилер-клиент.csv'
--WITH(
--FIRSTROW =2,
--ROWTERMINATOR='\n',
--FIELDTERMINATOR=';',
--FORMAT='csv',
--CODEPAGE='1251');
--SELECT *
--INTO [SANDBOX_DB_TEAM_21].[dbo].[GEN_OKVED]
--FROM #ok WHERE VID_OKVED='Основной'
----Конечные клиенты дилеров---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Ранжирование конечных клиентов дилеров
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 count(*) FROM #cll
--SELECT * FROM [DATAMARTS].[dpa].[vw_clients]
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Продажи конечных клиентов дилеров
--DROP TABLE IF EXISTS #vtt_v2
--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_v2
--FROM [DATAMARTS].[dpa].vw_transactions vt -- продажи конечных клиентов
----WHERE YEAR(vt.datetime_trn)=YEAR(dateadd(year, -1, getdate())) -- текущий год -1 UPD: за весь приод берем
----AND agent_inn IN (@inn_1,@inn_2)
----AND sale_channel='НП'
--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, --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 -- продажи конечных клиентов
--WHERE YEAR(vt.datetime_trn)=YEAR(dateadd(year, -1, getdate())) -- текущий год -1 UPD: за весь приод берем
--AND agent_inn IN (@inn_1,@inn_2)
--AND sale_channel='НП'
GROUP BY vt.datetime_trn, vt.client_inn, vt.contract_name, vt.region
--SELECT count(*) FROM #vtt -- 17011925 строк
--SELECT count(*) FROM #vtt_v2 -- 17011740 строк
--SELECT *
--FROM #vtt vtt
--LEFT JOIN #vtt_v2 vtt_v2
--ON vtt.client_inn=vtt_v2.client_inn
--AND vtt.contract_name=vtt_v2.contract_name
--AND vtt.region=vtt_v2.region
--WHERE vtt_v2.client_inn IS NULL
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS #vttr
SELECT t.client_inn, t.contract_name, t.region, t.MAX_DOC_date
INTO #vttr
FROM #vtt t
GROUP BY t.client_inn, t.contract_name, t.region, t.MAX_DOC_date
SELECT TOP 10 * FROM #vttr
SELECT COUNT(*) FROM #vttr
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Продаж конечных клиентов и справочника договоров
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
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----мэппинг АЗС для КК
-- 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
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--анализируемые инн
DROP TABLE IF EXISTS #wee;
SELECT KONTRAGENT_INN, 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)>='2021'
--AND s.KONTRAGENT_INN='7327060811'
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
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
----------------------------------------------------------------
--ИТОГ-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS #match_date
SELECT l.agent_inn, l.agent_name, l.client_inn, l.client_name, l.region, l.contract_name, l.MIN_DOC_date, l.MAX_DOC_date,
e.OTDELENIE, e.DOGOVORKONTRAGENTA_NOMER_DOGOVORA, e.DOGOVORKONTRAGENTA_DATA_DOGOVORA, MAX_DOC_DATE_KK
INTO #match_date
FROM #dll l
LEFT JOIN #reg e
ON l.client_inn=e.KONTRAGENT_INN
GROUP BY l.agent_inn, l.agent_name, l.client_inn, l.client_name, l.region, l.contract_name, l.MIN_DOC_date, l.MAX_DOC_date,
e.OTDELENIE, e.DOGOVORKONTRAGENTA_NOMER_DOGOVORA, e.DOGOVORKONTRAGENTA_DATA_DOGOVORA, MAX_DOC_DATE_KK
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
Editor is loading...
Leave a Comment