By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @Attendees TABLE (ID INT, Activity INT, Day VARCHAR(10), GroupID INT);
INSERT @Attendees (ID, Activity, Day)
VALUES
(1, 1, 'Mon'),
(2, 1, 'Mon'),
(3, 1, 'Tue'),
(4, 1, 'Mon'),
(5, 2, 'Mon'),
(6, 2, 'Tue'),
(7, 2, 'Mon'),
(8, 2, 'Mon'),
(9, 2, 'Mon'),
(10, 2, 'Mon');
DECLARE @Leaders TABLE (ID INT, Activity INT);
INSERT @Leaders (ID, Activity)
VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 2),
(6, 2);
DECLARE @Capacity INT = 2;
DECLARE @Day VARCHAR(10) = 'Mon';
WITH CTE_Attendees AS (
SELECT *
FROM @Attendees
WHERE Day = @Day
),
CTE_ClassesNeeded AS (
SELECT Activity, ClassesNeeded = (COUNT(*) - 1) / @Capacity + 1
FROM CTE_Attendees
GROUP BY Activity
| ID | Activity | Day | AssignedGroupID |
|---|---|---|---|
| 1 | 1 | Mon | 1 |
| 2 | 1 | Mon | 1 |
| 4 | 1 | Mon | 2 |
| 5 | 2 | Mon | 4 |
| 7 | 2 | Mon | 4 |
| 8 | 2 | Mon | 5 |
| 9 | 2 | Mon | 5 |
| 10 | 2 | Mon | 6 |