Untitled

mail@pastecode.io avatar
unknown
mysql
22 days ago
11 kB
1
Indexable
Never

----Переменные----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   
 -- --анализируемый период
 --   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 
Leave a Comment