Hello,
I am stuck in one logic implementation of client requirement.
We have following data in table ::
id cust_nbr item_id list_nbr
---------------------------------------
1 A 10
2 A 11
3 A 12
4 B 10
5 B 11
6 B 12
7 C 11
8 C 12
9 C 13
10 C 14
Expected out put ::
id cust_nbr item_id list_nbr
---------------------------------------
1 A 10 1
2 A 11 1
3 A 12 1
4 B 10 1
5 B 11 1
6 B 12 1
7 C 11 2
8 C 12 2
9 C 13 2
10 C 14 2
We want to assign unique list number to all same item which are having different customer.
As you can see here item_id 10,11,12 is assigned to 2 customer(A,B) so for that list_nbr 1 is assigned to this 2 customer.
item_id 11,12,13,14 is assigned to 1 customer(C) only so list_nbr 2 is assigned to this customer.
I am not able to find any way to implement this change using sql.
This data is just an example. There will be thousands of record in this table.
Could you please help me to figure out this issue?
Thanks.