-- создание таблиц
CREATE TABLE zp_test
(
rec_date nvarchar(50),
end_date nvarchar(50),
name NVARCHAR(255) NOT NULL,
vid_nadbavki nvarchar(255),
summa float
)
;
INSERT INTO zp_test (rec_date,end_date,name,vid_nadbavki,summa)
VALUES('01.01.2023','05.05.2023','руслан','оклад',000),
('01.01.2023','05.05.2023','руслан','проф мастерство',000),
('01.01.2023','05.05.2023','руслан','питание',000)
CREATE TABLE zp_fact
(
rec_date nvarchar(50),
end_date nvarchar(50),
name NVARCHAR(255) NOT NULL,
vid_nadbavki nvarchar(255),
summa float
)
INSERT INTO zp_fact (rec_date, end_date,name,vid_nadbavki,summa)
VALUES('06.05.2023','12.12.2023','руслан','оклад',500),
('06.05.2023','12.12.2023','руслан','проф мастерство',400),
('06.05.2023','12.12.2023','руслан','питание',300)
-- *************
select * from zp_fact
select name, sum(summa) as summa
into ##test_sum
from zp_test
group by name
select name, sum(summa) as summa
into ##fact_sum
from zp_fact
group by name
Go
BEGIN TRANSACTION
update zp_fact
set name = 'ЗАКРЫТО'
where name
in (select f.name
from ##fact_sum f
join ##test_sum t on t.name = f.name
where t.summa != f.summa
)
insert into zp_fact (rec_date,end_date,name,vid_nadbavki,summa)
select rec_date
, end_date
, name
, vid_nadbavki
, summa
from zp_test
where name not in (select f.name from zp_fact f)
COMMIT
Go
select * from zp_fact
Telegram: DB developers channel