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

/* ASSUME: that the COUNT for this query is <= 10:

SELECT InvoiceNumber, COUNT(*) FROM @t GROUP BY InvoiceNumber

*/

CREATE TABLE #CntByBatchInvoice (

BatchNumber INT,

InvoiceNumber INT,

Cnt int,

SetNo int

)

; WITH BaseData AS (

SELECT

InvID,

BatchNumber,

InvoiceNumber,

VisitDate,

Amount

FROM @t

)

,CntByInv AS (

SELECT

"Set" = ROW_NUMBER() OVER (ORDER BY BatchNumber, InvoiceNumber),

BatchNumber,

InvoiceNumber,

Cnt = COUNT(*)

FROM BaseData

GROUP BY BatchNumber, InvoiceNumber

)

INSERT INTO #CntByBatchInvoice (BatchNumber, InvoiceNumber, Cnt)

SELECT BatchNumber, InvoiceNumber, Cnt FROM CntByInv

DECLARE GroupSets_Cursor CURSOR FOR

SELECT BatchNumber, InvoiceNumber, Cnt

FROM #CntByBatchInvoice

ORDER BY BatchNumber, InvoiceNumber

OPEN GroupSets_Cursor

DECLARE @BatchNumber int

DECLARE @InvoiceNumber int

DECLARE @Cnt int

FETCH NEXT FROM GroupSets_Cursor INTO @BatchNumber, @InvoiceNumber, @Cnt

DECLARE @LastBatchNumber int; SET @LastBatchNumber = @BatchNumber

DECLARE @RunCnt int; SET @RunCnt = 0

DECLARE @SetNo int; SET @SetNo = 1

WHILE @@FETCH_STATUS = 0

BEGIN

IF @LastBatchNumber <> @BatchNumber

SET @SetNo = 0

IF @LastBatchNumber <> @BatchNumber

OR @RunCnt + @Cnt > 10

BEGIN

SET @SetNo = @SetNo + 1

SET @RunCnt = @Cnt

END

ELSE

SET @RunCnt = @RunCnt + @Cnt

UPDATE #CntByBatchInvoice

SET SetNo = @SetNo

WHERE BatchNumber = @BatchNumber

AND InvoiceNumber = @InvoiceNumber

FETCH NEXT FROM GroupSets_Cursor INTO @BatchNumber, @InvoiceNumber, @Cnt

END

CLOSE GroupSets_Cursor

DEALLOCATE GroupSets_Cursor

SELECT

T.InvID,

T.BatchNumber,

T.InvoiceNumber,

T.VisitDate,

T.Amount,

"Set" = G.SetNo

FROM @t T

JOIN #CntByBatchInvoice G

ON G.BatchNumber = T.BatchNumber

AND G.InvoiceNumber = T.InvoiceNumber

ORDER BY T.BatchNumber, T.InvoiceNumber

DROP TABLE #CntByBatchInvoice