May 20, 2008 at 8:49 am
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
May 20, 2008 at 9:08 am
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?
May 20, 2008 at 11:07 am
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
May 20, 2008 at 11:59 am
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
May 20, 2008 at 12:11 pm
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.
[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....
----------------------------------------------------------------------------------
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?
May 21, 2008 at 8:13 pm
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
Change is inevitable... Change for the better is not.
May 21, 2008 at 8:44 pm
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?
September 12, 2013 at 7:37 am
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.
September 12, 2013 at 7:18 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 14, 2015 at 5:23 pm
If I wanted to change the days to 30 instead of 15 (say Current, 1-30, 31-60, 61-90, 90+) would I change the math to '....*30/30' ? Also Im not sure I follow the logic on some of the SProc you created, does this also place payments in the correct place? Like for instance, customer has five invoices and makes a payment, that payment should be applied to invoice one, left over gets applied to the remaining invoices. Could you outline from your Proc where this takes place? 😉
July 15, 2015 at 3:24 pm
The provided code works great the first time, but won't run a second time. I'm getting the error: "Cannot drop the table xxx because it does not exist or you do not have permission." I'm not sure why this is happening, since I was the one that just created the table. Any ideas?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply