Identify rows containing repeating customers and assigning them new id as per serial number generated in sql

user_18264651 0
Viewed 42

I have a table

id repeat customer id store date
1 A 07-19-22
2 A 07-19-22
3 A 07-19-22
id repeat customer id store date
1 B 07-19-22
2 B 07-19-22
3 1 B 07-19-22
4 B 07-19-22

and more tables from other store

The problem here is

  1. all stores start with id 1
  2. repeat customer have new id in id column and their original id is retained in repeat customer id column

I have to concatenated all the tables and also keep track of repeating customer for analytics. I have joined all tables using UNION ALL and also created a dummy id column using SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NEW_ID, * FROM CTE, but I have no clue how to capture and assign value to repeat customer id such that I get the table as below

NEW_ID id new_repeat_customer_id repeat customer id store date
1 1 A 07-19-22
2 2 A 07-19-22
3 3 A 07-19-22
4 1 B 07-19-22
5 2 B 07-19-22
6 3 4 1 B 07-19-22
7 4 B 07-19-22
sql-server sql
1 Answers

The best way to incorporate it, would be to use Alphanumeric String as NEW_ID, and concat STORE and ID to create NEW_ID. For example A_000000001. In that way you can add similar STORE to REPEAT_CUSTOMER_ID as well.

So in this case, instead of NEW_ID=6, you would have NEW_ID=B_000000003 and REPEAT_CUSTOMER_ID would become B_000000001.

But in case that is not possible, you can use query like below to get the output

DB Fiddle Query

with CTE as
(
select * from STORE1 
UNION ALL
select * from STORE2
)

,CTE2 as
(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NEW_ID,t.* from CTE t)

,REPEAT_ID as 
(select NEW_ID,ID,REPEAT_CUSTOMER_ID,STORE from CTE2 where REPEAT_CUSTOMER_ID is not null)

,REPEACT_CUSTOMER_ID as 
(select c.NEW_ID as NEW_REPEAT_CUSTOMER_ID,r.NEW_ID
from REPEAT_ID r
left join CTE2 c
on c.ID=r.REPEAT_CUSTOMER_ID and c.STORE=r.STORE
)

select c.* , n.NEW_REPEAT_CUSTOMER_ID
from CTE2 c
left join REPEACT_CUSTOMER_ID n
on c.NEW_ID=n.NEW_ID

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=cbe63994b10f9e3b0eff53b0c89d463a

SO basically you have to separate rows where REPEATE customer is present and join it with main table query.

user_52343340
Related