-- Preparing data
CREATE TABLE [dbo].[DemoDebt](
	[ID] [int] NOT NULL,
	[Deal] [varchar](50) NOT NULL,
	[Type] [varchar](50) NOT NULL,
	[Amount] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Employees](
	[Id] [int] NULL,
	[Name] [varchar](50) NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[DemoDebt](id, deal, type, amount)
	VALUES(1, N'B4', N'Comm', 53) 
		, (2, N'B4', N'Sec', 848) 
		, (3, N'B1', N'Comm', 890) 
		, (4, N'B4', N'Un', 62) 
		, (5, N'B4', N'Un', 262) 
		, (6, N'B4', N'CC', 793) 
		, (7, N'B1', N'CC', 77) 
		, (8, N'B2', N'Comm', 892) 
		, (9, N'B2', N'CC', 905) 
		, (10, N'B1', N'Un', 690) 
		, (11, N'B1', N'Sec', 313) 
		, (12, N'B2', N'Sec', 155) 
		, (13, N'B3', N'Comm', 499) 
		, (14, N'B3', N'Comm', 640) 
		, (15, N'B2', N'Sec', 615) 
		, (16, N'B3', N'Sec', 539) 
		, (17, N'B1', N'Comm', 10) 
		, (18, N'B4', N'Comm', 286) 
		, (19, N'B2', N'Comm', 438) 
		, (20, N'B3', N'Sec', 717) 
		, (21, N'B2', N'Sec', 190) 
		, (22, N'B2', N'Comm', 653) 
		, (23, N'B1', N'Sec', 712) 
		, (24, N'B2', N'Un', 473) 
		, (25, N'B2', N'Un', 374) 
		, (26, N'B4', N'Un', 976) 
		, (27, N'B2', N'Comm', 874) 
		, (28, N'B4', N'CC', 977) 
		, (29, N'B4', N'Comm', 798) 
		, (30, N'B2', N'CC', 126) 
		, (31, N'B1', N'Comm', 64) 
		, (32, N'B4', N'CC', 794) 
		, (33, N'B1', N'Sec', 562) 
		, (34, N'B4', N'CC', 519) 
		, (35, N'B4', N'CC', 713) 
		, (36, N'B1', N'Comm', 28) 
		, (37, N'B2', N'Sec', 642) 
		, (38, N'B4', N'Sec', 429) 
		, (39, N'B3', N'Sec', 764) 
		, (40, N'B4', N'CC', 773) 
		, (41, N'B2', N'Comm', 863) 
		, (42, N'B4', N'Comm', 558) 
		, (43, N'B2', N'CC', 654) 
		, (44, N'B1', N'CC', 301) 
		, (45, N'B3', N'Sec', 709) 
		, (46, N'B2', N'Sec', 557) 
		, (47, N'B3', N'Sec', 911) 
		, (48, N'B4', N'Sec', 692) 
		, (49, N'B2', N'Sec', 880) 
		, (50, N'B1', N'CC', 84) 
		, (51, N'B1', N'Un', 368) 
		, (52, N'B2', N'Un', 505) 
		, (53, N'B1', N'Sec', 212) 
		, (54, N'B1', N'Comm', 238) 
		, (55, N'B3', N'Comm', 818) 
		, (56, N'B3', N'Un', 354) 
		, (57, N'B2', N'CC', 217) 
		, (58, N'B2', N'Un', 825) 
		, (59, N'B1', N'CC', 734) 
		, (60, N'B4', N'Sec', 614) 
		, (61, N'B2', N'CC', 365) 
		, (62, N'B1', N'Un', 630) 
		, (63, N'B2', N'Sec', 615) 
		, (64, N'B2', N'Comm', 495) 
		, (65, N'B1', N'Comm', 158) 
		, (66, N'B1', N'Un', 580) 
		, (67, N'B2', N'Comm', 76) 
		, (68, N'B4', N'Sec', 429) 
		, (69, N'B2', N'Un', 593) 
		, (70, N'B1', N'Sec', 252) 
		, (71, N'B4', N'Comm', 22) 
		, (72, N'B3', N'Un', 888) 
		, (73, N'B1', N'Sec', 596) 
		, (74, N'B3', N'Un', 266) 
		, (75, N'B2', N'Comm', 643) 
		, (76, N'B4', N'Comm', 137) 
		, (77, N'B4', N'Un', 698) 
		, (78, N'B3', N'Un', 885) 
		, (79, N'B1', N'Comm', 323) 
		, (80, N'B2', N'Sec', 510);

INSERT INTO [dbo].[Employees](id, name)  
	VALUES(1, N'John') 
	, (2, N'Danial') 
	, (3, N'Beth') 
	, (4, N'Bob') 
	, (5, N'Alice');

SELECT * FROM DemoDebt;
SELECT * FROM Employees;

--==========================================================
/****** Object:  StoredProcedure [dbo].[usp_demo_Exec]    Script Date: 6/26/2021 5:32:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_demo_Exec] 
AS
BEGIN
	SET NOCOUNT ON;

    -- Pre process
	DECLARE @nAveage INT = 0, @nEmpolyeeCount int = 0, @nCurrentProcessEmpoyee INT = 0;
	SELECT @nEmpolyeeCount = COUNT(*) FROM Employees
	SELECT @nAveage = SUM(AMOUNT)/@nEmpolyeeCount
		FROM DemoDebt;

	SELECT Id, 0 AS Processed
		INTO #tmp_Employees
		from Employees;
	SELECT *, 0 AS EmployeeId
		INTO #tmp_Process
		FROM DemoDebt;

	DECLARE @nDebtId INT, @nAmount INT
	DECLARE debt_cursor CURSOR FOR   
	SELECT Id, Amount  
	FROM DemoDebt;  

	-- Process
	OPEN debt_cursor  
	FETCH NEXT FROM debt_cursor  
	INTO @nDebtId, @nAmount;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		select top 1 @nCurrentProcessEmpoyee = Id
		from #tmp_Employees
		WHERE Processed < @nAveage
		ORDER BY Processed;
	
		IF(@nCurrentProcessEmpoyee IS NULL OR @nCurrentProcessEmpoyee = 0)
		BEGIN
			SELECT top 1 @nCurrentProcessEmpoyee = Id
				FROM #tmp_Employees
				ORDER BY Processed;
		END

		UPDATE #tmp_Process
			SET EmployeeId = @nCurrentProcessEmpoyee
			WHERE ID = @nDebtId;

		UPDATE #tmp_Employees
			SET Processed = Processed + @nAmount
			WHERE Id = @nCurrentProcessEmpoyee;

		FETCH NEXT FROM debt_cursor  
		INTO @nDebtId, @nAmount;
	END

	CLOSE debt_cursor;  
	DEALLOCATE debt_cursor; 

	-- Result
	SELECT t.*, e.[Name] 
		FROM #tmp_Process t
			INNER JOIN Employees e ON t.EmployeeId = e.Id
		ORDER BY EmployeeId;
	
	SELECT t.EmployeeId, e.[Name], SUM(t.Amount) AS Amount, COUNT(t.Id) AS Frequency
		FROM #tmp_Process t
			INNER JOIN Employees e ON t.EmployeeId = e.Id
		GROUP BY t.EmployeeId, e.[Name]
		ORDER BY t.EmployeeId, e.[Name];

	DROP TABLE #tmp_Employees;
	DROP TABLE #tmp_Process;
END


--==========================================================
-- Result
EXEC usp_demo_Exec;
