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С: АСКУ РП----------------------------------------------------------------------------------------------------------------------------------
--SELECT count (*) FROM [DATAMARTS].[ASKU_RP].[vw_buyer_invoice] --785070
--SELECT count (distinct c_number) FROM [DATAMARTS].[ASKU_RP].[vw_buyer_invoice] --185213
--SELECT count (distinct id_rref) FROM [DATAMARTS].[ASKU_RP].[vw_buyer_invoice] --785067

--SELECT TOP 10 * FROM [DATAMARTS].[ASKU_RP].[vw_buyer_invoice_product] 

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.id_rref,
	  s.c_number, 
	  s.is_posted, 
	  --c.price AS price,
	  --c.quantity AS quantity,
	  s.document_amount AS amount, 
	  s.id_dl, 
	  CASE 
			WHEN s.id_dl = 6 THEN 'ЦФ'
			WHEN s.id_dl = 7 THEN 'УФ'
			WHEN s.id_dl = 8 THEN 'СФ'
	  END AS base,
	  CAST(getdate() AS date) AS today, 
	  CAST(getdate()-1 AS date) AS prev_day, 
	  COUNT(s.c_number)OVER(PARTITION BY s.c_number, year(CAST(s.c_date_time as date))) 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.id_rref,
	s.is_posted, 
	--c.price, 
	--c.quantity, 
	s.document_amount,
	s.id_dl


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;

SELECT * FROM [DATAMARTS].[ASKU_RP].[vw_buyer_invoice] WHERE id_rref = 'A31D0050569267F111EEBFEEAAF9FD7E'
SELECT * FROM [DATAMARTS].[ASKU_RP].[vw_buyer_invoice_product] where id_rref = 'A31D0050569267F111EEBFEEAAF9FD7E'
select top 10 * from [DATAMARTS].[ASKU_RP].[vw_counterparty_data] WHERE c_code = '551_000_00000334' OR id_rref = 'A31D0050569267F111EEBFEEAAF9FD7E'


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

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,
	id_rref NVARCHAR(32),
    c_number NVARCHAR(32),
    is_posted NVARCHAR(2),
   -- price DECIMAL(18, 2),
   -- quantity DECIMAL(18, 2),
    amount DECIMAL(18, 2),
	id_dl INT,
	base NVARCHAR(20),
    today DATE,
    prev_day DATE,
    count_change INT,
    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) IN ('1','2')
ORDER BY d_date DESC, c_number

SELECT * 
FROM [SANDBOX_DB_TEAM_21].[dbo].[TIT_buyer_invoice_analysis] 
	WHERE c_number = '552_000_00012227'