INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Overmatching?

Overmatching?

(OP)
If I have this data

ID	Acct	Date		Amount	TranCode	ID	Acct	Date		Amount	TranCodeType
5	123	7/5/2017	10000	18		76	123	7/5/2017	10000	12
6	123	7/5/2017	10000	18		77	123	7/5/2017	10000	12
7	123	7/5/2017	10000	18		78	123	7/5/2017	10000	12
 

between two tables (with ID being the first field in each table) and I join the two tables on the data points that I have available, then I end up with 9 items instead of 3 matching records. Given the lack of unique identifiers, how would you get

ID	ID
5	76
6	77
7	78
 

instead of

ID	ID
5	76
5	77
5	78
6	77
6	78
6	76
7	78
7	77
7	76
 

that I get with

CODE

select table1.Id,table2.Id
from table1
join table2
on table1.acct = table2.acct
and table1.date = table2.date
and table1.amount = table2.amount

I keep thinking I must be missing something, but I cannot see it. Thanks for your help!
Willie

RE: Overmatching?

So what determines the numbers you want (76,77,78)? The fact that they are the first (top) record for each table1 id?

Simi

RE: Overmatching?

I just re-looked at that

so is this table one

ID Acct Date Amount TranCode

5 123 7/5/2017 10000 18
6 123 7/5/2017 10000 18
7 123 7/5/2017 10000 18

And this is table2?
ID Acct Date Amount TranCodeType
76 123 7/5/2017 10000 12
77 123 7/5/2017 10000 12
78 123 7/5/2017 10000 12

Then they should not match at all with those criteria....

Simi

RE: Overmatching?

You get 3 records with:

select table1.acct, table1.date, table1.amount
from table1
join table2
 on table1.acct   = table2.acct
and table1.date   = table2.date
and table1.amount = table2.amount
 
because you ignore ID

If you include ID, you get 3 x 3 = 9 records

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Overmatching?

You need to have a pair of matching identifiers. However if all of your data is this clean you could do

select table1.acct, table1.date, table1.amount
from table1
join table2
on table1.acct = (table2.acct - 71)

Simi

RE: Overmatching?

(OP)
Sorry, the data is not that clean. Not strictly. What I have is a table with Core data and a table with detail data. I am matching detail to core so that I can move to history. However, there are a few issues that come in to play, one of them being that I may have 3 transactions in the core for the same acct, for the same amount with the same code within the same hour (and I only receive day and hour). Then, in the detail I have three transactions that match that acct, that amount and that code for that same day. (This data is coming from different sources, but all apply to the same acct, so I cannot get anything different that will help with uniqueness). So, if I match on the fields that I know that I can with an inner join, then I get nine matches because #1 in the core will match all three in the detail as will #2 and #3 in the core. So, aside from making the first match, copying the data to history and then deleting the copied child and core transactions (one of each) and then re-running the matching query again, is there a more efficient way to do this? Here is my current stored procedure code:

CODE

BEGIN

DECLARE @CurrCoreId INT, @CurrChildId INT, @transactId INT, @BundleId nvarchar(255)
DECLARE @MainTransactionId INT, @ChildId INT, @RowCount INT

Create table #CoreProcess
	(	
		CoreRawId int
		,ChildTransactionRawId int
		,TxCode int
		,EntityType_ImportFileTypeId int
		,BundleId nvarchar(255)
		,rnk int
	)

INSERT INTO #CoreProcess

	SELECT
		core.CoreRawId
		,child.ChildTransactionRawId
		,core.TxCode
		,child.EntityType_ImportFileTypeId
		,child.BundleId
		,DENSE_RANK() OVER (ORDER BY core.CoreRawId) as rnk
	FROM dbo.CIB_CoreRaw core
	JOIN dbo.[CIB_ChildTransactionRaw] child
		ON dbo.fn_DecryptByPassPhrase(core.[BaseAccountNumber]) = dbo.fn_DecryptByPassPhrase([child].[BaseAccountNumber])
			AND core.BankISN = child.BankISN
	JOIN dbo.CIB_TransactionCode code
		ON core.TXCode = code.TxCode
			AND child.EntityType_ImportFileTypeId = code.EntityType_ImportFileTypeID
			AND code.HasChildData = 1
	WHERE (core.Credit = child.Amount OR core.Debit = child.Amount)
	AND child.Amount > 0
	AND core.BankISN = @BankISN
	AND core.PacketDate = CONVERT(DATE, child.TransactionDate)

