0

I've been trying to figure this out for hours and can't seem to get it. If it can be done, any help would be greatly appreciated! I hope I'm clear with what I'm asking :-)

Consider the following:

Table A consists of lastname (null), firstname(null), merchant, amount, transdate, reference; with only unique field value being reference. The merchant, amounts and transdate can have similar duplicated values.

example:

Row 1:  {Null},{Null},"BLANK-ROSTER NUM","34.30","2018-01-01","12345" 
Row 2:  {Null},{Null},"BLANK-ROSTER NUM","34.30","2018-01-01","77777" 
Row 3:  {Null},{Null},"BLANK-ROSTER NUM","16.07","2018-12-24","99999" 
Row 4:  {Null},{Null},"BLANK-ROSTER NUM","16.07","2018-12-24","44444" 
Row 5:  {Null},{Null},"BLANK-ROSTER NUM","34.30","2018-01-01","66666" 

The other table has the lastname and firstname values I want to assign to each line, similar merchant,amount and transdate values throughout, but no reference field.

example:

Row 1:  SMITH,JOHN,"BLANK-ROSTER NUM","34.30","2018-01-01"
Row 2:  JONES,JAMES,"BLANK-ROSTER NUM","34.30","2018-01-01"
Row 3:  MILLER,STEVE,"BLANK-ROSTER NUM","16.07","2018-12-24"
Row 4:  WILSON,JACK,"BLANK-ROSTER NUM","34.30","2018-01-01"
Row 5:  DAVIS,BEN,"BLANK-ROSTER NUM","16.07","2018-12-24"

Is there a way to get names updated into the similar rows in TABLE A? It doesn't matter which goes to which so long as one name doesn't populate all similar rows in TABLE A (which is what happens to me). Any row with same merchant, amount, and date can update to any row in Table A that has same merchant, amount, and date. Avoiding the duplication of data is my issue.

Is it possible to get in TABLE A the names to distinctly assign to a row?

UPDATED TABLE A after assigning names from rows with same merchant, amount, and date distinctly:

Row 1:  "SMITH", "JOHN","BLANK-ROSTER NUM","34.30","2018-01-01","12345" 
Row 2:  "JONES","JAMES","BLANK-ROSTER NUM","34.30","2018-01-01","77777" 
Row 3:  "MILLER","STEVE","BLANK-ROSTER NUM","16.07","2018-12-24","99999" 
Row 4:  "DAVIS","BEN","BLANK-ROSTER NUM","16.07","2018-12-24","44444" 
Row 5:  "WILSON","JACK","BLANK-ROSTER NUM","34.30","2018-01-01","66666" 

//////////Edited to include Table Def using SQL Server 2012

Table A:

 INSERT INTO [Recon2018].[dbo].[RECONReport]
       ([Employee - Last]
       ,[Employee - First]
       ,[MI]
       ,[Posted Amount]
       ,[Merchant]
       ,[Reference]
       ,[transdate])
 VALUES
       (<Employee - Last, nvarchar(50),>
       ,<Employee - First, nvarchar(255),>
       ,<MI, nvarchar(255),>
       ,<Posted Amount, decimal(18,2),>
       ,<Merchant, nvarchar(50),>
       ,<Reference, nvarchar(50),>
       ,<transdate, datetime2(0),>)

TABLE B:

 INSERT INTO [Recon2018].[dbo].[Summary_Final]
       ([Employee Last Name]
       ,[Employee First Name]
       ,[Employee Middle Initial]
       ,[Merchant]
       ,[CLIN Rate (Fee)]
       ,[Fee Transaction Date])
 VALUES
       (<Employee Last Name, nvarchar(max),>
       ,<Employee First Name, nvarchar(max),>
       ,<Employee Middle Initial, nvarchar(255),>
       ,<Merchant, nvarchar(max),>
       ,<CLIN Rate (Fee), float,>
       ,<Fee Transaction Date, datetime2(0),>)
  • Please update your question to include table definitions and sample data in the form of INSERT statements (so we don't have to type up the data). Also, please specify your RDBMS (SQL Server, Oracle, etc.) – Scott Hodgin Aug 28 '18 at 18:09
  • Thanks Scott. I did update to include two defs using SQL 2012. Hope that helps. – JonInVa Aug 28 '18 at 18:32
  • It should be possible to make a solution using RANK() and the modulo operator (%). Don't have the time to expand to a full answer at this time, so I'll toss it out for someone else to use. – RDFozz Aug 28 '18 at 19:19
0

This solution seems to work against your sample data.

--Demo setup
Declare @TableA table
    (FirstName varchar(6), LastName varchar(6), Merchant varchar(16), Amount decimal(11,2), TransDate varchar(10), Reference int)
;

INSERT INTO @TableA
    (FirstName, LastName, Merchant, Amount, TransDate, Reference)
VALUES
    ('{Null}', '{Null}', 'BLANK-ROSTER NUM', 34.30, '2018-01-01', 12345),
    ('{Null}', '{Null}', 'BLANK-ROSTER NUM', 34.30, '2018-01-01', 77777),
    ('{Null}', '{Null}', 'BLANK-ROSTER NUM', 16.07, '2018-12-24', 99999),
    ('{Null}', '{Null}', 'BLANK-ROSTER NUM', 16.07, '2018-12-24', 44444),
    ('{Null}', '{Null}', 'BLANK-ROSTER NUM', 34.30, '2018-01-01', 66666)
;

Declare @TableB table
    (FirstName varchar(6), LastName varchar(6), Merchant varchar(16), Amount decimal(11,2), TransDate varchar(10))
;
INSERT INTO @TableB
    (FirstName, LastName, Merchant, Amount, TransDate)
VALUES
    ('SMITH', 'JOHN', 'BLANK-ROSTER NUM', 34.30, '2018-01-01'),
    ('JONES', 'JAMES', 'BLANK-ROSTER NUM', 34.30, '2018-01-01'),
    ('MILLER', 'STEVE', 'BLANK-ROSTER NUM', 16.07, '2018-12-24'),
    ('WILSON', 'JACK', 'BLANK-ROSTER NUM', 34.30, '2018-01-01'),
    ('DAVIS', 'BEN', 'BLANK-ROSTER NUM', 16.07, '2018-12-24')
;

--The solution
;

--Common table expressions to put a row number on each row
--partition by merchant, amount and transdata
WITH TableARowNumbers
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY Merchant
            ,Amount
            ,TransDate ORDER BY Merchant
                ,Amount
                ,TransDate
            ) AS rn
    FROM @TableA
    )
    ,TableBRowNumbers
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY Merchant
            ,Amount
            ,TransDate ORDER BY Merchant
                ,Amount
                ,TransDate
            ) AS rn
    FROM @TableB
    )

