Программирование на SQL

Целью данной задачи является создание запроса или процедуры для изучения ваших знаний SQL Server. Задача не совсем простая и решений может быть несколько. Если Вы претендуете на вакансию Разработчика SQL Server, то Вам будет дано на решение не более 3 дней

Необходимо написать скрипт, который выводит таблицу, в которой для каждого клиента указана следующая информация
1. Наименование клиента
2. ИНН клиента
3. № счета, по которому есть открытая поставка (действующая на дату 01.01.2021) по продукту «Продукт А».
Если есть несколько счетов, удовлетворяющие данному условию, необходимо вывести тот счет, по которому создана
открытая поставка по «Продукт А» с максимальной датой окончания. Если нет счетов, удовлетворяющих данному условию,
необходимо вывести счет с максимальной датой оплаты, в котором есть строки на «Продукт А» на подключение или продление.
Не более одного счета на клиента
4. Дата создания счета из п.3
5. Дата оплаты счета из п.3
6. Сумма счета по всем строкам из п.3
7. Оплата по счету по всем строкам из п.3

Примечания:
● У каждого счета должна быть хотя бы одна строка счета;
● По каждой строке счета может быть создано любое количество поставок, а может не быть ни одной;
● У клиента может быть несколько параллельных поставок по одному продукту.
● Cost и Paid указывается за суммарное количество продуктов/услуг в строке счета, т.е. стоимость 1 шт. из строки счета = Cost/Cnt.

Таблицы для решения

--создание объектов
--Клиенты
create table dbo.Clients (
  cID   int           not null
 ,Name  nvarchar(500) collate Cyrillic_General_CI_AS not null
 ,Inn   nvarchar(12)  collate Cyrillic_General_CI_AS not null
 ,Phone nvarchar(15)  collate Cyrillic_General_CI_AS null
 ,Email nvarchar(500) collate Cyrillic_General_CI_AS null
);
alter table dbo.Clients add constraint PK_Clients primary key clustered (cID);

--Счета
create table dbo.Bills (
  bID     int          not null
 ,Num     nvarchar(50) collate Cyrillic_General_CI_AS not null
 ,BDate   date         not null
 ,PayDate date         null
 ,cID     int          not null
);
alter table dbo.Bills add constraint PK_Bills primary key clustered (bID);
alter table dbo.Bills add constraint FK_Bills_cID foreign key (cID) references dbo.Clients (cID);

--Строки счета
create table dbo.BillContent (
  bcID        int            not null
 ,bID         int            not null
 ,Product     nvarchar(50)   collate Cyrillic_General_CI_AS null
 ,TariffName  nvarchar(1000) collate Cyrillic_General_CI_AS null
 ,ServiceName nvarchar(1000) collate Cyrillic_General_CI_AS null
 ,TypeID      tinyint        not null
 ,Cost        money          not null
 ,Paid        money          null
 ,Cnt         int            not null
);
alter table dbo.BillContent add constraint PK_BillContent primary key clustered (bcID);
alter table dbo.BillContent add constraint FK_BillContent_bID foreign key (bID) references dbo.Bills (bID);

--Поставки
create table dbo.RetailPacks (
  rpID  int  not null
 ,bcID  int  not null
 ,Since date not null
 ,UpTo  date not null
);
alter table dbo.RetailPacks add constraint PK_RetailPacks primary key clustered (rpID);
alter table dbo.RetailPacks add constraint FK_RetailPacks_bcID foreign key (bcID) references dbo.BillContent (bcID);

--Ответ
create table #Answer (
  Name    nvarchar(500) collate Cyrillic_General_CI_AS null
 ,Inn     nvarchar(12)  collate Cyrillic_General_CI_AS not null
 ,Num     nvarchar(50)  collate Cyrillic_General_CI_AS null
 ,BDate   date          null
 ,PayDate date          null
 ,BillSum money         null
 ,BillPay money         null
);

Решение задачи

Предлагаемое решение содержит процедуру, и небольшую ошибку, которую Вы должны исправить сами. Таблицы тоже заполните сами. Таким образом,  это позволит Вам понять хорошо ли Вы разбираетесь в этой теме и сможете ли находить и решать различные проблемы в  SQL Server. Важно. Желательно Вам с нуля написать решение или план данной задачи, а не просто почитать готовое решение

-- Запуск процедуры
EXEC TestSQLServer_SP '2021-01-01T00:00:00','Продукт А',0

-- Тесты --
EXEC TestSQLServer_SP 
EXEC TestSQLServer_SP '2020-09-16T00:00:00',NULL

ALTER PROCEDURE [dbo].[TestSQLServer_SP] 
	@DateP as DateTime = '2021-01-01',
	@Product as nvarchar(512) = NULL,
	@TEST as int = 1
AS
BEGIN

	SET NOCOUNT ON;

	-- Создадим временную таблицу для упрощения вычислений и тестирования
	SELECT c.cID, b.bID, c.Name, c.Inn, b.Num, b.BDate, b.PayDate, bc.Cost, bc.Paid, bc.Product, bc.Cnt, rp.Since, rp.UpTo, 
	CASE
		WHEN  @DateP between rp.Since AND rp.UpTo THEN rp.UpTo
		ELSE NULL
	END	
	as UpToDate 
	INTO #Clients FROM Clients c
		LEFT JOIN Bills b ON c.cID = b.cID
		    LEFT JOIN BillContent bc ON bc.bID = b.bID 
				LEFT JOIN RetailPacks rp ON bc.bcID = rp.bcID
					WHERE bc.Product = ISNULL(@Product, bc.Product)

	-- Получаем результат
	;WITH CTE AS
	( SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY cID ORDER BY UpToDate DESC, PayDate DESC)
	  FROM #Clients
	)
	SELECT c.[Name], c.Inn, c.Num, c.BDate, c.PayDate, Sum(c.Cost) as SumCost, Sum(Paid) as SumPaid FROM CTE c WHERE c.RN=1 GROUP BY 
		c.[Name], c.Inn, c.Num, c.BDate, c.PayDate 

	-- Тестирование --
	IF @TEST = 1
	BEGIN
		SELECT 'OK' as 'Test', *,RN = ROW_NUMBER() OVER (PARTITION BY cID ORDER BY UpToDate DESC, PayDate DESC) FROM #Clients
	END