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
Summary AS
( SELECT
ROW_NUMBER() OVER(ORDER BY InvoiceNumber)AS Row
, BatchNumber
, InvoiceNumber
, COUNT(*) AS Cnt
FROM @t t
GROUP BY BatchNumber ,InvoiceNumber
),
Sets AS(
SELECT
Row
, InvoiceNumber
, BatchNumber
, 1 AS [Set]
, Cnt AS SetRunningCount
FROM Summary t
WHERE Row in(SELECT MIN(Row) FROM Summary GROUP BY BatchNumber)
UNION ALL
SELECT
t.Row
, t.InvoiceNumber
, t.BatchNumber
, CASE
WHEN Sets.SetRunningCount+t.Cnt <= 10 THEN
Sets.[Set]
ELSE
Sets.[Set]+1
END AS [Set]
, CASE
WHEN Sets.BatchNumber=t.BatchNumber
AND Sets.SetRunningCount+t.Cnt <= 10 THEN
Sets.SetRunningCount+t.Cnt
ELSE
t.Cnt
END AS SetRunningCount
FROM
Summary t
JOIN Sets ON Sets.BatchNumber =t.BatchNumber
AND t.row = Sets.Row+1
)
SELECT
InvID
, t.BatchNumber
, t.InvoiceNumber
, VisitDate
, Amount
, [Set]
FROM
@t t
JOIN Sets ON Sets.BatchNumber = t.BatchNumber
AND Sets.InvoiceNumber = t.InvoiceNumber
ORDER BY InvID
