Untitled

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;


	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



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, ИНДЕКС;




	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);
-----------------------

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;



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


--добавим все во временную таблицу 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