--Update TableA joining on Merchant, Amount, TransDate and rn
UPDATE ta
SET ta.FirstName = tb.FirstName
    ,ta.LastName = tb.LastName
FROM TableARowNumbers ta
JOIN TableBRowNumbers tb
    ON tb.Merchant = ta.Merchant
        AND tb.Amount = ta.Amount
        AND tb.TransDate = ta.TransDate
        AND tb.rn = ta.rn

SELECT *
FROM @TableA

| FirstName | LastName | Merchant         | Amount | TransDate  | Reference |
|-----------|----------|------------------|--------|------------|-----------|
| SMITH     | JOHN     | BLANK-ROSTER NUM | 34.30  | 2018-01-01 | 12345     |
| JONES     | JAMES    | BLANK-ROSTER NUM | 34.30  | 2018-01-01 | 77777     |
| MILLER    | STEVE    | BLANK-ROSTER NUM | 16.07  | 2018-12-24 | 99999     |
| DAVIS     | BEN      | BLANK-ROSTER NUM | 16.07  | 2018-12-24 | 44444     |
| WILSON    | JACK     | BLANK-ROSTER NUM | 34.30  | 2018-01-01 | 66666     |
  • Wow Scott. That is exactly what I needed and after modifying to use my actual tables and fields, it updated my "TABLE A" like a champ! You are amazing! Thank you for your prompt and extremely valuable response! – JonInVa Aug 28 '18 at 19:59
  • There is no "internal" order for the rows and we can not expect that two run of your code produce the same result. You have joined tables in some random and unpredictable way that violate the consistency of data. – Kondybas Aug 29 '18 at 8:00
  • @JonInVa - you’re welcome! – Scott Hodgin Aug 29 '18 at 8:21
  • @Kondybas - if you read the question, you'll find the OP's doesn't care about the query being unpredictable on successive runs - they simply do not want the SAME First/Last name assigned to every row with the same merchant, amount and TransDate. Therefore, it doesn't matter that John Smith is assigned to reference 12345 on run 1 and James Smith is assigned on a subsequent run. The OP just doesn't want John Smith assigned to all rows for the same merchant, amount and TransDate. – Scott Hodgin Aug 29 '18 at 8:50
0

This is impossible.

+------+------+------------------+-------+------------+-------+
| Null | Null | BLANK-ROSTER NUM | 34.30 | 2018-01-01 | 12345 |
| Null | Null | BLANK-ROSTER NUM | 34.30 | 2018-01-01 | 77777 |
| Null | Null | BLANK-ROSTER NUM | 34.30 | 2018-01-01 | 66666 |
+------+------+------------------+-------+------------+-------+

can't be updated from

+--------+-------+------------------+-------+------------+
| SMITH  | JOHN  | BLANK-ROSTER NUM | 34.30 | 2018-01-01 |
| JONES  | JAMES | BLANK-ROSTER NUM | 34.30 | 2018-01-01 |
| WILSON | JACK  | BLANK-ROSTER NUM | 34.30 | 2018-01-01 |
+--------+-------+------------------+-------+------------+

There is absolutely no reason to choose Jack Wilson among other persons for the row having 66666 in the last column. Also there is no visible sorting for both tables so we can't use it somehow.

  • Thanks. That's what I was trying to find out. So looking at it another way, there is no way to get the unique "reference", e.g. 12345 onto a single row in Table B as a tmp table in order to then update Table A from that tmp table? I can get all unique rows from TABLE B using a distinct querey with JOIN on Table A on all those common values but can't get those names in those distinct rows into TABLE A. I guess I'm out of luck then :-). Thanks for replying though! – JonInVa Aug 28 '18 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.