Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

True Aging Report Expand / Collapse
Author
Message
Posted Tuesday, May 20, 2008 8:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2008 2:25 PM
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
Post #503770
Posted Tuesday, May 20, 2008 9:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 7,120, Visits: 15,014
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?
Post #503797
Posted Tuesday, May 20, 2008 11:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2008 2:25 PM
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
Post #503894
Posted Tuesday, May 20, 2008 11:59 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #503932
Posted Tuesday, May 20, 2008 12:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 7,120, Visits: 15,014
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....:)

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?
Post #503946
Posted Wednesday, May 21, 2008 8:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:53 PM
Points: 36,769, Visits: 31,225
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #504946
Posted Wednesday, May 21, 2008 8:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 7,120, Visits: 15,014
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...:) Well - the only "sane" way. There's always the "dual-ledger, dual updatable cursor" method....:D 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?
Post #504955
Posted Thursday, September 12, 2013 7:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 19, 2013 2:52 AM
Points: 2, Visits: 26
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.
Post #1494176
Posted Thursday, September 12, 2013 7:18 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:04 AM
Points: 3,614, Visits: 5,228
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!
Post #1494418
Posted Friday, January 10, 2014 6:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 7, 2014 4:19 PM
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
Post #1530007
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse