Thanks for the sample tables and data. It would help if you also gave us what you wanted for the result. But I think you are looking for
;With cte As (Select lct.Scheme, lct.ProductName, lct.BINNumber, ct.TransactionTime, ct.CardNumber, ct.TransactionAmount, Row_Number() Over(Partition By ct.CardNumber Order By Len(lct.BINNumber) Desc) As rn From LookupCardType lct Inner Join CardTransactions ct On CharIndex(lct.BINNumber, ct.CardNumber) > 0) Select Scheme, ProductName, BINNumber, TransactionTime, CardNumber, TransactionAmount From cte Where rn = 1;
0 additional answers
Sort by: Most helpful
Match a value to a lookup table with a key of varying length
Sorry but this isn't easy to describe.
I'd like to find the Product for each card transaction. I want to search for the BINNumber that has the most digits matching the card number. In the sample data below, the transaction 1 would return product 1, transaction 2 would return product 2 and transaction 3 would return product 3.
CREATE TABLE LookupCardType ( Scheme varchar(10) NOT NULL, ProductName varchar(100) NOT NULL, BINNumber varchar(19) NOT NULL ); INSERT INTO LookupCardType( Scheme, ProductName, BINNumber ) VALUES( 'Mastercard', 'MasterCard Business to Business Credit', '6229340145' ), ( 'Mastercard', 'MasterCard Gold Debit', '622934' ), ( 'Mastercard', 'MasterCard Platinum Debit', '622' ); CREATE TABLE CardTransactions ( TransactionTime datetime NOT NULL, CardNumber varchar(19) NOT NULL, TransactionAmount decimal(10,2) NOT NULL ); INSERT INTO CardTransactions( TransactionTime, CardNumber, TransactionAmount ) VALUES( '2021-06-22 07:03:00.123', '6229340145630001', 23.99 ), ( '2021-06-22 07:13:44.023', '6229340045630002', 5.99 ), ( '2021-06-22 09:22:00.444', '6229330045630003', 275 );
Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.
Sign in to comment
I tried doing the above changes as a Reply to your request but clicking Submit didn't work when I had a section of Code embedded.