DECLARE @t TABLE
(
[InvID] [int] IDENTITY(1,1),
[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 between 1 and 10 batchnumber/invoicenumber rows
/* This solution creates 2 CTEs
query13_mod (batchnumber,invoicenumber,invoice_count,batch_seq)
- returns one row per batchnumber/invoicenumber.
- invoice_count = batchnumber/invoice number/visitdate date rows
- batch_seq = sequence number. Used to join batchnumber/invoice with succeeding row in recursvie CTE.
query13_CTE (batchnumber,invoicenumber,invoice_count,batch_seq,batch_count,batch_set_id)
returns one row per batchnumber/invoicenumber.
- batch_count = cumulative number of rows per batchnumber/batch_set_id
*/
with query13_mod as
(select batchnumber, invoicenumber,
count(*) invoice_count,
rank() over (partition by batchnumber order by invoicenumber) as batch_seq
from @t
group by batchnumber, invoicenumber),
query13_cte as
(-- select first invoice for each batchnumber
select *, invoice_count as batch_count, 1 as batch_set_id
from query13_mod
where batch_seq = 1
union all
-- recursive query. compares successive batchnumber/invoice summary rows and calculates running batch_count, batch_set_id
select qm.*,
-- calculate cumulative invoice/visit date count
(case when qt.batch_count + qm.invoice_count <= 10 --will this next invoice/visit date count put us over the limit?
then qt.batch_count + qm.invoice_count -- accumlate batch_count
else qm.invoice_count -- over limit of 10. reset batch_count
end) as batch_count,
-- calculate batch_set_id
(case when (qt.batch_count + qm.invoice_count) <= 10 --will this next invoice/visit date count put us over the limit?
then qt.batch_set_id -- batch_set_id
else qt.batch_set_id + 1 -- over limit of 10. Increment batch_set_id
end) as batch_set_id
from query13_mod qm
join query13_cte qt
on qt.batch_seq + 1 = qm.batch_seq
and qt.batchnumber = qm.batchnumber)
-- join query13_CTE with base data to select batch_set_id for each detail row
select t.*, qt.batch_set_id
from query13_cte qt
join @t t
on qt.batchnumber = t.batchnumber
and qt.invoicenumber = t.invoicenumber
order by 1, 2, 3, 4
option (maxrecursion 0)
