set nocount on;

-- Clear results from previous tests (if any)
update dbo.Registrations set SessionNo = NULL;
truncate table dbo.Sessions;

drop table if exists #regs;
drop table if exists #bridge;

-- Force empty cache
checkpoint;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
go

--The time starts from here
declare @dtstart datetime = getdate(),
		@milliseconds int;

with 
C as (	select row_number() over (partition by year, quarter, ABS(50 - NumCandidates) order by NumCandidates) Rn,
		       count(*) over (partition by year, quarter, ABS(50 - NumCandidates)) CountEl,
			   Year, Quarter, SubjCode, NumCandidates, ABS(50 - NumCandidates) as AbsDiff
		from Registrations)

select ROW_NUMBER() over(partition by year, quarter order by AbsDiff, ABS(Rn - (ABS(CountEl - Rn) + 1)), NumCandidates) Rn, 
	   Year, Quarter, SubjCode, NumCandidates
into #regs
from C;

--select * from #regs order by Year, Quarter, Rn

with 
C1 as ( --As anchor member of the recursive cte, it considers records having Rn = 1
		select Rn, Year, Quarter, SubjCode, NumCandidates, NumCandidates CustomSum
		from #regs
		where Rn = 1
		
		union all

		--You add the new value of NumCandidates to the sum of the previous record
		--If the running sum remains less than the maximum capacity of the bin or it reaches exactly the maximum capacity of the bin 
		--   then there is nothing to do
		--If the running sum overtakes the maximum capacity of the bin, the new value will be part of a new bin and so 
		--   the new value is added to the multiple of 100 that has been overtaken
		select Regs.Rn, Regs.Year, Regs.Quarter, Regs.SubjCode, Regs.NumCandidates,
				case when (ca.NewSumValue / 100 = C1.CustomSum / 100) or (ca.NewSumValue % 100 = 0) then ca.NewSumValue 
					 else (ca.NewSumValue / 100 * 100) + Regs.NumCandidates 
				end
		from #regs Regs
		inner join C1 on Regs.Rn = C1.Rn + 1 and Regs.Year = C1.Year and Regs.Quarter = C1.Quarter
		--You use the apply operator in order to have a more readable "select clause"
		cross apply (select C1.CustomSum + Regs.NumCandidates as NewSumValue) as ca
		)
--You subtract 1 from CustomSum because the exact multiple of 100, must belong to the current 'bin'
select Rn, Year, Quarter, SubjCode, NumCandidates, CustomSum, (CustomSum - 1) / 100 as binNumber 
into #bridge
from C1
option (maxrecursion 0);

--select * from #bridge

--Set the results
insert into Sessions
select Year, Quarter, binNumber, 100 - SUM(NumCandidates)
from #bridge
group by Year, Quarter, binNumber

update r
set SessionNo = b.binNumber
from Registrations r
inner join #bridge b on r.Year = b.Year and r.Quarter = b.Quarter and r.SubjCode = b.SubjCode

drop table #regs;
drop table #bridge;

set @milliseconds = DATEDIFF(MILLISECOND, @dtstart, GETDATE())

raiserror ('Execution time (ms): %i', 10, 1, @milliseconds) with nowait;
go


-- Show results, for efficiency comparison
WITH PerYearQuarter AS
 (SELECT   Year, Quarter,
           COUNT(*) AS NumSessions,
           SUM(SpaceLeft) AS SeatsEmpty
  FROM     dbo.Sessions
  GROUP BY Year, Quarter)
SELECT     COALESCE (CAST(Quarter AS varchar(3)), 'ALL') AS Quarter,
           SUM(NumSessions) AS NumSessions,
           SUM(NumSessions * 100.0 - SeatsEmpty) / SUM(NumSessions) AS AvgSessionSize,
           AVG(SeatsEmpty * 1.0) AS AvgEmptySeats
FROM       PerYearQuarter
GROUP BY   Quarter WITH ROLLUP
ORDER BY   GROUPING(Quarter), Quarter;
go