Overmatching?

[wbodger]

If I have this data

[pre]
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
[/pre]

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

[pre]
ID ID
5 76
6 77
7 78
[/pre]

instead of

[pre]
ID ID
5 76
5 77
5 78
6 77
6 78
6 76
7 78
7 77
7 76
[/pre]

that I get with

select table1.Id,table2.Id
from table1
join table2
[indent]on table1.acct = table2.acct[/indent]
[indent]and table1.date = table2.date[/indent]
[indent]and table1.amount = table2.amount[/indent]

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

 

[simian336]

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

Simi

 

[simian336]

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

 

[Andrzejek]

You get 3 records with:

[pre]
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
[/pre]
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.

 

[simian336]

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

 

[wbodger]

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:

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

[pre]
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

[/pre]

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