How to select and export 5,000 lines of debit and credit transactions at a time and have the debits and credits balance to zero?

Question

The ERP system we're migrating to requires csv files with 5,000 or less rows for the GL. The debit and credit transactions within each file must balance to zero. There are multiple debit and credit transaction rows that share a common transaction ID.

Using offset and fetch next I've been able to extract 5000 rows at a time, however the credits and debits do not balance.

Data Example:

TranID  Credit  Debit   Balance Account#
1       250     0       250     ABC
1       0       250     0       DEF
2       0       100     -100    GHI
2       50      0       -50     JKL
2       50      0       0       MNO


declare @batchsize INT = 5000,
    @loopcount INT = 0;

while (@loopcount*@batchsize < (select  count(*) from [Apps2].[dbo].[GLTrans]))
begin
  SELECT *  FROM [Apps2].[dbo].[GLTrans]
  ORDER BY tranID
  offset (@batchsize * @loopcount) rows
  fetch next (@batchsize) rows only

  set @loopcount= @loopcount + 1
end

Answer 1

A simple solution is pre-process all the transactions and assign a batch no (for each CSV files). The temp table stored the number of lines per TranID.

It is assumed that the Debit & Credit will balance for each TranID.

After that you can generate the CSV based on the temp table.

-- create the temp table
create table #trans
(
    TranID      int identity,
    Cnt         int,
    Batch       int
)

-- populate the temp table
insert into #trans (TranID, Cnt)
select TranID, Cnt = count(*)
from   [Apps2].[dbo].[GLTrans]
group by TranID

declare @batchsize  int = 5000,
        @batch      int = 1

while   exists (select * from #trans where Batch is null)
begin
    update  t
    set     Batch   = @batch 
    from
    (
        select  *, cumm = sum(Cnt) over (order by TranID)
        from    #trans
        where   Batch   is null
    ) t
    where   cumm    <= @batchsize 

    select  @batch = @batch + 1
end

--  Verify
select  *, sum(Cnt) over (partition by Batch order by TranID)
from    #trans 
order by TranID