Untitled
unknown
plain_text
a year ago
4.7 kB
4
Indexable
-------------------------Задаем константы---------------------------------------------------------------------------------------------------------------------------------------------------------------- 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
Editor is loading...
Leave a Comment