Create a new record based on multiple records

Question

I have a table with Accounts that, if there are multiple matching ones, I need to combine to create a new one in a procedure.

The table looks like:

ACCT    ID      QTY     LEI
A_1     2       200     NULL
A_2     3       200     NULL
A_3     3       200     0
A_1     3       100     NULL
BB_1    2       200     NULL
BB_2    2       100     NULL
BB_3    3       200     0
BB_1    3       100     NULL

What I am trying to do is:

• Find the ones I need to combine based on ACCT; The data above basically has two ACCTS, A and BB, the "_" are just to identify them as individual sub accounts.
• For column QTY: SUM of QTY based on the ID and ACCT
• For column LEI: If any record in the group of ACCT and ID is 0 and rest are NULL then replace with 0, if all are NULL then NULL
• If there's only one record (no other record to merge with), that whole line will be used (see first record in table) .
• Create a new record based on the above, rename ACCT to _X and delete the existing records it has used

End result of the above looks like this:

ACCT    ID      QTY     LEI
A_X     2       200     NULL
A_X     3       500     0
BB_X    2       300     NULL
BB_X    3       300     0

Not sure what the best way of approaching this is, any ideas on this?

Answer 1

• use string operations (like SubString and IndexOf) to parse the name of the account
• use group by and aggregate functions (i.e. Sum) to calculate the results
• insert the results into a temp table, delete the original data, insert back into the original table