Untitled

-------------------------Задаем константы----------------------------------------------------------------------------------------------------------------------------------------------------------------
 DECLARE @date AS date = DATEADD(MONTH,DATEDIFF(MONTH,0,DATEADD(MONTH,-2,GETDATE())),0) -- начало текущего-2 мес. 

 SELECT	
		CAST(GETDATE() AS date) AS ТекущаяДата,
		@date AS Дата2МесяцаНазад

-------ОСНОВНОЙ КОД для проверки Счет на оплату покупателю из 1С: АСКУ РП----------------------------------------------------------------------------------------------------------------------------------

DROP TABLE IF EXISTS #temp_buyer_invoice;


  SELECT 
	  concat(s.id_rref, convert(date,s.c_date_time), s.c_number, s.document_amount) as concative,
	  CAST(s.c_date_time as date) AS d_date, 
	  s.c_number, 
	  s.is_posted, 
	  c.price AS price,
	  c.quantity AS quantity,
	  s.document_amount AS amount, 
	  s.id_dl,
	  CAST(getdate() AS date) AS today, 
	  CAST(getdate()-1 AS date) AS prev_day, 
	  COUNT(s.c_number)OVER(PARTITION BY s.c_number) AS count_change

  INTO #temp_buyer_invoice
  FROM [DATAMARTS].[ASKU_RP].[vw_buyer_invoice] s
	  LEFT JOIN [DATAMARTS].[ASKU_RP].[vw_buyer_invoice_product] c ON s.id_rref=c.id_rref
  WHERE 
	s.c_date_time>=@date
  GROUP BY 
	concat(s.id_rref, convert(date,s.c_date_time), s.c_number, s.document_amount),
	CAST(s.c_date_time AS date), 
	s.c_number, 
	s.is_posted, 
	c.price, 
	c.quantity, 
	s.document_amount,
	s.id_dl

--SELECT count(*) FROM #temp_buyer_invoice --18704

ALTER TABLE #temp_buyer_invoice
ADD base NVARCHAR(20)

UPDATE #temp_buyer_invoice
	SET base =  
		CASE 
			WHEN id_dl = 6 THEN 'ЦФ'
			WHEN id_dl = 7 THEN 'УФ'
			WHEN id_dl = 8 THEN 'СФ'
		END;

ALTER TABLE #temp_buyer_invoice
ADD status_change NVARCHAR(255)

UPDATE #temp_buyer_invoice
	SET status_change =  
		CASE 
			WHEN count_change=1 THEN 'Без изменений'
			WHEN count_change> 1 THEN 'Есть изменения'
			ELSE 'Нет информации'
		END;


-- Единожды создаем таблицу в оригинальной базе данных

--DROP TABLE IF EXISTS [SANDBOX_DB_TEAM_21].[dbo].[TIT_buyer_invoice_analysis]
---- Создание таблицы buyer_invoice_analysis
--CREATE TABLE [SANDBOX_DB_TEAM_21].[dbo].[TIT_buyer_invoice_analysis] (
--    concative NVARCHAR(MAX),
--    d_date DATE,
--    c_number NVARCHAR(MAX),
--    is_posted NVARCHAR(MAX),
--    price DECIMAL(18, 2),
--    quantity DECIMAL(18, 2),
--    amount DECIMAL(18, 2),
--	id_dl INT,
--    today DATE,
--    prev_day DATE,
--    count_change INT,
--	base NVARCHAR(20),
--    status_change NVARCHAR(255)
--);



-- создание временной таблицы - копируем инфо из главной для подстраховки	
DROP TABLE IF EXISTS #buyer_invoice_analysis_backup
SELECT * 
INTO #buyer_invoice_analysis_backup
FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_buyer_invoice_analysis]

SELECT TOP 5 * FROM #buyer_invoice_analysis_backup ORDER BY today DESC
SELECT count(*) FROM #buyer_invoice_analysis_backup

SELECT TOP 5 * FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_buyer_invoice_analysis] ORDER BY today DESC
SELECT count(*) FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_buyer_invoice_analysis]

--добавление новых/измененных данных
INSERT INTO [SANDBOX_DB_TEAM_21].[dbo].[TIT_buyer_invoice_analysis]
SELECT * FROM #buyer_invoice_analysis_backup
WHERE #buyer_invoice_analysis_backup.concative NOT IN 
(SELECT concative FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_buyer_invoice_analysis])


SELECT TOP 5 * FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_buyer_invoice_analysis] ORDER BY today DESC
SELECT count(*) FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_buyer_invoice_analysis]
	
-----------------------------------------ИТОГ---------------------------------------------------------------------------------------------------------------------------------------------------
SELECT max(today) AS TODAY_BUYER_INVOICE
FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_buyer_invoice_analysis]
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT * 
FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_buyer_invoice_analysis] 
	WHERE status_change = 'Есть изменения' 
	AND month(d_date) = 2
	AND id_dl = 6
ORDER BY d_date DESC