All right then - we do it the "hard" way....:)

This has a few moving parts, and is based on the assumption that your tracking your invoices as POSITIVE and your payments are NEGATIVE.

[font="Courier New"]DROP TABLE #cctemp

DROP TABLE #ccbal

DROP TABLE #CCTEMP_original

DROP TABLE #ddtemp

--first some test data

CREATE TABLE #CCTEMP_original(memberID INT, cashflow money, postdate DATETIME)

INSERT #CCTEMP_original

   SELECT TOP 1000000

       CAST(RAND(checksum(NEWID()))*50+1 AS INT),

       CAST(RAND(checksum(NEWID()))*500-250 AS money),

       DATEADD(dd,36524+365,CAST(RAND(checksum(NEWID()))*2500 AS INT))

   FROM sys.all_columns sc1, sys.all_columns sc2

CREATE CLUSTERED INDEX uci_cctempO ON #CCTEMP_original(memberID,postdate)

--start cleanup process

DECLARE @g DATETIME --just to know how fast this is

SET @g=GETDATE();

--first - a helper table:

SELECT memberID,

   SUM(CASE WHEN cashflow>0 THEN cashflow ELSE 0 END) AS debit,

   ABS(SUM(CASE WHEN cashflow>0 THEN 0 ELSE cashflow END)) AS credit,

   SUM(cashflow) AS bal

INTO #ccbal

FROM #CCTEMP_original

GROUP BY memberID

CREATE UNIQUE CLUSTERED INDEX uci_ccb ON #ccbal(memberID)

--move in only those items that have more debits than credits

--if you have a credit balance you don't belong on an aging report

--we only care about the debits

SELECT *, CAST(0 AS money) AS runbal

   INTO #cctemp

FROM #CCTEMP_original

   WHERE EXISTS (SELECT NULL FROM #ccbal WHERE #ccbal.memberid=#CCTEMP_original.memberID AND bal>0)

   AND cashflow>0

CREATE CLUSTERED INDEX uci_cct ON #cctemp(memberid, postdate)

--create a running grouped total of the debits:

DECLARE @prevMemberID INT

   SET @prevMemberID=-1;

DECLARE @runbal money

   SET @runbal =0;

DECLARE @dummy money

   SET @dummy=0;

UPDATE #CCTEMP

SET @runbal=runbal=CASE WHEN @prevmemberID=memberid THEN @runbal ELSE 0 END + cashflow,

   @dummy=@runbal,

   @prevmemberID=memberID

FROM #CCTEMP WITH (INDEX(uci_cct),tablockX)

--get rid of all of the items where the running total is less than the total debits

--those we be all of the debits that have been "paid off"

DELETE #cctemp

FROM #cctemp

       INNER JOIN #ccbal ON #cctemp.memberID=#ccbal.memberID

WHERE runbal<credit

--update the cashflow of the first row by group to account for any remaining credit

;WITH balCTE AS

   (

       SELECT  #cctemp.memberid,

               Row_Number() OVER (partition BY #cctemp.memberID ORDER BY #cctemp.memberID,runbal) RN,

               cashflow,

               runbal,

               credit

       FROM #cctemp

               INNER JOIN #ccbal ON #cctemp.memberiD=#ccbal.memberID)

UPDATE balCTE

   SET cashflow=runbal-credit

WHERE RN=1

--create the aging data

SELECT MemberID, SUM(CashFlow) CashFlow, DATEDIFF(DAY,postDate + 1,GETDATE())/15*15 DaysAged

INTO #ddtemp

FROM #cctemp

GROUP BY MemberID, DATEDIFF(DAY,postDate + 1,GETDATE())/15*15  ORDER BY MemberID

--show me the magic

SELECT * FROM #DDtemp

ORDER BY memberID, daysaged DESC

--how fast were we?

SELECT DATEDIFF(ms,@g,GETDATE())

--play these if you want validation we did it right

--select sum(cashflow) from #CCTEMP_original

--select sum(bal) from #CCbal

--select sum(case when bal>0 then bal else 0 end) from #CCbal

--select sum(cashflow) from #cctemp[/font]

I'm thinking this should do the trick.

considering how much we're doing - 4.4 seconds doesn't seem so bad on the million row test....