Untitled

SELECT TOP 10 * FROM [DATAMARTS].[ASKU_RP].[vw_buyer_invoice] WHERE id_rref = 'A31D0050569267F111EED17AC69E48D5'
SELECT TOP 10 * FROM [DATAMARTS].[ASKU_RP].[vw_buyer_invoice_product] WHERE id_rref = 'A31D0050569267F111EED17AC69E48D5'

SELECT DISTINCT id_dl FROM [DATAMARTS].[ASKU_RP].[vw_buyer_invoice]

  FROM 
	[DATAMARTS].[ASKU_RP].[vw_oil_product_docket_application] s
	  LEFT JOIN [DATAMARTS].[ASKU_RP].[vw_counterparty] c ON s.consignee_rrref=c.id_rref
	  LEFT JOIN [DATAMARTS].[ASKU_RP].[vw_operating_unit] o ON s.operating_unit_rref=o.id_rref

SELECT TOP 5 * FROM [DATAMARTS].[ASKU_RP].[vw_oil_product_docket_application] -- WHERE id_rref = 'A22700215AEEF21C11E296A2DB3A4A5E'
SELECT TOP 5 * FROM [DATAMARTS].[ASKU_RP].[vw_counterparty]  
SELECT TOP 5 * FROM [DATAMARTS].[ASKU_RP].[vw_operating_unit] WHERE id_rref = 'A22700215AEEF21C11E296A2DB3A4A5E'

SELECT TOP 10 * FROM [DATAMARTS].[ASKU_RP].[vw_buyer_invoice] 

-------------------------Задаем константы----------------------------------------------------------------------------------------------------------------------------------------------------------------
 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, 
	  CAST(getdate() AS date) AS today, 
	  CAST(getdate()-1 AS date) AS prev_day, 
	  (CASE WHEN s.is_posted='01' THEN CAST(getdate()-1 AS date) ELSE NULL END) AS prev_day_01,
	  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

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


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;