Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Matching query (Already answered)

Author  Topic 

jonasalbert20
Constraint Violating Yak Guru

300 Posts
Posted - 2007-08-02 : 02:20:10
I have two tables: (1) tblTransaction (2) tblForMatching

tblTransaction 


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



tblForMatching


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





Any help will be greatly appreciated.






Want Philippines to become 1st World COuntry? Go for World War 3...

jonasalbert20
Constraint Violating Yak Guru

300 Posts
Posted - 2007-08-02 : 20:14:19
Seems I will be getting a hard time finding an idea.

Please help...






Want Philippines to become 1st World COuntry? Go for World War 3...
Go to Top of Page

Koji Matsumura
Posting Yak Master

141 Posts
Posted - 2007-08-03 : 03:21:13
There is no record in tblForMatching with customercode = 'C-005'.
Is this what you want?
Go to Top of Page

jonasalbert20
Constraint Violating Yak Guru

300 Posts
Posted - 2007-08-03 : 03:30:42
sorry wrong copy and paste please see again. tnx for the reply








Want Philippines to become 1st World COuntry? Go for World War 3...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-08-03 : 03:32:43
Search for other FIFO solutions here at SQLTeam.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Koji Matsumura
Posting Yak Master

141 Posts
Posted - 2007-08-03 : 03:40:05
How about 'C-003'?
Go to Top of Page

jonasalbert20
Constraint Violating Yak Guru

300 Posts
Posted - 2007-08-03 : 03:41:24
C-003 has no match ID




Want Philippines to become 1st World COuntry? Go for World War 3...
Go to Top of Page

Koji Matsumura
Posting Yak Master

141 Posts
Posted - 2007-08-03 : 04:34:30
is the following correct?
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

or matchID = 2?
Go to Top of Page

jonasalbert20
Constraint Violating Yak Guru

300 Posts
Posted - 2007-08-03 : 04:42:30
matchID is retained because it has no match in tbltransaction. It is already matched by top two(2) records of C-001 and 1000 amount from tblForMatching.

Want Philippines to become 1st World COuntry? Go for World War 3...
Go to Top of Page

Koji Matsumura
Posting Yak Master

141 Posts
Posted - 2007-08-03 : 04:42:31
If the above matchID = 2

Try

SELECT A.transdate, B.tID, B.ownercode, A.customercode, A.amount, A.matchID, A.transdate
FROM @tblForMatching A
LEFT OUTER JOIN @tbltransaction B ON DATEDIFF(dd, A.transdate, B.transdate) = 0 AND A.customercode = B.customercode AND A.amount = B.amount
AND
(
SELECT COUNT(*)
FROM @tbltransaction Z
WHERE Z.customercode = B.customercode AND Z.amount = B.amount AND Z.transdate = B.transdate AND Z.tID <= B.tID
)
=
(
SELECT COUNT(*)
FROM @tblForMatching Z
WHERE Z.customercode = A.customercode AND Z.amount = A.amount AND DATEDIFF(dd, Z.transdate, A.transdate) = 0 AND Z.transdate <= A.transdate
)
UNION
SELECT C.transdate, C.tID, C.ownercode, C.customercode, C.amount, NULL, NULL
FROM @tbltransaction C
WHERE NOT EXISTS
(
SELECT *
FROM @tblForMatching Z
WHERE DATEDIFF(dd, Z.transdate, C.transdate) = 0 AND Z.customercode = C.customercode AND Z.amount = C.amount
)
Go to Top of Page

jonasalbert20
Constraint Violating Yak Guru

300 Posts
Posted - 2007-08-03 : 05:04:48
WOW! Thank you very much my friend. Ive been having a headache trying to solve this by my own. I will study your approached.


Thanks again.




Want Philippines to become 1st World COuntry? Go for World War 3...
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site