May 29, 2012 at 6:30 am
Greetings all. I was posed with what turned out to be a more difficult problem than I had anticipated regarding determining payments that resulted in a NSF. I should note here that I have come up with two solutions, only one of which I will post here, but it is kind of bulky, and I was wondering if anyone could come up with a better way. Here are the assumptions: for a payment (id_cash_tran = 5000) to result in NSF, there must be a corresponding id_cash_tran = 5008 transaction with timestamp value within 60 days after the 5000 transaction, and the amount must be the negative of the original amount. Also, the NSF transactions are assumed to occur in the same order as the payments. For example, if customer 1 has two $500 payments on the same day, then has two NSF records a week later, the first NSF is to be matched with the first payment, and the second NSF is to be matched with the second payment. So, here is the table and data.
USE tempdb
GO
IF OBJECT_ID('tempdb..PAYMENTS','u') IS NOT NULL
DROP TABLE tempdb..PAYMENTS;
CREATE TABLE tempdb..PAYMENTS
(
ACCT_NUM NUMERIC(10,0), -- Self explanatory
TS_PMT DATETIME2(6), -- Timestamp of when the record hit our system
AT_PMT DECIMAL(11,2), -- Amount of the transaction
ID_CASH_TRAN CHAR(4), -- Char 4 transaction code. 5000 = payment, 5008 = NSF
CD_PMT_SRC CHAR(2) -- Payment source. There are many. Could be third party, credit card, etc
)
;
INSERT INTO tempdb..PAYMENTS
SELECT 37172806,'2011-09-28 20:21:43.970485',-8664.02,'5000','TP' UNION ALL
SELECT 37172806,'2011-04-21 20:22:01.081300',-800.00,'5000','CC' UNION ALL
SELECT 37172806,'2011-04-21 20:22:00.920541',-2055.60,'5000','TP' UNION ALL
SELECT 37172806,'2011-01-13 20:22:21.510461',-6000.00,'5000','TP' UNION ALL
SELECT 37172806,'2011-01-11 20:23:32.780891',3236.81,'5008','TP' UNION ALL
SELECT 37172806,'2011-01-04 18:18:20.990343',500.00,'5008','GC' UNION ALL
SELECT 37172806,'2011-01-04 18:18:20.206875',500.00,'5008','GC' UNION ALL
SELECT 37172806,'2010-12-28 18:50:45.769860',500.00,'5008','GC' UNION ALL
SELECT 37172806,'2010-12-23 20:21:44.823681',-3236.81,'5000','TP' UNION ALL
SELECT 37172806,'2010-12-23 20:21:44.816891',-500.00,'5000','CW' UNION ALL
SELECT 37172806,'2010-12-23 20:21:44.809691',-500.00,'5000','CW' UNION ALL
SELECT 37172806,'2010-12-23 20:21:44.728266',-500.00,'5000','CW' UNION ALL
SELECT 93299972,'2011-12-12 20:22:35.304907',-1000.00,'5000','TP' UNION ALL
SELECT 93299972,'2011-12-05 18:16:02.528679',500.00,'5008','GC' UNION ALL
SELECT 93299972,'2011-12-05 18:16:01.951365',500.00,'5008','GC' UNION ALL
SELECT 93299972,'2011-12-01 20:22:07.818174',-500.00,'5000','CW' UNION ALL
SELECT 93299972,'2011-12-01 20:22:07.649355',-500.00,'5000','CW' UNION ALL
SELECT 93299972,'2011-11-09 18:12:13.884036',142.00,'5008','GC' UNION ALL
SELECT 93299972,'2011-11-09 18:12:13.843398',500.00,'5008','GC' UNION ALL
SELECT 93299972,'2011-11-09 18:12:13.357006',500.00,'5008','GC' UNION ALL
SELECT 93299972,'2011-11-04 20:24:28.886868',-142.00,'5000','CW' UNION ALL
SELECT 93299972,'2011-11-04 20:24:28.872289',-500.00,'5000','CW' UNION ALL
SELECT 93299972,'2011-11-04 20:24:28.590740',-500.00,'5000','CW' UNION ALL
SELECT 93299972,'2011-09-26 18:14:24.812516',250.00,'5008','GC' UNION ALL
SELECT 93299972,'2011-09-22 20:22:23.081475',-250.00,'5000','CW' UNION ALL
SELECT 93299972,'2011-07-21 20:23:18.970942',-200.00,'5000','TP' UNION ALL
SELECT 93299972,'2011-06-15 20:22:44.308069',-150.00,'5000','TP' UNION ALL
SELECT 93299972,'2011-05-19 20:22:05.343900',-240.00,'5000','TP' UNION ALL
SELECT 114739790,'2011-12-27 18:09:46.129389',-200.00,'5000','MB' UNION ALL
SELECT 114739790,'2011-11-23 18:13:04.550969',-200.45,'5000','MB' UNION ALL
SELECT 114739790,'2011-09-16 20:22:44.701845',-607.74,'5000','TP' UNION ALL
SELECT 114739790,'2011-08-08 20:22:10.591635',-200.00,'5000','TP' UNION ALL
SELECT 114739790,'2011-07-13 18:17:19.482826',-100.00,'5000','MB' UNION ALL
SELECT 114739790,'2011-05-17 20:23:11.812372',-462.00,'5000','TP' UNION ALL
SELECT 114739790,'2011-05-16 20:23:06.653234',-1300.00,'5000','TP' UNION ALL
SELECT 114739790,'2011-04-20 18:16:57.795456',500.00,'5008','GC' UNION ALL
SELECT 114739790,'2011-04-20 18:16:57.222716',500.00,'5008','GC' UNION ALL
SELECT 114739790,'2011-04-15 18:14:55.295335',379.25,'5008','GC' UNION ALL
SELECT 114739790,'2011-04-13 20:22:27.885458',-500.00,'5000','CW' UNION ALL
SELECT 114739790,'2011-04-13 20:22:27.659510',-500.00,'5000','CW' UNION ALL
SELECT 114739790,'2011-04-11 20:22:56.769630',-379.25,'5000','CW' UNION ALL
SELECT 114739790,'2011-02-28 20:22:45.120888',-275.00,'5000','CW' UNION ALL
SELECT 114739790,'2010-12-29 20:22:02.069491',-200.00,'5000','CW' UNION ALL
SELECT 114739790,'2010-11-05 20:24:46.697936',-240.00,'5000','TP' UNION ALL
SELECT 232412320,'2011-11-02 18:18:21.295221',381.00,'5008','GC' UNION ALL
SELECT 232412320,'2011-11-02 18:18:21.192792',600.00,'5008','GC' UNION ALL
SELECT 232412320,'2011-11-02 18:18:20.336650',600.00,'5008','GC' UNION ALL
SELECT 232412320,'2011-11-01 13:53:40.504517',-1600.00,'5000','TP' UNION ALL
SELECT 232412320,'2011-10-24 20:23:16.818424',-381.00,'5000','CW' UNION ALL
SELECT 232412320,'2011-10-24 20:23:16.809497',-600.00,'5000','CW' UNION ALL
SELECT 232412320,'2011-10-24 20:23:16.712385',-600.00,'5000','CW' UNION ALL
SELECT 232412320,'2011-08-12 20:23:25.312317',-200.00,'5000','CW' UNION ALL
SELECT 232412320,'2011-06-30 20:22:42.962455',-475.00,'5000','CW' UNION ALL
SELECT 232412320,'2011-04-05 20:32:27.098455',-165.99,'5000','CW' UNION ALL
SELECT 232412320,'2011-04-05 20:32:27.020345',-500.00,'5000','CW' UNION ALL
SELECT 232412320,'2010-12-07 20:27:40.159718',-144.00,'5000','CW' UNION ALL
SELECT 232412320,'2010-12-07 20:27:39.600371',-600.00,'5000','CW' UNION ALL
SELECT 692809411,'2011-08-26 18:20:30.396055',-190.00,'5000','CF' UNION ALL
SELECT 692809411,'2011-08-17 18:26:54.614314',-465.71,'5000','GC' UNION ALL
SELECT 692809411,'2011-08-08 18:27:35.606194',190.00,'5008','CF' UNION ALL
SELECT 692809411,'2011-07-29 18:29:23.825201',-190.00,'5000','CF' UNION ALL
SELECT 692809411,'2011-07-12 18:33:10.543816',190.00,'5008','CF' UNION ALL
SELECT 692809411,'2011-07-01 18:24:40.453690',-190.00,'5000','CF' UNION ALL
SELECT 692809411,'2011-06-03 18:29:28.756537',-60.00,'5000','CF' UNION ALL
SELECT 692809411,'2011-04-29 18:23:05.421140',-100.00,'5000','CF' UNION ALL
SELECT 692809411,'2011-04-21 18:21:22.946513',-363.55,'5000','GC' UNION ALL
SELECT 692809411,'2011-04-08 20:29:22.183643',-70.00,'5000','TP' UNION ALL
SELECT 692809411,'2011-03-21 18:27:31.321997',190.00,'5008','CF' UNION ALL
SELECT 692809411,'2011-03-11 18:26:00.625220',-190.00,'5000','CF' UNION ALL
SELECT 692809411,'2011-01-28 18:24:08.262612',-20.00,'5000','CF' UNION ALL
SELECT 692809411,'2011-01-14 18:28:14.524934',-100.00,'5000','CF' UNION ALL
SELECT 692809411,'2010-12-28 19:01:05.482708',190.00,'5008','CF' UNION ALL
SELECT 692809411,'2010-12-20 18:20:41.767741',-190.00,'5000','CF' UNION ALL
SELECT 692809411,'2010-12-17 18:22:02.569107',-190.00,'5000','CF'
;
CREATE NONCLUSTERED INDEX IX_tempdb_PAYMENTS_ACCT_NUM__TS_PMT__id_cash_tran__at_pmt
ON PAYMENTS(ACCT_NUM ASC, TS_PMT DESC, id_cash_tran, at_pmt);
And now the solution. This requires creating two rounds of duplicate rows for each payment type (5000 and 5008), then using ROWNUMBER() to join them. With 36 million rows, it takes around 90 seconds to execute on my machine. The problem that sparked the request is that when a NSF record is generated, it has a different payment source (cd_pmt_src) than when the payment was received. I was asked to show all the NSF records with their corresponding payment cd_pmt_src, and payment timestamp.
/*
With CTE 'PMTS', I am creating duplicate rows for every payment row. That is,
for every row whereID_CASH_TRAN = '5000', I want to see every row where there
exists ID_CASH_TRAN = '5008', and AT_PMT is thenegative, and the '5008' row is
within 60 days. P_ROWNUM will allow me to join at the end
*/
WITH PMTS AS
(
SELECT
*,
P_ROWNUM = ROW_NUMBER() OVER (PARTITION BY ACCT_NUM, AT_PMT ORDER BY ACCT_NUM ASC, AT_PMT ASC, TS_PMT DESC)
FROM
(
SELECT DISTINCT
ACCT_NUM,
TS_PMT,
AT_PMT,
CD_PMT_SRC
FROM
(
SELECT
a.ACCT_NUM,
TS_PMT = a.TS_PMT,
a.AT_PMT,
a.CD_PMT_SRC
FROM tempdb..PAYMENTS a INNER JOIN tempdb..PAYMENTS b
ON a.ACCT_NUM = b.ACCT_NUM
AND a.AT_PMT = b.AT_PMT * -1
AND DATEDIFF(d,a.TS_PMT,b.TS_PMT) <= 60
AND b.TS_PMT > a.TS_PMT
WHERE a.ID_CASH_TRAN = '5000'
AND b.ID_CASH_TRAN = '5008'
) t1
) t2
),
/*
CTE NSF will then create duplicate rows using CTE PMTS and the source table again.
Join criteria is almost the same, with the exception that I only want to see the
ID_CASH_TRAN = '5008' rows here. Again, there will be duplicate rows, each with
a row number.
*/
NSF AS
(
SELECT
ACCT_NUM,
TS_NSF,
AT_NSF,
CD_PMT_SRC,
N_ROWNUM = ROW_NUMBER() OVER (PARTITION BY ACCT_NUM, AT_NSF ORDER BY ACCT_NUM ASC, AT_NSF ASC, TS_NSF DESC)
FROM
(
SELECT DISTINCT
ACCT_NUM,
TS_NSF,
AT_NSF,
CD_PMT_SRC
FROM
(
SELECT
b.ACCT_NUM,
TS_NSF = b.TS_PMT,
AT_NSF = b.AT_PMT,
b.CD_PMT_SRC
FROM PMTS a INNER JOIN tempdb..PAYMENTS b
ON a.ACCT_NUM = b.ACCT_NUM
AND a.AT_PMT = b.AT_PMT * -1
AND DATEDIFF(d,a.TS_PMT,b.TS_PMT) <=60
AND b.TS_PMT > a.TS_PMT
AND b.ID_CASH_TRAN = '5008'
) t1
)t2
)
/*
Now, by joining on the same criteria, and now including the row number,
I can see matched records. For this data it works, and for the real
data, I spot checked many and it seemed to work in all instances.
*/
SELECT
a.ACCT_NUM,
a.TS_PMT,
a.AT_PMT,
a.CD_PMT_SRC,
b.TS_NSF,
b.AT_NSF,
b.CD_PMT_SRC
FROM PMTS a INNER JOIN NSF b
ON a.ACCT_NUM = b.ACCT_NUM
AND a.AT_PMT = b.AT_NSF * -1
AND DATEDIFF(d,a.TS_PMT,b.TS_NSF) <= 60
AND b.TS_NSF > a.TS_PMT
AND a.P_ROWNUM = b.N_ROWNUM
ORDER BY ACCT_NUM ASC,TS_PMT ASC;
GO
-- to verify the results
SELECT * FROM tempdb..PAYMENTS
WHERE ACCT_NUM = 37172806
Now, let's verify the results. For account 37172806, they made three $500 dollar payments on the same day, and I am showing the NSF records matched to them in the order they were received. I think I am good. I had messed around with using a loop, which worked but was slow, then I tried using a recursive CTE, but could not figure it out. I am iterested in seeing a recursive CTE solution, even if it does not perform as well, just to see how it would be structured. Thank you to any takers.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 29, 2012 at 6:27 pm
Greg,
I just wanted to let you know I took a stab at this last night but I was not able to come up with a simpler query that returned the same results set.
I am curious why you think this may be solvable with a recursive CTE. I am unable to come up with a recursion formula that would satisfy the functional requirements. If you can and you post it, it may lend some additional understanding to the matching process.
Challenging and interesting problem to be sure. I also wanted to subscribe to this thread to see if anyone else is able to help you.
BTW. What is an NSF?
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
May 29, 2012 at 7:55 pm
CELKO (5/29/2012)
This is not a table; it has no key asnd not way rto ever have a key.
Well, Joe, thank you once again for pointing out the obvious. However, the fact that the table has no key is not relevant here. What is relevant, and maybe I was not clear in the problem definition, is that we have no way to link a payment with a subsequent NSF if there is one, other than to match on the account number and amount.
Account numbers are not used in math, so they are strings and not numeric. Look up the term “tag numbers” in your data modeling book. And while you have it open, read the section of the ISI-11179 rules for data element names.
Wow, Joe, thank you so much for your great and meaningful insight! For anyone else who may also be wondering why I did that, it is because those are the datatypes in our mainframe, to which I have a linked server to get the data. When SQL Server gets the data, it correctly gives it a datatype that is equivalent to the host. Imagine that. I can't speak to why the COBOL programmers did that back in 1993, but I suspect it has something to do with storage space being an issue back then. Ok Joe, here is an exercise for you to try at home.
DECLARE @decimal DECIMAL(10,0), @char CHAR(10)
SET @decimal = 1234567890
SET @char = '1234567890'
SELECT 'Dec bytes' = DATALENGTH(@decimal), 'Char bytes' = DATALENGTH(@char)
Do you really track transactions to nanoseconds? Most of us are happy with a date.
Joe, I really can't speak to what makes 'most of us' 'happy'. To elaborate on the data a bit, there are around 6 other columns I did not show included in the mainframe table key, and, yes Joe, they make the row unique. However, nothing about a row's uniqueness in this table helps me solve the problem at hand. I included only the columns that I needed.
We have the ANSI syntax now; no need to use 1970's Sybase dialect.
Hey Joe, we have English now, no need to say things like:
CELKO (5/29/2012)
This is not a table; it has no key asnd not way rto ever have a key.
Unfortunately, your data does not have a key!! you had dups!
I'm not sure what 'dups' is, but if you are meaning to say 'dupe', Joe, commonly used in the English language as slang for 'duplicate', then I think you may be mistaken. There were 74 rows in my sample data. Please open one of your books, and investigate COUNT() and the DISTINCT key word. Here is a hint, you can use them like this...
SELECT COUNT(*) FROM tempdb..payments
SELECT DISTINCT * FROM tempdb..payments
Also, Joe, I took the liberty of creating a table called 'Joes_Payments' using your data. If you get your book out again, you might take interest on the section entitled 'LEFT JOIN'. To get you started, you could try something like this...
SELECT
a.*
FROM tempdb..payments a LEFT OUTER JOIN Joes_Payments b
ON a.acct_num = b.acct_nbr
AND a.ts_pmt = b.payment_timestamp
AND a.at_pmt = b.payment_amt
AND a.id_cash_tran = b.transaction_code
AND a.cd_pmt_src = b.payment_source
WHERE b.acct_nbr IS NULL
YUCK!
Couldn't have said it better myself Joe.
What happens if I do not pay the whole amount?
Very good question Joe. The term 'NSF', which I mistakenly took to be universally understood, stands for Not Sufficient Funds, and is banking parlance for what happens when you write a bad check. I'm not aware of any instances of a bank paying some of the amount, if you don't have enough funds in your account to cover the entire amount. So, for our purposes, we don't have any partial payments via check. If a customer writes a check for an amount less than what is due, it is not relevant here, as the check amount and NSF amount will be the same, and are not dependent upon what is actually owed.
We can do running totals now.
Who said anything about running totals? If I needed to do a running sum problem, Jeff's got quite the arsenal of well tested code posted here in very well written and respectful articles.
Does this help?
Well, Joe, I'm not sure, as your code did not run as posted. Now, there may be some configurations you may have assumed I knew to set, but I don't think it really warrants the effort of figuring it out. However, just from looking at the column headings, I can see it has something to do with running sums, which is not what I was after. Did you read the problem?
Thank you so much, Joe, for taking your good time to consider my problem. I know you are probably very busy, and you can ill afford to spend time on my trifles. You really shouldn't have. Really.
To the rest of 'us', I apologize for ranting like this, as *most* of the help I have either received here myself, or read in posts by others is very respectful and helpful. Thank you all.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 29, 2012 at 8:04 pm
dwain.c (5/29/2012)
I am curious why you think this may be solvable with a recursive CTE. I am unable to come up with a recursion formula that would satisfy the functional requirements. If you can and you post it, it may lend some additional understanding to the matching process.
Dwain, thanks for taking a look. My first stab at it was to create two temp tables, one with payments, and one with NSF's. Then, with a loop, update a column in the payments table with the minimum timestamp value of the NSF where the account numbers and ABS(at_pmt) matched, and the NSF timestamp was greater than the payment timestamp. Then, set a bit column added to the NSF table = 1 to indicate it was already spoken for. Continue until all NSF had found a home. It worked, but I really wanted something I could also run directly on the mainframe, so I abandoned it. However, it just occurred to me that the way I was using available NSFs to update the payments table seemed like it could be done recursively. However, I could not figure out even how to start, so I abandoned that avenue as well. Again, I'm pretty sure my solution works, we are still looking for errors, I was mainly interested in any other solutions for academic purposes.
NSF = Not Sufficient Funds, and is what happens when a check bounces. For a little more detail, see my response to Joe. Thanks again.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 29, 2012 at 9:32 pm
Greg Snidow (5/29/2012)
dwain.c (5/29/2012)
I am curious why you think this may be solvable with a recursive CTE. I am unable to come up with a recursion formula that would satisfy the functional requirements. If you can and you post it, it may lend some additional understanding to the matching process.Dwain, thanks for taking a look. My first stab at it was to create two temp tables, one with payments, and one with NSF's. Then, with a loop, update a column in the payments table with the minimum timestamp value of the NSF where the account numbers and ABS(at_pmt) matched, and the NSF timestamp was greater than the payment timestamp. Then, set a bit column added to the NSF table = 1 to indicate it was already spoken for. Continue until all NSF had found a home. It worked, but I really wanted something I could also run directly on the mainframe, so I abandoned it. However, it just occurred to me that the way I was using available NSFs to update the payments table seemed like it could be done recursively. However, I could not figure out even how to start, so I abandoned that avenue as well. Again, I'm pretty sure my solution works, we are still looking for errors, I was mainly interested in any other solutions for academic purposes.
NSF = Not Sufficient Funds, and is what happens when a check bounces. For a little more detail, see my response to Joe. Thanks again.
So from this description, I think you're saying you want the following from the recursive CTE;
Anchor Leg - Matches the first payment record for each account with the first NSF record and marks that NSF (and payment) as matched.
Recursive Leg - Processes the next payment record for each account with the next NSF, which sort of implies an EXCEPT on what the CTE has already generated.
Saying it this way, I'm not sure it is possible (I'm somewhat recursively challenged even though I've posted a few recursive CTEs in the past), so I am not entirely sure how to explain why I think it won't.
I am intrigued with this problem though. I started out the same way, creating 2 tables with 2000 and 2008 account codes. I then couldn't get the matching quite right. My best effort returned one extra row matching a payment to a duplicate NSF.
I may have another look when I have some more spare time. At least since it is academic, you're not in a hurry!
BTW. Nice comeback to CELKO.
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
May 30, 2012 at 10:48 am
So, we had this issue at my previous company. Now, this database was refreshed nightly and not updated during the day... So this is a bit different. However, our solution was to run a nightly job to parse out the data as we needed it. I could see maybe adding another table and a trigger to parse out your 5008 payments into their own table? Sometimes the reorganization of data in another step will make this easier.
Jared
CE - Microsoft
May 30, 2012 at 11:50 am
SQLKnowItAll (5/30/2012)
I could see maybe adding another table and a trigger to parse out your 5008 payments into their own table? Sometimes the reorganization of data in another step will make this easier.
Jared, I completely agree it could be easier. However, making changes to the mainframe ain't happenin'. There are hundreds of COBOL jobs that run every night, and they are all very tightly interwoven and timed to a tee. There aren't many people left here who truly understand it all, and one wrong move could cause great headaches. And those who do understand it all are tied up with projects many orders of magnitude greater than my negligible issue.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 30, 2012 at 12:23 pm
Not sure this is correct, couldn't find any expected results based on the sample data provided, so you will have to tell me if it is okay.
USE Sandbox -- my test database, I don't like using tempdb for this
GO
IF OBJECT_ID('dbo.PAYMENTS','u') IS NOT NULL
DROP TABLE tempdb..PAYMENTS;
CREATE TABLE dbo.PAYMENTS
(
ACCT_NUM NUMERIC(10,0), -- Self explanatory
TS_PMT DATETIME2(6), -- Timestamp of when the record hit our system
AT_PMT DECIMAL(11,2), -- Amount of the transaction
ID_CASH_TRAN CHAR(4), -- Char 4 transaction code. 5000 = payment, 5008 = NSF
CD_PMT_SRC CHAR(2) -- Payment source. There are many. Could be third party, credit card, etc
)
;
GO
INSERT INTO dbo.PAYMENTS
SELECT 37172806,'2011-09-28 20:21:43.970485',-8664.02,'5000','TP' UNION ALL
SELECT 37172806,'2011-04-21 20:22:01.081300',-800.00,'5000','CC' UNION ALL
SELECT 37172806,'2011-04-21 20:22:00.920541',-2055.60,'5000','TP' UNION ALL
SELECT 37172806,'2011-01-13 20:22:21.510461',-6000.00,'5000','TP' UNION ALL
SELECT 37172806,'2011-01-11 20:23:32.780891',3236.81,'5008','TP' UNION ALL
SELECT 37172806,'2011-01-04 18:18:20.990343',500.00,'5008','GC' UNION ALL
SELECT 37172806,'2011-01-04 18:18:20.206875',500.00,'5008','GC' UNION ALL
SELECT 37172806,'2010-12-28 18:50:45.769860',500.00,'5008','GC' UNION ALL
SELECT 37172806,'2010-12-23 20:21:44.823681',-3236.81,'5000','TP' UNION ALL
SELECT 37172806,'2010-12-23 20:21:44.816891',-500.00,'5000','CW' UNION ALL
SELECT 37172806,'2010-12-23 20:21:44.809691',-500.00,'5000','CW' UNION ALL
SELECT 37172806,'2010-12-23 20:21:44.728266',-500.00,'5000','CW' UNION ALL
SELECT 93299972,'2011-12-12 20:22:35.304907',-1000.00,'5000','TP' UNION ALL
SELECT 93299972,'2011-12-05 18:16:02.528679',500.00,'5008','GC' UNION ALL
SELECT 93299972,'2011-12-05 18:16:01.951365',500.00,'5008','GC' UNION ALL
SELECT 93299972,'2011-12-01 20:22:07.818174',-500.00,'5000','CW' UNION ALL
SELECT 93299972,'2011-12-01 20:22:07.649355',-500.00,'5000','CW' UNION ALL
SELECT 93299972,'2011-11-09 18:12:13.884036',142.00,'5008','GC' UNION ALL
SELECT 93299972,'2011-11-09 18:12:13.843398',500.00,'5008','GC' UNION ALL
SELECT 93299972,'2011-11-09 18:12:13.357006',500.00,'5008','GC' UNION ALL
SELECT 93299972,'2011-11-04 20:24:28.886868',-142.00,'5000','CW' UNION ALL
SELECT 93299972,'2011-11-04 20:24:28.872289',-500.00,'5000','CW' UNION ALL
SELECT 93299972,'2011-11-04 20:24:28.590740',-500.00,'5000','CW' UNION ALL
SELECT 93299972,'2011-09-26 18:14:24.812516',250.00,'5008','GC' UNION ALL
SELECT 93299972,'2011-09-22 20:22:23.081475',-250.00,'5000','CW' UNION ALL
SELECT 93299972,'2011-07-21 20:23:18.970942',-200.00,'5000','TP' UNION ALL
SELECT 93299972,'2011-06-15 20:22:44.308069',-150.00,'5000','TP' UNION ALL
SELECT 93299972,'2011-05-19 20:22:05.343900',-240.00,'5000','TP' UNION ALL
SELECT 114739790,'2011-12-27 18:09:46.129389',-200.00,'5000','MB' UNION ALL
SELECT 114739790,'2011-11-23 18:13:04.550969',-200.45,'5000','MB' UNION ALL
SELECT 114739790,'2011-09-16 20:22:44.701845',-607.74,'5000','TP' UNION ALL
SELECT 114739790,'2011-08-08 20:22:10.591635',-200.00,'5000','TP' UNION ALL
SELECT 114739790,'2011-07-13 18:17:19.482826',-100.00,'5000','MB' UNION ALL
SELECT 114739790,'2011-05-17 20:23:11.812372',-462.00,'5000','TP' UNION ALL
SELECT 114739790,'2011-05-16 20:23:06.653234',-1300.00,'5000','TP' UNION ALL
SELECT 114739790,'2011-04-20 18:16:57.795456',500.00,'5008','GC' UNION ALL
SELECT 114739790,'2011-04-20 18:16:57.222716',500.00,'5008','GC' UNION ALL
SELECT 114739790,'2011-04-15 18:14:55.295335',379.25,'5008','GC' UNION ALL
SELECT 114739790,'2011-04-13 20:22:27.885458',-500.00,'5000','CW' UNION ALL
SELECT 114739790,'2011-04-13 20:22:27.659510',-500.00,'5000','CW' UNION ALL
SELECT 114739790,'2011-04-11 20:22:56.769630',-379.25,'5000','CW' UNION ALL
SELECT 114739790,'2011-02-28 20:22:45.120888',-275.00,'5000','CW' UNION ALL
SELECT 114739790,'2010-12-29 20:22:02.069491',-200.00,'5000','CW' UNION ALL
SELECT 114739790,'2010-11-05 20:24:46.697936',-240.00,'5000','TP' UNION ALL
SELECT 232412320,'2011-11-02 18:18:21.295221',381.00,'5008','GC' UNION ALL
SELECT 232412320,'2011-11-02 18:18:21.192792',600.00,'5008','GC' UNION ALL
SELECT 232412320,'2011-11-02 18:18:20.336650',600.00,'5008','GC' UNION ALL
SELECT 232412320,'2011-11-01 13:53:40.504517',-1600.00,'5000','TP' UNION ALL
SELECT 232412320,'2011-10-24 20:23:16.818424',-381.00,'5000','CW' UNION ALL
SELECT 232412320,'2011-10-24 20:23:16.809497',-600.00,'5000','CW' UNION ALL
SELECT 232412320,'2011-10-24 20:23:16.712385',-600.00,'5000','CW' UNION ALL
SELECT 232412320,'2011-08-12 20:23:25.312317',-200.00,'5000','CW' UNION ALL
SELECT 232412320,'2011-06-30 20:22:42.962455',-475.00,'5000','CW' UNION ALL
SELECT 232412320,'2011-04-05 20:32:27.098455',-165.99,'5000','CW' UNION ALL
SELECT 232412320,'2011-04-05 20:32:27.020345',-500.00,'5000','CW' UNION ALL
SELECT 232412320,'2010-12-07 20:27:40.159718',-144.00,'5000','CW' UNION ALL
SELECT 232412320,'2010-12-07 20:27:39.600371',-600.00,'5000','CW' UNION ALL
SELECT 692809411,'2011-08-26 18:20:30.396055',-190.00,'5000','CF' UNION ALL
SELECT 692809411,'2011-08-17 18:26:54.614314',-465.71,'5000','GC' UNION ALL
SELECT 692809411,'2011-08-08 18:27:35.606194',190.00,'5008','CF' UNION ALL
SELECT 692809411,'2011-07-29 18:29:23.825201',-190.00,'5000','CF' UNION ALL
SELECT 692809411,'2011-07-12 18:33:10.543816',190.00,'5008','CF' UNION ALL
SELECT 692809411,'2011-07-01 18:24:40.453690',-190.00,'5000','CF' UNION ALL
SELECT 692809411,'2011-06-03 18:29:28.756537',-60.00,'5000','CF' UNION ALL
SELECT 692809411,'2011-04-29 18:23:05.421140',-100.00,'5000','CF' UNION ALL
SELECT 692809411,'2011-04-21 18:21:22.946513',-363.55,'5000','GC' UNION ALL
SELECT 692809411,'2011-04-08 20:29:22.183643',-70.00,'5000','TP' UNION ALL
SELECT 692809411,'2011-03-21 18:27:31.321997',190.00,'5008','CF' UNION ALL
SELECT 692809411,'2011-03-11 18:26:00.625220',-190.00,'5000','CF' UNION ALL
SELECT 692809411,'2011-01-28 18:24:08.262612',-20.00,'5000','CF' UNION ALL
SELECT 692809411,'2011-01-14 18:28:14.524934',-100.00,'5000','CF' UNION ALL
SELECT 692809411,'2010-12-28 19:01:05.482708',190.00,'5008','CF' UNION ALL
SELECT 692809411,'2010-12-20 18:20:41.767741',-190.00,'5000','CF' UNION ALL
SELECT 692809411,'2010-12-17 18:22:02.569107',-190.00,'5000','CF'
;
CREATE NONCLUSTERED INDEX IX_tempdb_PAYMENTS_ACCT_NUM__TS_PMT__id_cash_tran__at_pmt
ON dbo.PAYMENTS(ACCT_NUM ASC, TS_PMT DESC, id_cash_tran, at_pmt);
go
with BaseData as (
SELECT
ACCT_NUM,
TS_PMT,
AT_PMT,
ID_CASH_TRAN,
CD_PMT_SRC,
ROW_NUMBER() over (partition by ACCT_NUM, ID_CASH_TRAN, AT_PMT order by TS_PMT) as RowNum
FROM
dbo.PAYMENTS
)
select
bd1.ACCT_NUM,
bd1.TS_PMT,
bd1.AT_PMT,
bd1.ID_CASH_TRAN,
bd1.CD_PMT_SRC,
bd2.TS_PMT,
bd2.AT_PMT,
bd2.ID_CASH_TRAN,
bd2.CD_PMT_SRC
from
BaseData bd1
inner join BaseData bd2
on (bd1.ACCT_NUM = bd2.ACCT_NUM
and bd1.RowNum = bd2.RowNum
and bd1.ID_CASH_TRAN = 5000
and bd2.ID_CASH_TRAN = 5008
and ABS(bd1.AT_PMT) = bd2.AT_PMT
and bd2.TS_PMT between bd1.TS_PMT and dateadd(dd, 60, bd1.TS_PMT))
ORDER BY bd1.ACCT_NUM, bd1.TS_PMT;
go
drop table dbo.PAYMENTS;
go
May 30, 2012 at 12:41 pm
Lynn Pettis (5/30/2012)
Not sure this is correct, couldn't find any expected results based on the sample data provided, so you will have to tell me if it is okay.
Lynn, I appologize if it was not clear. This...
SELECT
a.ACCT_NUM,
a.TS_PMT,
a.AT_PMT,
a.CD_PMT_SRC,
b.TS_NSF,
b.AT_NSF,
b.CD_PMT_SRC
FROM PMTS a INNER JOIN NSF b
ON a.ACCT_NUM = b.ACCT_NUM
AND a.AT_PMT = b.AT_NSF * -1
AND DATEDIFF(d,a.TS_PMT,b.TS_NSF) <= 60
AND b.TS_NSF > a.TS_PMT
AND a.P_ROWNUM = b.N_ROWNUM
ORDER BY ACCT_NUM ASC,TS_PMT ASC;
is my expected results. The code I posted works exactly as I need it to, so the results of the above, which requires the inclusion of the two CTE's is my expected results. I'll try out your code, as I'm just looking for a better way. Thank you.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 30, 2012 at 12:53 pm
Once I got your code to run, I do see a slight problem. I have three less values than you. So I am looking into why I missed them.
May 30, 2012 at 1:01 pm
Found my problem, now to solve it.
May 30, 2012 at 1:02 pm
Lynn Pettis (5/30/2012)
you will have to tell me if it is okay.
Lynn, it's very close. Below is my expected output. The three rows with asterisks are data missing from your results. Thanks again.
ACCT_NUMTS_PMTAT_PMTCD_PMT_SRCTS_NSFAT_NSF
371728062010-12-23 20:21:44.728266-500.00CW2010-12-28 18:50:45.769860500.00
371728062010-12-23 20:21:44.809691-500.00CW2011-01-04 18:18:20.206875500.00
371728062010-12-23 20:21:44.816891-500.00CW2011-01-04 18:18:20.990343500.00
371728062010-12-23 20:21:44.823681-3236.81TP2011-01-11 20:23:32.7808913236.81
932999722011-09-22 20:22:23.081475-250.00CW2011-09-26 18:14:24.812516250.00
932999722011-11-04 20:24:28.590740-500.00CW2011-11-09 18:12:13.357006500.00
932999722011-11-04 20:24:28.872289-500.00CW2011-11-09 18:12:13.843398500.00
932999722011-11-04 20:24:28.886868-142.00CW2011-11-09 18:12:13.884036142.00
932999722011-12-01 20:22:07.649355-500.00CW2011-12-05 18:16:01.951365500.00
932999722011-12-01 20:22:07.818174-500.00CW2011-12-05 18:16:02.528679500.00
1147397902011-04-11 20:22:56.769630-379.25CW2011-04-15 18:14:55.295335379.25
1147397902011-04-13 20:22:27.659510-500.00CW2011-04-20 18:16:57.222716500.00
1147397902011-04-13 20:22:27.885458-500.00CW2011-04-20 18:16:57.795456500.00
2324123202011-10-24 20:23:16.712385-600.00CW2011-11-02 18:18:20.336650600.00*
2324123202011-10-24 20:23:16.809497-600.00CW2011-11-02 18:18:21.192792600.00
2324123202011-10-24 20:23:16.818424-381.00CW2011-11-02 18:18:21.295221381.00
6928094112010-12-20 18:20:41.767741-190.00CF2010-12-28 19:01:05.482708190.00
6928094112011-03-11 18:26:00.625220-190.00CF2011-03-21 18:27:31.321997190.00*
6928094112011-07-01 18:24:40.453690-190.00CF2011-07-12 18:33:10.543816190.00*
6928094112011-07-29 18:29:23.825201-190.00CF2011-08-08 18:27:35.606194190.00
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 30, 2012 at 6:41 pm
Greg Snidow (5/30/2012)
Lynn Pettis (5/30/2012)
you will have to tell me if it is okay.Lynn, it's very close. Below is my expected output. The three rows with asterisks are data missing from your results. Thanks again.
ACCT_NUMTS_PMTAT_PMTCD_PMT_SRCTS_NSFAT_NSF
371728062010-12-23 20:21:44.728266-500.00CW2010-12-28 18:50:45.769860500.00
371728062010-12-23 20:21:44.809691-500.00CW2011-01-04 18:18:20.206875500.00
371728062010-12-23 20:21:44.816891-500.00CW2011-01-04 18:18:20.990343500.00
371728062010-12-23 20:21:44.823681-3236.81TP2011-01-11 20:23:32.7808913236.81
932999722011-09-22 20:22:23.081475-250.00CW2011-09-26 18:14:24.812516250.00
932999722011-11-04 20:24:28.590740-500.00CW2011-11-09 18:12:13.357006500.00
932999722011-11-04 20:24:28.872289-500.00CW2011-11-09 18:12:13.843398500.00
932999722011-11-04 20:24:28.886868-142.00CW2011-11-09 18:12:13.884036142.00
932999722011-12-01 20:22:07.649355-500.00CW2011-12-05 18:16:01.951365500.00
932999722011-12-01 20:22:07.818174-500.00CW2011-12-05 18:16:02.528679500.00
1147397902011-04-11 20:22:56.769630-379.25CW2011-04-15 18:14:55.295335379.25
1147397902011-04-13 20:22:27.659510-500.00CW2011-04-20 18:16:57.222716500.00
1147397902011-04-13 20:22:27.885458-500.00CW2011-04-20 18:16:57.795456500.00
2324123202011-10-24 20:23:16.712385-600.00CW2011-11-02 18:18:20.336650600.00*
2324123202011-10-24 20:23:16.809497-600.00CW2011-11-02 18:18:21.192792600.00
2324123202011-10-24 20:23:16.818424-381.00CW2011-11-02 18:18:21.295221381.00
6928094112010-12-20 18:20:41.767741-190.00CF2010-12-28 19:01:05.482708190.00
6928094112011-03-11 18:26:00.625220-190.00CF2011-03-21 18:27:31.321997190.00*
6928094112011-07-01 18:24:40.453690-190.00CF2011-07-12 18:33:10.543816190.00*
6928094112011-07-29 18:29:23.825201-190.00CF2011-08-08 18:27:35.606194190.00
This is quite similar to the results I was getting when I tried it. I tried it again yesterday, alas without success.
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
May 30, 2012 at 6:57 pm
Lynn Pettis (5/30/2012)
Once I got your code to run, I do see a slight problem. I have three less values than you. So I am looking into why I missed them.
That's the same thing that happened to me when I first started, and also seems to have happened to Dwain. The problem happens when there are multiples of the same payment amount (id_cash_tran = 5000) after the last instance that same amount having id_cash_tran = 5008 (the NSF's). This throws off the row numbers. That's why I ended up having to do two rounds of joins. The purpose of the first CTE is to get only the 5000 records having at least one instance of the same amount showing up with a 5008 id_cash_tran at a later date, and within 60 days. Once this is done, the method we all seem to have tried will work. Anyhow, thank you for taking an interest.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 30, 2012 at 7:02 pm
dwain.c (5/30/2012)
Greg Snidow (5/30/2012)
Lynn Pettis (5/30/2012)
you will have to tell me if it is okay.Lynn, it's very close. Below is my expected output. The three rows with asterisks are data missing from your results. Thanks again.
ACCT_NUMTS_PMTAT_PMTCD_PMT_SRCTS_NSFAT_NSF
371728062010-12-23 20:21:44.728266-500.00CW2010-12-28 18:50:45.769860500.00
371728062010-12-23 20:21:44.809691-500.00CW2011-01-04 18:18:20.206875500.00
371728062010-12-23 20:21:44.816891-500.00CW2011-01-04 18:18:20.990343500.00
371728062010-12-23 20:21:44.823681-3236.81TP2011-01-11 20:23:32.7808913236.81
932999722011-09-22 20:22:23.081475-250.00CW2011-09-26 18:14:24.812516250.00
932999722011-11-04 20:24:28.590740-500.00CW2011-11-09 18:12:13.357006500.00
932999722011-11-04 20:24:28.872289-500.00CW2011-11-09 18:12:13.843398500.00
932999722011-11-04 20:24:28.886868-142.00CW2011-11-09 18:12:13.884036142.00
932999722011-12-01 20:22:07.649355-500.00CW2011-12-05 18:16:01.951365500.00
932999722011-12-01 20:22:07.818174-500.00CW2011-12-05 18:16:02.528679500.00
1147397902011-04-11 20:22:56.769630-379.25CW2011-04-15 18:14:55.295335379.25
1147397902011-04-13 20:22:27.659510-500.00CW2011-04-20 18:16:57.222716500.00
1147397902011-04-13 20:22:27.885458-500.00CW2011-04-20 18:16:57.795456500.00
2324123202011-10-24 20:23:16.712385-600.00CW2011-11-02 18:18:20.336650600.00*
2324123202011-10-24 20:23:16.809497-600.00CW2011-11-02 18:18:21.192792600.00
2324123202011-10-24 20:23:16.818424-381.00CW2011-11-02 18:18:21.295221381.00
6928094112010-12-20 18:20:41.767741-190.00CF2010-12-28 19:01:05.482708190.00
6928094112011-03-11 18:26:00.625220-190.00CF2011-03-21 18:27:31.321997190.00*
6928094112011-07-01 18:24:40.453690-190.00CF2011-07-12 18:33:10.543816190.00*
6928094112011-07-29 18:29:23.825201-190.00CF2011-08-08 18:27:35.606194190.00
This is quite similar to the results I was getting when I tried it. I tried it again yesterday, alas without success.
And that's what happened to me at first. When first asked if it could be done, I said (in true Barny Fife fashion) "sure, no problem." It was only after working on it for a few hours that I realized it wasn't so simple. I did try to mess around with a recursive CTE today, but have not even come close to making any progress, so I think I'll take your word for it that it can't be done. Thanks again.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply