SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE customers ( id int not null identity primary key, name varchar(255) not null ); CREATE TABLE products ( id int not null identity primary key, name varchar(255) not null ); CREATE TABLE plans ( id int not null identity primary key, date_created date not null, customer_id int foreign key references customers(id), product_id int foreign key references products(id), amount_product int not null ); CREATE TABLE production_accounting ( id int not null identity primary key, date_created date not null, customer_id int foreign key references customers(id), product_id int foreign key references products(id), amount_finished_products int not null ); insert into customers(name) values('BGE'); insert into customers(name) values('LBFG'); insert into customers(name) values('FGE'); insert into products(name) values('nil'); insert into products(name) values('hammer'); insert into plans(date_created, customer_id, product_id, amount_product) values('2022-01-1', 1, 1, 5000); insert into plans(date_created, customer_id, product_id, amount_product) values('2022-02-1', 1, 1, 300); insert into plans(date_created, customer_id, product_id, amount_product) values('2022-03-1', 1, 1, 200); insert into plans(date_created, customer_id, product_id, amount_product) values('2022-01-1', 2, 1, 300); insert into plans(date_created, customer_id, product_id, amount_product) values('2022-02-1', 2, 1, 200); insert into plans(date_created, customer_id, product_id, amount_product) values('2022-03-1', 2, 1, 700); insert into plans(date_created, customer_id, product_id, amount_product) values('2022-01-1', 3, 2, 100); insert into plans(date_created, customer_id, product_id, amount_product) values('2022-02-1', 3, 2, 100); insert into plans(date_created, customer_id, product_id, amount_product) values('2022-03-1', 3, 2, 100); insert into production_accounting(date_created, customer_id, product_id, amount_finished_products) values('2022-01-1', 1, 1, 5000); insert into production_accounting(date_created, customer_id, product_id, amount_finished_products) values('2022-02-1', 1, 1, 300); insert into production_accounting(date_created, customer_id, product_id, amount_finished_products) values('2022-03-1', 1, 1, 200); insert into production_accounting(date_created, customer_id, product_id, amount_finished_products) values('2022-01-1', 2, 1, 300); insert into production_accounting(date_created, customer_id, product_id, amount_finished_products) values('2022-02-1', 2, 1, 200); insert into production_accounting(date_created, customer_id, product_id, amount_finished_products) values('2022-03-1', 2, 1, 500); insert into production_accounting(date_created, customer_id, product_id, amount_finished_products) values('2022-01-1', 3, 2, 100); insert into production_accounting(date_created, customer_id, product_id, amount_finished_products) values('2022-02-1', 3, 2, 100); insert into production_accounting(date_created, customer_id, product_id, amount_finished_products) values('2022-03-1', 3, 2, 10); -- Мой вариант через два запроса SELECT [customers].[name], [products].[name], SUM([plans].[amount_product]) AS plan_sum_products FROM [plans] LEFT JOIN [products] ON [products].[id] = [plans].[product_id] LEFT JOIN [customers] ON [customers].[id] = [plans].[customer_id] WHERE DATEPART(quarter, [plans].[date_created]) = 1 AND YEAR([plans].[date_created]) = YEAR(GETDATE()) GROUP BY [customers].[name], [products].[name] ORDER BY [customers].[name]; SELECT [customers].[name], [products].[name], SUM([production_accounting].[amount_finished_products]) AS accounting_sum_products FROM [production_accounting] LEFT JOIN [products] ON [products].[id] = [production_accounting].[product_id] LEFT JOIN [customers] ON [customers].[id] = [production_accounting].[customer_id] WHERE DATEPART(quarter, [production_accounting].[date_created]) = 1 AND YEAR([production_accounting].[date_created]) = YEAR(GETDATE()) GROUP BY [customers].[name], [products].[name] ORDER BY [customers].[name]; SELECT [customers].[name], [products].[name], plan_sum_products, accounting_sum_products FROM ( SELECT [customer_id], [product_id], SUM([plans].[amount_product]) AS plan_sum_products FROM [plans] WHERE DATEPART(quarter, [plans].[date_created]) = 1 AND YEAR([plans].[date_created]) = YEAR(GETDATE()) GROUP BY [customer_id], [product_id] ) [plans] FULL OUTER JOIN ( SELECT [customer_id], [product_id], SUM([production_accounting].[amount_finished_products]) AS accounting_sum_products FROM [production_accounting] WHERE DATEPART(quarter, [production_accounting].[date_created]) = 1 AND YEAR([production_accounting].[date_created]) = YEAR(GETDATE()) GROUP BY [customer_id], [product_id] ) [production_accounting] ON [production_accounting].customer_id = [plans].customer_id and [production_accounting].product_id = [plans].product_id LEFT JOIN [products] ON [products].[id] = coalesce([production_accounting].product_id, [plans].product_id) LEFT JOIN [customers] ON [customers].[id] = coalesce([production_accounting].customer_id, [plans].customer_id) ;