Home

TSQL Challenge 13 - Solution by Adan Bucio


DECLARE @t TABLE(
    InvID INT IDENTITY,
    BatchNumber INT,
    InvoiceNumber INT,
    VisitDate DATETIME,
    Amount MONEY )
INSERT INTO @t(BatchNumber, InvoiceNumber, VisitDate, Amount)
SELECT 10000001,20001,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20001,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20001,'2009-01-03',50.00 UNION ALL
SELECT 10000001,20001,'2009-01-04',50.00 UNION ALL
SELECT 10000001,20002,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20002,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20002,'2009-01-03',50.00 UNION ALL
SELECT 10000001,20003,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20003,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-03',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-04',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-05',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-06',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-13',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-14',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-15',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-06',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-07',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-08',50.00 UNION ALL
SELECT 10000001,20006,'2009-01-01',50.00 UNION ALL
SELECT 10000002,20007,'2009-01-01',50.00 UNION ALL
SELECT 10000002,20007,'2009-01-02',50.00 UNION ALL
SELECT 10000002,20007,'2009-01-03',50.00 UNION ALL
SELECT 10000002,20008,'2009-01-01',50.00 UNION ALL
SELECT 10000002,20008,'2009-01-02',50.00 UNION ALL
SELECT 10000002,20008,'2009-01-03',50.00;

WITH RowsByInvoice AS(
	SELECT		ROW_NUMBER() OVER(PARTITION BY BatchNumber ORDER BY InvoiceNumber) AS rn,
				BatchNumber,
				InvoiceNumber,
				COUNT(*) AS [RowCount]
	FROM		@t
	GROUP BY	BatchNumber, InvoiceNumber
),
--	Recursively agregate RowCount resetting the sum when it 
--	overflows ten. When this happens we increment the value
--	of Set column. 
Sets AS (
	SELECT		rn, BatchNumber, InvoiceNumber, [RowCount], 1 AS [Set]
	FROM		RowsByInvoice
	WHERE		rn = 1
	UNION ALL
	SELECT		r.rn, r.BatchNumber, r.InvoiceNumber,
				CASE
					WHEN r.[RowCount] + s.[RowCount] > 10 THEN r.[RowCount]
					ELSE r.[RowCount] + s.[RowCount]
				END,
				CASE
					WHEN r.[RowCount] + s.[RowCount] > 10 THEN s.[Set] + 1
					ELSE s.[Set]
				END
	FROM		RowsByInvoice AS r
				INNER JOIN	Sets AS s
							ON r.rn = s.rn + 1
							AND r.BatchNumber = s.BatchNumber
)
SELECT		t.InvID,
			t.BatchNumber,
			t.InvoiceNumber,
			t.VisitDate,
			t.Amount,
			s.[Set]
FROM		@t AS t
			INNER JOIN	Sets AS s
						ON	t.BatchNumber	= s.BatchNumber
						AND	t.InvoiceNumber	= s.InvoiceNumber
ORDER BY	t.InvID;