Declare @i Int,
        @max int;

SELECT @i = 1,@max = MAX(rnk)
FROM #CoreProcess

WHILE @i < @max

BEGIN

	SELECT @CurrCoreId = CoreRawId
		,@CurrChildId = ChildTransactionRawId
		,@BundleId = BundleId
	FROM #CoreProcess
	WHERE rnk = @i;

	EXEC @transactId = dbo.CIB_CopyCoreToHistoryById @CurrCoreId,-1 --this deletes the core record after copying it over to history

	IF (LEN(@BundleId) < 1)
	BEGIN
	EXEC dbo.CIB_CopyChildRawToHistoryById	@transactId,@CurrChildId --this deletes the child record after copying it over to history
	--DELETE FROM #CoreProcess WHERE CoreRawId = @CurrCoreId
	END

	--if bundle is not null, then need to add those child items as well..
	IF (LEN(@BundleId) > 1)
	BEGIN
	EXEC dbo.CIB_CopyChildRawToHistoryByBundleId	@transactId,@BundleId --this deletes the child record after copying it over to history
	--DELETE FROM #CoreProcess WHERE BundleId = @BundleId
	END

SET @i = @i + 1;
END

DROP TABLE #CoreProcess

END 

and a sampling of data illustrating the issue

CoreRawId	ChildTransactionRawId	TxCode	EntityType_ImportFileTypeId	BundleId	rnk
26320		260913			195	117				NULL		17103
26320		260914			195	117				NULL		17103
26321		260916			195	117				NULL		17104
26322		260914			195	117				NULL		17105
26322		260913			195	117				NULL		17105
26323		260919			195	117				NULL		17106
26323		260918			195	117				NULL		17106
26323		260917			195	117				NULL		17106
26323		260920			195	117				NULL		17106
26324		260901			195	117				NULL		17107
26325		260896			195	117				NULL		17108
26326		260897			195	117				NULL		17109
26327		260892			195	117				NULL		17110
26328		260904			195	117				NULL		17111
26329		260900			195	117				NULL		17112
26330		260886			195	117				NULL		17113
26331		260911			195	117				NULL		17114
26332		260909			195	117				NULL		17115
26333		260908			195	117				NULL		17116
26334		260891			195	117				NULL		17117
26335		260893			195	117				NULL		17118
26336		260910			195	117				NULL		17119
26337		260906			195	117				NULL		17120
26338		260905			195	117				NULL		17121
26343		261607			408	120	3000000178/24/2017d6e6c8a19dc241f487f4ceb201b282fb	17122
26343		261608			408	120	3000000178/24/2017d6e6c8a19dc241f487f4ceb201b282fb	17122
26343		261609			408	120	3000000178/24/2017d6e6c8a19dc241f487f4ceb201b282fb	17122
26343		261610			408	120	3000000178/24/2017d6e6c8a19dc241f487f4ceb201b282fb	17122
26343		261615			408	120	3000000178/24/2017d6e6c8a19dc241f487f4ceb201b282fb	17122
26343		261616			408	120	3000000178/24/2017d6e6c8a19dc241f487f4ceb201b282fb	17122

 

you can see in this example that, for instance 26320 matches 260913 and 260914, but 26322 also matches both 260913 and 260914. What I want is for 26320 to take one (let's say 260913) and 26322 to take the other (which would be 2609+14 in this case). I know that I can go head and re-run the query after moving the first matched records each time, which would drop the duplicates, but if I have 35k records in the core and 350k records in the detail, that is more cumbersome than I would like to be...

Thanks for your thoughts,
Willie

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close