Figuring out NSF payments
-
I still want to believe that there must be a method to do this with less JOINs. But you're correct, that without them the ROW_NUMBERs just don't seem to compute as needed.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]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 -
You were looking for a recursive solution, but what about something with temps and row numbers, like:
IF OBJECT_ID('TEMPDB..#pmt','u') IS NOT NULL DROP TABLE #pmt;
IF OBJECT_ID('TEMPDB..#nsf','u') IS NOT NULL DROP TABLE #nsf;
--put all payments into temp with row num
select PmtNum=Row_Number() over (partition by acct_num, at_pmt order by ts_pmt)
, *
INTO #pmt
from #PAYMENTS
where ID_CASH_TRAN = 5000 --get all payments in temp in order by date
--put all nsfs into temp with row num
select NSFNum=Row_Number() over (partition by acct_num, at_pmt order by ts_pmt)
, *
INTO #nsf --drop table #nsf
from #PAYMENTS
where ID_CASH_TRAN = 5008
--match them up
select
AccountNumber=p.Acct_num
,PaymentTranAcct=p.id_cash_tran
,PaymentDate=p.TS_pmt
,PaymentAmount=p.AT_PMT
,PaymentSource=p.cd_pmt_src
,NSFTranAcct=n.id_cash_tran
,NSFDate=n.TS_pmt
,NSFAmount=n.AT_PMT
,NSFSource=n.cd_pmt_src
from #pmt p
left outer join #nsf n
on n.acct_num = p.acct_num
and abs(n.at_pmt) = abs(p.at_pmt)
and n.NSFNum = p.PmtNum
--where p.ACCT_NUM = 37172806
Produces results like:
AccountNumber PaymentTranAcct PaymentDate PaymentAmount PaymentSource NSFTranAcct NSFDate NSFAmount NSFSource
37172806 5000 2011-09-28 20:21:43.970485 -8664.02 TP NULL NULL NULL NULL
37172806 5000 2011-01-13 20:22:21.510461 -6000.00 TP NULL NULL NULL NULL
37172806 5000 2010-12-23 20:21:44.823681 -3236.81 TP 5008 2011-01-11 20:23:32.780891 3236.81 TP
37172806 5000 2011-04-21 20:22:00.920541 -2055.60 TP NULL NULL NULL NULL
37172806 5000 2011-04-21 20:22:01.081300 -800.00 CC NULL NULL NULL NULL
37172806 5000 2010-12-23 20:21:44.728266 -500.00 CW 5008 2010-12-28 18:50:45.769860 500.00 GC
37172806 5000 2010-12-23 20:21:44.809691 -500.00 CW 5008 2011-01-04 18:18:20.206875 500.00 GC
37172806 5000 2010-12-23 20:21:44.816891 -500.00 CW 5008 2011-01-04 18:18:20.990343 500.00 GC
93299972 5000 2011-12-12 20:22:35.304907 -1000.00 TP NULL NULL NULL NULL
93299972 5000 2011-11-04 20:24:28.590740 -500.00 CW 5008 2011-11-09 18:12:13.357006 500.00 GC
93299972 5000 2011-11-04 20:24:28.872289 -500.00 CW 5008 2011-11-09 18:12:13.843398 500.00 GC
93299972 5000 2011-12-01 20:22:07.649355 -500.00 CW 5008 2011-12-05 18:16:01.951365 500.00 GC
93299972 5000 2011-12-01 20:22:07.818174 -500.00 CW 5008 2011-12-05 18:16:02.528679 500.00 GC
93299972 5000 2011-09-22 20:22:23.081475 -250.00 CW 5008 2011-09-26 18:14:24.812516 250.00 GC
93299972 5000 2011-05-19 20:22:05.343900 -240.00 TP NULL NULL NULL NULL
93299972 5000 2011-07-21 20:23:18.970942 -200.00 TP NULL NULL NULL NULL
93299972 5000 2011-06-15 20:22:44.308069 -150.00 TP NULL NULL NULL NULL
93299972 5000 2011-11-04 20:24:28.886868 -142.00 CW 5008 2011-11-09 18:12:13.884036 142.00 GC
114739790 5000 2011-05-16 20:23:06.653234 -1300.00 TP NULL NULL NULL NULL
114739790 5000 2011-09-16 20:22:44.701845 -607.74 TP NULL NULL NULL NULL
114739790 5000 2011-04-13 20:22:27.659510 -500.00 CW 5008 2011-04-20 18:16:57.222716 500.00 GC
114739790 5000 2011-04-13 20:22:27.885458 -500.00 CW 5008 2011-04-20 18:16:57.795456 500.00 GC
114739790 5000 2011-05-17 20:23:11.812372 -462.00 TP NULL NULL NULL NULL
114739790 5000 2011-04-11 20:22:56.769630 -379.25 CW 5008 2011-04-15 18:14:55.295335 379.25 GC
114739790 5000 2011-02-28 20:22:45.120888 -275.00 CW NULL NULL NULL NULL
114739790 5000 2010-11-05 20:24:46.697936 -240.00 TP NULL NULL NULL NULL
114739790 5000 2011-11-23 18:13:04.550969 -200.45 MB NULL NULL NULL NULL
114739790 5000 2010-12-29 20:22:02.069491 -200.00 CW NULL NULL NULL NULL
114739790 5000 2011-08-08 20:22:10.591635 -200.00 TP NULL NULL NULL NULL
114739790 5000 2011-12-27 18:09:46.129389 -200.00 MB NULL NULL NULL NULL
114739790 5000 2011-07-13 18:17:19.482826 -100.00 MB NULL NULL NULL NULL
232412320 5000 2011-11-01 13:53:40.504517 -1600.00 TP NULL NULL NULL NULL
232412320 5000 2010-12-07 20:27:39.600371 -600.00 CW 5008 2011-11-02 18:18:20.336650 600.00 GC
232412320 5000 2011-10-24 20:23:16.712385 -600.00 CW 5008 2011-11-02 18:18:21.192792 600.00 GC
232412320 5000 2011-10-24 20:23:16.809497 -600.00 CW NULL NULL NULL NULL
232412320 5000 2011-04-05 20:32:27.020345 -500.00 CW NULL NULL NULL NULL
232412320 5000 2011-06-30 20:22:42.962455 -475.00 CW NULL NULL NULL NULL
232412320 5000 2011-10-24 20:23:16.818424 -381.00 CW 5008 2011-11-02 18:18:21.295221 381.00 GC
232412320 5000 2011-08-12 20:23:25.312317 -200.00 CW NULL NULL NULL NULL
232412320 5000 2011-04-05 20:32:27.098455 -165.99 CW NULL NULL NULL NULL
232412320 5000 2010-12-07 20:27:40.159718 -144.00 CW NULL NULL NULL NULL
692809411 5000 2011-08-17 18:26:54.614314 -465.71 GC NULL NULL NULL NULL
692809411 5000 2011-04-21 18:21:22.946513 -363.55 GC NULL NULL NULL NULL
692809411 5000 2010-12-17 18:22:02.569107 -190.00 CF 5008 2010-12-28 19:01:05.482708 190.00 CF
692809411 5000 2010-12-20 18:20:41.767741 -190.00 CF 5008 2011-03-21 18:27:31.321997 190.00 CF
692809411 5000 2011-03-11 18:26:00.625220 -190.00 CF 5008 2011-07-12 18:33:10.543816 190.00 CF
692809411 5000 2011-07-01 18:24:40.453690 -190.00 CF 5008 2011-08-08 18:27:35.606194 190.00 CF
692809411 5000 2011-07-29 18:29:23.825201 -190.00 CF NULL NULL NULL NULL
692809411 5000 2011-08-26 18:20:30.396055 -190.00 CF NULL NULL NULL NULL
692809411 5000 2011-01-14 18:28:14.524934 -100.00 CF NULL NULL NULL NULL
692809411 5000 2011-04-29 18:23:05.421140 -100.00 CF NULL NULL NULL NULL
692809411 5000 2011-04-08 20:29:22.183643 -70.00 TP NULL NULL NULL NULL
692809411 5000 2011-06-03 18:29:28.756537 -60.00 CF NULL NULL NULL NULL
692809411 5000 2011-01-28 18:24:08.262612 -20.00 CF NULL NULL NULL NULL
Maybe the windowing functions are too slow on a large set?
Interesting problem!
[font="Courier New"]Looking for a Deadlock Victim Support Group..[/font] -
Recursive solution (updated):
-- Important index (could also cluster on ID_CASH_TRAN, ACCT_NUM, TS_PMT)
CREATE UNIQUE INDEX
[UQ dbo.PAYMENTS ID_CASH_TRAN, ACCT_NUM, TS_PMT (AT_PMT, CD_PMT_SRC)]
ON dbo.PAYMENTS
(ID_CASH_TRAN, ACCT_NUM, TS_PMT)
INCLUDE (AT_PMT, CD_PMT_SRC);
The logic is fairly simple (though the code is relatively verbose). It starts off finding the first NSF per account, and the PMT that goes with it. In then recursively finds the next NSF in sequence, and the associated PMT (in the same time order).
WITH LastNSF AS
(
-- Last NSF per account
SELECT
nsf.ACCT_NUM,
nsf.AT_PMT,
nsf.TS_PMT,
nsf.CD_PMT_SRC
FROM dbo.PAYMENTS AS nsf
WHERE
nsf.ID_CASH_TRAN = '5008'
AND nsf.TS_PMT =
(
SELECT
MAX(nsf2.TS_PMT)
FROM dbo.PAYMENTS AS nsf2
WHERE
nsf2.ID_CASH_TRAN = nsf.ID_CASH_TRAN
AND nsf2.ACCT_NUM = nsf.ACCT_NUM
)
), rCTE AS
(
-- Anchor: last NSF and matching PMT per account
SELECT
pmt.ACCT_NUM,
pmt.TS_PMT,
pmt.AT_PMT,
pmt.CD_PMT_SRC,
TS_NSF = LastNSF.TS_PMT,
AT_NSF = LastNSF.AT_PMT,
CD_PMT_SRC_NSF = LastNSF.CD_PMT_SRC
FROM LastNSF
CROSS APPLY
(
-- PMT associated with the first NSF
SELECT TOP (1)
pmt.ACCT_NUM,
pmt.TS_PMT,
pmt.AT_PMT,
pmt.CD_PMT_SRC
FROM dbo.PAYMENTS AS pmt
WHERE
pmt.ID_CASH_TRAN = '5000'
AND pmt.ACCT_NUM = LastNSF.ACCT_NUM
AND pmt.AT_PMT = 0.0 - LastNSF.AT_PMT
AND LastNSF.TS_PMT BETWEEN pmt.TS_PMT AND DATEADD(DAY, 60, pmt.TS_PMT)
ORDER BY
pmt.TS_PMT DESC
) AS pmt
UNION ALL
-- Recusrive bit
SELECT
PMT.ACCT_NUM,
PMT.TS_PMT,
PMT.AT_PMT,
PMT.CD_PMT_SRC,
TS_NSF = PreviousNSF.TS_PMT,
AT_NSF = PreviousNSF.AT_PMT,
CD_PMT_SRC_NSF = PreviousNSF.CD_PMT_SRC
FROM
(
-- Previous NSF in sequence
SELECT * FROM
(
SELECT
PreviousNSF.ACCT_NUM,
AT_PMT = PreviousNSF.AT_PMT,
PreviousNSF.CD_PMT_SRC,
PreviousNSF.TS_PMT,
Recusrive_TS_PMT = rCTE.TS_PMT,
PNSF60 = DATEADD(DAY, -60, PreviousNSF.TS_PMT),
rn = ROW_NUMBER() OVER (ORDER BY PreviousNSF.TS_PMT DESC)
FROM dbo.PAYMENTS AS PreviousNSF
JOIN rCTE ON
rCTE.ACCT_NUM = PreviousNSF.ACCT_NUM
AND PreviousNSF.TS_PMT < rCTE.TS_NSF
WHERE
PreviousNSF.ID_CASH_TRAN = '5008'
) AS PreviousNSF
WHERE
PreviousNSF.rn = 1
) AS PreviousNSF
CROSS APPLY
(
-- PMT associated with the current NSF
SELECT *
FROM
(
SELECT
PMT.ACCT_NUM,
PMT.TS_PMT,
PMT.AT_PMT,
PMT.CD_PMT_SRC,
rn = ROW_NUMBER() OVER (ORDER BY PMT.TS_PMT DESC)
FROM dbo.PAYMENTS AS PMT
WHERE
PMT.ID_CASH_TRAN = '5000'
AND PMT.ACCT_NUM = PreviousNSF.ACCT_NUM
AND PMT.AT_PMT = 0.0 - PreviousNSF.AT_PMT
AND PMT.TS_PMT < PreviousNSF.Recusrive_TS_PMT
AND PMT.TS_PMT <= PreviousNSF.TS_PMT
AND PMT.TS_PMT >= PreviousNSF.PNSF60
) AS PMT
WHERE
PMT.rn = 1
) AS PMT
)
SELECT
ACCT_NUM,
TS_PMT,
AT_PMT,
CD_PMT_SRC,
TS_NSF,
AT_NSF,
CD_PMT_SRC_NSF
FROM rCTE
ORDER BY
ACCT_NUM,
TS_PMT
OPTION (MAXRECURSION 0);
ACCT_NUM TS_PMT AT_PMT CD_PMT_SRC TS_NSF AT_NSF CD_PMT_SRC_NSF
37172806 2010-12-23 20:21:44.728266 -500.00 CW 2010-12-28 18:50:45.769860 500.00 GC
37172806 2010-12-23 20:21:44.809691 -500.00 CW 2011-01-04 18:18:20.206875 500.00 GC
37172806 2010-12-23 20:21:44.816891 -500.00 CW 2011-01-04 18:18:20.990343 500.00 GC
37172806 2010-12-23 20:21:44.823681 -3236.81 TP 2011-01-11 20:23:32.780891 3236.81 TP
93299972 2011-09-22 20:22:23.081475 -250.00 CW 2011-09-26 18:14:24.812516 250.00 GC
93299972 2011-11-04 20:24:28.590740 -500.00 CW 2011-11-09 18:12:13.357006 500.00 GC
93299972 2011-11-04 20:24:28.872289 -500.00 CW 2011-11-09 18:12:13.843398 500.00 GC
93299972 2011-11-04 20:24:28.886868 -142.00 CW 2011-11-09 18:12:13.884036 142.00 GC
93299972 2011-12-01 20:22:07.649355 -500.00 CW 2011-12-05 18:16:01.951365 500.00 GC
93299972 2011-12-01 20:22:07.818174 -500.00 CW 2011-12-05 18:16:02.528679 500.00 GC
114739790 2011-04-11 20:22:56.769630 -379.25 CW 2011-04-15 18:14:55.295335 379.25 GC
114739790 2011-04-13 20:22:27.659510 -500.00 CW 2011-04-20 18:16:57.222716 500.00 GC
114739790 2011-04-13 20:22:27.885458 -500.00 CW 2011-04-20 18:16:57.795456 500.00 GC
232412320 2011-10-24 20:23:16.712385 -600.00 CW 2011-11-02 18:18:20.336650 600.00 GC
232412320 2011-10-24 20:23:16.809497 -600.00 CW 2011-11-02 18:18:21.192792 600.00 GC
232412320 2011-10-24 20:23:16.818424 -381.00 CW 2011-11-02 18:18:21.295221 381.00 GC
692809411 2010-12-20 18:20:41.767741 -190.00 CF 2010-12-28 19:01:05.482708 190.00 CF
692809411 2011-03-11 18:26:00.625220 -190.00 CF 2011-03-21 18:27:31.321997 190.00 CF
692809411 2011-07-01 18:24:40.453690 -190.00 CF 2011-07-12 18:33:10.543816 190.00 CF
692809411 2011-07-29 18:29:23.825201 -190.00 CF 2011-08-08 18:27:35.606194 190.00 CF
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Thank you so much Paul for investigating. I ran your solution against a table with around 800,000 rows and it ran in around 4 seconds, where my solution took around 10. The one problem I see is that my result set included 60,114 rows where yours returned 59,252, so I'm not sure where the differences are at this point. Ultimately I need to have the timestamps of both the payment and the NSF in the result set so I can compare, and also to be able to use it as a mapping table for other thing. It will take me some time to digest how your solution works, as there are some elements there I have not used before, mainly INCLUDE in the index and CROSS APPLY. Thank you again for your help. The below is what my code returns, and is what I need. I am trying to get the timestamp of the NSF included in your output, but I keep messing it up 🙂
ACCT_NUM TS_PMT AT_PMT CD_PMT_SRC TS_NSF AT_NSF CD_PMT_SRC
37172806 2010-12-23 20:21:44.728266 -500.00 CW 2010-12-28 18:50:45.769860 500.00 GC
37172806 2010-12-23 20:21:44.809691 -500.00 CW 2011-01-04 18:18:20.206875 500.00 GC
37172806 2010-12-23 20:21:44.816891 -500.00 CW 2011-01-04 18:18:20.990343 500.00 GC
37172806 2010-12-23 20:21:44.823681 -3236.81 TP 2011-01-11 20:23:32.780891 3236.81 TP
93299972 2011-09-22 20:22:23.081475 -250.00 CW 2011-09-26 18:14:24.812516 250.00 GC
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less. -
Greg Snidow (5/31/2012)
I am trying to get the timestamp of the NSF included in your output, but I keep messing it up 🙂I omitted that column from the final SELECT by mistake! All the machinery is there in the complicated bit to make it work except that, so:
SELECT
ACCT_NUM,
TS_PMT,
AT_PMT,
CD_PMT_SRC,
TS_NSF, -- Add this
AT_NSF,
CD_PMT_SRC_NSF
FROM rCTE
ORDER BY
ACCT_NUM,
TS_PMT
OPTION (MAXRECURSION 0);
INCLUDE just includes a column at the leaf of the index without making it part of the key (so it's no good for searching, but it's there at the leaf when you've found the data you want). As far as APPLY is concerned: http://qa.sqlservercentral.com/articles/APPLY/69953/ and http://qa.sqlservercentral.com/articles/APPLY/69954/
Also added the CD_PMT_SRC_NSF column.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Thank you Paul. I was actually in the process of letting you know what a dunce I am for not trying that first. DOH!
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less. -
Greetings Greg, glad to see you're making progress on your issue. It's far above my mediocre scripting skills, otherwise it looks like a juicy one to sink teeth into.
I'm only here during an idle lunchtime to point out a opportunity to improve your autosignature; YOUR glass might well be at 50% capacity, but given that the transport by humans of most unclosed liquid receptacles is not undertaken with those receptacles at more than 98-99% of total capacity by anyone other than the outright reckless who don't care much about having to then wipe their hands, or creating spillages on the floor, it would be more apt to say,
"...the glass is at 60% of operational capacity".
🙂
-
I don't get it Paul. I took 10 of the accounts that had records in my result set and not yours, and ran both of our code on only those accounts, and the results were identical.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less. -
Greg Snidow (5/31/2012)
Thank you Paul. I was actually in the process of letting you know what a dunce I am for not trying that first. DOH!Not at all, it was my error. As far as understanding the code is concerned, well I'm sorry about the code to be honest, but it's as simple as I can make it.
It might help a bit if I say that APPLY is just a correlated join (LATERAL JOIN in ANSI SQL), and the ROW_NUMBER ... WHERE rn = 1 business is just because TOP (1) isn't allowed in the recursive part of a CTE. Using the ROW_NUMBER trick and indexing carefully means the optimizer introduces a TOP operator (which is ironic, since we aren't allowed to do that).
The other bit of syntax that might need explaining is in the FirstNSF CTE (the very first one). I use a construction that I blogged about here: http://sqlblog.com/blogs/paul_white/archive/2010/07/28/the-segment-top-query-optimisation.aspx to very efficiently find the first NSF per account group.
If (and when) you find something I have misunderstood about your requirement (i.e. a code bug) please let me know and I'll try to fix it up. As it stands, it seems to do what I think you said you want, but these things are always tricky.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
jblovesthegym (5/31/2012)
Greetings Greg, glad to see you're making progress on your issue. It's far above my mediocre scripting skills, otherwise it looks like a juicy one to sink teeth into.I'm only here during an idle lunchtime to point out a opportunity to improve your autosignature; YOUR glass might well be at 50% capacity, but given that the transport by humans of most unclosed liquid receptacles is not undertaken with those receptacles at more than 98-99% of total capacity by anyone other than the outright reckless who don't care much about having to then wipe their hands, or creating spillages on the floor, it would be more apt to say,
"...the glass is at 60% of operational capacity".
🙂
Tochee jb. I can see your point. However, the glass has a maximum volume that cannot be changed. Unless, of course, your elevation changes drastically enough that the gravitational force on the surface tension of the liquid becomes such that the liquid could actually rise above the rim of the container. All things being equal, one's glass, even during transport, is some fraction of the total capacity of the glass. If we lose some in the transport, then so be it. That's why I use lids.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less. -
Greg Snidow (5/31/2012)
I don't get it Paul. I took 10 of the accounts that had records in my result set and not yours, and ran both of our code on only those accounts, and the results were identical.There will be a logical reason (or perhaps you have ghosts, can't say for sure from here). Did you persist your 60,114 rows and my 59,252 rows in a table? It ought to be possible to quickly find differences from those, and check manually whether the rows belong in the result set or not. I'm sure it's not as easy as that, though. Oh, and I agree lids are vital.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Greg,
Last night while working n this I did find a problem with your solution. Based on your requirements, one of the records you return is incorrect. It happens to be the one that Paul found with his solution.
Your Results:
ACCT_NUM TS_PMT AT_PMT CD_PMT_SRC TS_NSF AT_NSF CD_PMT_SRC
692809411 2010-12-20 18:20:41.767741 -190.00 CF 2010-12-28 19:01:05.482708 190.00 CF
Your sample data:
ACCT_NUM TS_PMT AT_PMT ID_CASH_TRAN CD_PMT_SRC RowNum
692809411 2010-12-17 18:22:02.569107 -190.00 5000 CF 1 <-- Should return this
692809411 2010-12-20 18:20:41.767741 -190.00 5000 CF 2 <-- You return this this
692809411 2010-12-28 19:01:05.482708 190.00 5008 CF 1
-
Lynn Pettis (5/31/2012)
It happens to be the one that Paul found with his solution.Yes I highlighted that difference in my first post. I wonder if Greg missed it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Lynn Pettis (5/31/2012)
Based on your requirements, one of the records you return is incorrect. It happens to be the one that Paul found with his solution.
That record was bugging me too when I tried this one but I just figured it was a "hidden requirement."
Wait to go Paul! Leave it to an MVP to come up with a recursive solution to this monster. I should have said I "didn't think it was possible for me.":-)
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]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 -
Lynn Pettis (5/31/2012)
Greg,Last night while working n this I did find a problem with your solution. Based on your requirements, one of the records you return is incorrect. It happens to be the one that Paul found with his solution.
Your Results:
ACCT_NUM TS_PMT AT_PMT CD_PMT_SRC TS_NSF AT_NSF CD_PMT_SRC
692809411 2010-12-20 18:20:41.767741 -190.00 CF 2010-12-28 19:01:05.482708 190.00 CF
Your sample data:
ACCT_NUM TS_PMT AT_PMT ID_CASH_TRAN CD_PMT_SRC RowNum
692809411 2010-12-17 18:22:02.569107 -190.00 5000 CF 1 <-- Should return this
692809411 2010-12-20 18:20:41.767741 -190.00 5000 CF 2 <-- You return this this
692809411 2010-12-28 19:01:05.482708 190.00 5008 CF 1
Ahh, and therein may lie the problem. By looking at the above, the customer made two $190 payments, one on 12/17, and one on 12/20, then had a NSF on 12/28. One of the assumptions is that a NSF must be tied to the most recent instance of a payment in that amount. So, sinced the payment of $190 on 12/20 is more recent than the one made on 12/17, the NSF must be matched to the payment made on 12/20. It is easy to be confused by the multiple payments in the same dollar amount, but we have many customers who are on a levelized payment plan, so they pay the exact same amount every month. We then have customers who take their monthly amount and break it into several equal parts they send in throughout the month, sometimes just days apart.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply