Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


True Aging Report


True Aging Report

Author
Message
abinder-682132
abinder-682132
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 30
I have worked with SQL for many, many years and have never had the need to ask a question that I hadn’t been able to answer myself after some or a lot of research. Now I do. I have researched this problem for more than several days and have only come up with a partial solution. Here is my situation.

I have a table (#cctemp) with three columns: MemberID, CashFlow (represents either a charge (+) or a payment (-)) and Date (when charge or payment occurred). I need to present the data in the form of an Aging Report Table where the groups are: Total, 0to15Days, 16to30Days, 31to45Days ... Over120Days. I have been successful in forming groups using the following SQL Statement:

select MemberID, sum(CashFlow) CashFlow, ((dateadd(day, datediff(day,getdate(),Date + 1)/15*15,getdate()))) DaysAged into #ddtemp
from #cctemp GROUP BY MemberID, (dateadd(day, datediff(day,getdate(),Date + 1)/15*15,getdate())) order by MemberID

This gives me a summary table of CashFlows grouped into 15 day periods and indicated by the Date column equal to: 15, 30, 45, etc. Then it is a simple matter inserting the results into a new table where the Columns are: MemberID, Total, 0to15, 16to30, etc.

It sounds like this is the solution but it isn't. The trouble is that I get the correct groupings showing net CasFlow for each group but the way a "TRUE" aging report works is that the payments (credits) are applied to the oldest receivable (charge) first and whatever is left over is applied to the second oldest, etc. This is the crux of the problem.

I can relatively easily do something like this in C# or VB but I need to do this in SQL Server and in a table. Can anyone help? (By the way, I think the grouping algorithm may be usefull to people that need to group by arbitrary time periods.)

Thanks!

abinder
abinder@hps.md
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7661 Visits: 18090
Sounds to me that you already have your answer. You've essentially determined your own issue: you need to accurate derive/store the effective date of the credits/payments instead of the POSTING date. By trying to run aging against the actual date posted - you will never get the result you want.

Do you have a "PaymentsApplied" (or CreditsApplied) table? Because it sounds to me that you'd need one to do this accurately and efficiently. This would "map" the payment into the various invoice(s) it is being used to pay off/reduce as might be the case.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
abinder-682132
abinder-682132
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 30
Matt,
That's one of the problems. I don't have a Payments(Credits)Applied table. I have only what I stated in my original posting a series of Debits and Credits and their respective dates. (There is, of course, more info but none that applies here.)

Alfons
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
The simplest solution would be to build the CreditsApplied table and use that. Is that possible?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7661 Visits: 18090
abinder (5/20/2008)
Matt,
That's one of the problems. I don't have a Payments(Credits)Applied table. I have only what I stated in my original posting a series of Debits and Credits and their respective dates. (There is, of course, more info but none that applies here.)

Alfons


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

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

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





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....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45329 Visits: 39936
Heh... I started reading this post and then saw your first reply... I knew you'd use the running balance trick... it just makes sense, here. Nicely done!

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7661 Visits: 18090
Jeff Moden (5/21/2008)
Heh... I started reading this post and then saw your first reply... I knew you'd use the running balance trick... it just makes sense, here. Nicely done!


Thanks - it's also the only other way I could think of doing it...Smile Well - the only "sane" way. There's always the "dual-ledger, dual updatable cursor" method....BigGrin It would probably still be running for the first time on a 1M row test...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
aslam.jeewa
aslam.jeewa
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 33
Thank you the most amazing, incredible SQL query. I cannot begin to describe how much this query assisted me. I have one request though please.

I now need to run a query that outputs payments against age bucket.

eg. MemberID, Full payment, 0days Payment, 30days Payment etc. etc.....

Can you modify so that the query produces the aged report & we then run perhaps another SQL to show how each payment was "allocated".

Thanking you kindly.
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4279 Visits: 6431
aslam.jeewa (9/12/2013)
Thank you the most amazing, incredible SQL query. I cannot begin to describe how much this query assisted me. I have one request though please.

I now need to run a query that outputs payments against age bucket.

eg. MemberID, Full payment, 0days Payment, 30days Payment etc. etc.....

Can you modify so that the query produces the aged report & we then run perhaps another SQL to show how each payment was "allocated".

Thanking you kindly.


You'd probably do better starting your own thread with DDL, consumable sample data and expected results, rather than reviving a 5 year old thread. If you post it in the right forum (SQL 2005, 2008, 20012) you might even get a better solution for your platform based on newer available syntax.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
petereg33
petereg33
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 5
GSquared (5/20/2008)
The simplest solution would be to build thedandp qualities of a leader D&P DNP android programming language CreditsApplied table and use that. Is that possible?

that's great thx

customer service skills list hr source consulting human resources internships project proposal template superior staffing
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search