Matching query
-
I have two tables: (1) tblTransaction (2) tblForMatching
select tbltransaction.*
from (
select 1 tID, '2007-08-02' as transdate, 'OWNER-001' as ownercode, 'C-001' as customercode, 1000 as amount, null as matchID union all
select 2 tID, '2007-08-02' as transdate, 'OWNER-001' as ownercode, 'C-001' as customercode, 1000 as amount, null as matchID union all
select 3 tID, '2007-08-02' as transdate, 'OWNER-002' as ownercode, 'C-002' as customercode, 2000 as amount, null as matchID union all
select 4 tID, '2007-08-02' as transdate, 'OWNER-003' as ownercode, 'C-003' as customercode, 500 as amount, null as matchID union all
select 5 tID, '2007-08-02' as transdate, 'OWNER-004' as ownercode, 'C-004' as customercode, 1500 as amount, null as matchID union all
select 6 tID, '2007-08-02' as transdate, 'OWNER-005' as ownercode, 'C-005' as customercode, 2500 as amount, null as matchID
) as tblTransaction
select tblForMatching.*
from (
select 1 as matchID, '2007-08-02 11:17:24.967' as transdate, 'C-001' as customercode, 1000 as amount union all -- match
select 2 as matchID, '2007-08-02 11:18:21.967' as transdate, 'C-001' as customercode, 1000 as amount union all -- match
select 3 as matchID, '2007-08-02 11:18:11.967' as transdate, 'C-001' as customercode, 1000 as amount union all -- no match
select 4 as matchID, '2007-08-02 11:19:22.967' as transdate, 'C-001' as customercode, 1000 as amount union all -- no match
select 5 as matchID, '2007-08-02 11:20:12.967' as transdate, 'C-002' as customercode, 2000 as amount union all -- match
select 6 as matchID, '2007-08-02 11:21:14.967' as transdate, 'C-004' as customercode, 1500 as amount union all -- match
select 7 as matchID, '2007-08-02 12:22:15.967' as transdate, 'C-001' as customercode, 1000 as amount union all -- no match
select 8 as matchID, '2007-08-02 12:23:34.967' as transdate, 'C-005' as customercode, 2500 as amount union all -- match
select 9 as matchID, '2007-08-02 12:23:38.967' as transdate, 'C-006' as customercode, 2500 as amount -- no match
) as tblForMatching
What I want is to do a matching from tblForMatching to tblTransaction by their exact customercode and amount on each transdate. Incase of multiple customercode and amount on each transdate in tblForMatching, matching takes place depending on the occurrences of customercode and amount also in the tblTransaction. Matching occurs on first come basis, field tID on tblTransaction and transdate on tblForMatching.
Please refer an example on the customercode = 'C-001' and amount = 1000.
Desired result should be like this...
select '2007-08-02' as transdate, 1 tID, 'OWNER-001' as ownercode, 'C-001' as customercode, 1000 as amount, 1 as matchID, '2007-08-02 11:17:24.967' as transdate union all
select '2007-08-02' as transdate, 2 tID, 'OWNER-001' as ownercode, 'C-001' as customercode, 1000 as amount, 2 as matchID, '2007-08-02 11:18:21.967' as transdate union all
select '2007-08-02' as transdate, 3 tID, 'OWNER-002' as ownercode, 'C-002' as customercode, 2000 as amount, 5 as matchID, '2007-08-02 11:20:12.967' as transdate union all
select '2007-08-02' as transdate, 4 tID, 'OWNER-003' as ownercode, 'C-003' as customercode, 500 as amount, null as matchID, null as transdate union all
select '2007-08-02' as transdate, 5 tID, 'OWNER-004' as ownercode, 'C-004' as customercode, 1500 as amount, 6 as matchID, '2007-08-02 11:21:14.967' as transdate union all
select '2007-08-02' as transdate, 6 tID, 'OWNER-005' as ownercode, 'C-005' as customercode, 2500 as amount, 8 as matchID, '2007-08-02 12:23:34.967' as transdate union all
select '2007-08-02' as transdate, null as tID, null as ownercode, 'C-001' as customercode, 1000 as amount, 3 as matchID, '2007-08-02 11:18:11.967' as transdate union all
select '2007-08-02' as transdate, null as tID, null as ownercode, 'C-001' as customercode, 1000 as amount, 4 as matchID, '2007-08-02 11:19:22.967' as transdate union all
select '2007-08-02' as transdate, null as tID, null as ownercode, 'C-001' as customercode, 1000 as amount, 7 as matchID, '2007-08-02 12:22:15.967' as transdate union all
select '2007-08-02' as transdate, null as tID, null as ownercode, 'C-006' as customercode, 2500 as amount, 9 as matchID, '2007-08-02 12:23:38.967' as transdate
-
Huh... did you edit the post? I started working on it, and now I see there are 9 rows in table Matching, while there were only 8 when I copied the definition.
I'll better drop the tables and recreate them with new data.
-
Yup sorry Vladan, i added just one(1). because desired result is wrong. tnx for the reply.
-
OK, I see that it works better now but there still seem to be some inconsistencies. You say that you do the matching from the side of TBLforMatching based on earlier date, while result shows it is done using matchID.
2007-08-02 11:18:21.967 (matchID 2) is matched, while 2007-08-02 11:18:11.967 (matchID 3) is not.
So, which one is true?
-
yup... because the corresponding matching from tbltransaction happens only base on the top occurrences from TBLforMatching so in this case (matchID 3) will have no match.
thanks.
-
This gives the required result as I understood it from description - it differs where the posted desired result does not follow the rules of description (see previous post).
SELECT COALESCE(X.transdate, Y.transday) as transdate,
X.tID, t3.ownercode,
COALESCE(X.customercode, Y.customercode) as customercode,
COALESCE(X.amount,Y.amount) as amount,
m3.matchID, Y.transdate
FROM
(SELECT t1.tID, t1.transdate, t1.customercode, t1.amount, COUNT(*) as rank
FROM #trans t1
JOIN #trans t2 ON t1.transdate=t2.transdate AND t1.customercode=t2.customercode AND t1.amount=t2.amount
AND t1.tID >= t2.tID
GROUP BY t1.tID, t1.transdate, t1.customercode, t1.amount) as X
FULL OUTER JOIN
(SELECT m1.transdate, DATEADD(d, DATEDIFF(d, 0, m1.transdate),0) as transday, m1.customercode, m1.amount, COUNT(*) as rank
FROM #matching m1
JOIN #matching m2 ON m1.customercode=m2.customercode AND m1.amount=m2.amount
AND DATEADD(d, DATEDIFF(d, 0, m1.transdate), 0)=DATEADD(d, DATEDIFF(d, 0, m2.transdate), 0)
AND m1.transdate >= m2.transdate
GROUP BY m1.transdate, DATEADD(d, DATEDIFF(d, 0, m1.transdate),0), m1.customercode, m1.amount) as Y
ON X.transdate=Y.transday AND X.customercode = Y.customercode AND X.amount=Y.amount AND X.rank=Y.rank
LEFT JOIN #trans t3 ON t3.tID=X.tID
LEFT JOIN #matching m3 ON m3.transdate=Y.transdate AND m3.customercode=Y.customercode AND m3.amount=Y.amount
ORDER BY X.tID
I decided to post this, since it can be modified easily if the matching should be done on other conditions. The only thing I didn't try to achieve is sorting NULLs, so you have them at the beginning instead of at the end... we can address that later if this query works.
-
Hm.. but 2007-08-02 11:18:21.967 is GREATER than 2007-08-02 11:18:11.967 !! So that means matchID 2 should have no match, not matchID 3.
-
ohhhhhhhh im very sorry....
lets make (matchID 3) from '2007-08-02 11:18:11.967' to
2007-08-02 12:18:11.967' to correct the result.
Im very sorry vladan for the datas...
-
Well, if the problem was only in the desired result or in the sample data, then all is OK. Let me know whether my code works.
BTW, I forgot to post that I created temporary tables from you posted, by just adding "INTO ...." - like this:
select tbltransaction.*
into #trans
from (
select 1 tID, '2007-08-02' as transdate, 'OWNER-001' as ownercode, 'C-001' as customercode, 1000 as amount, null as matchID union all
select 2 tID, '2007-08-02' as transdate, 'OWNER-001' as ownercode, 'C-001' as customercode, 1000 as amount, null as matchID union all
select 3 tID, '2007-08-02' as transdate, 'OWNER-002' as ownercode, 'C-002' as customercode, 2000 as amount, null as matchID union all
select 4 tID, '2007-08-02' as transdate, 'OWNER-003' as ownercode, 'C-003' as customercode, 500 as amount, null as matchID union all
select 5 tID, '2007-08-02' as transdate, 'OWNER-004' as ownercode, 'C-004' as customercode, 1500 as amount, null as matchID union all
select 6 tID, '2007-08-02' as transdate, 'OWNER-005' as ownercode, 'C-005' as customercode, 2500 as amount, null as matchID
) as tblTransaction
select tblForMatching.*
into #matching
from (
select 1 as matchID, '2007-08-02 11:17:24.967' as transdate, 'C-001' as customercode, 1000 as amount union all -- match
select 2 as matchID, '2007-08-02 11:18:21.967' as transdate, 'C-001' as customercode, 1000 as amount union all -- match
select 3 as matchID, '2007-08-02 11:18:11.967' as transdate, 'C-001' as customercode, 1000 as amount union all -- no match
select 4 as matchID, '2007-08-02 11:19:22.967' as transdate, 'C-001' as customercode, 1000 as amount union all -- no match
select 5 as matchID, '2007-08-02 11:20:12.967' as transdate, 'C-002' as customercode, 2000 as amount union all -- match
select 6 as matchID, '2007-08-02 11:21:14.967' as transdate, 'C-004' as customercode, 1500 as amount union all -- match
select 7 as matchID, '2007-08-02 12:22:15.967' as transdate, 'C-001' as customercode, 1000 as amount union all -- no match
select 8 as matchID, '2007-08-02 12:23:34.967' as transdate, 'C-005' as customercode, 2500 as amount union all -- match
select 9 as matchID, '2007-08-02 12:23:38.967' as transdate, 'C-006' as customercode, 2500 as amount -- no match
) as tblForMatching
So that's how you get the tables I'm using in my query.
Let me know whether it works as expected, and if not, what is the problem.
-
hehehehe.. same guy...
anyway its been a headache for me...
Thanks for the help.
I greatly appreciated. -
Just being curious - which of the solutions did you choose, mine or Koji's? And which performs better on your real data?
-
WOW! Vladan your query is a little bit faster. I got a new query style for both of you guys. Both of your solution are good... But i will try make it readable... and more faster. Just 3-4 sec differential on on query time.
Thanks a lot for all of your shared knowledge.
-
You're welcome... mostly I only write comments when the query is finished, and I wasn't sure whether I understood the requirements or not, so I didn't comment it at all. If you need some explanations to it, let me know - but it should be more or less clear - first group the rows and rank those that belong to one group, then use the rank when joining both derived tables (X and Y), and fetch some additional info from the original tables.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply