Updating / Inserting records

[AlterIT]

I am trying to insert/update records into a table using insert/update logic.

Background: Base table has customer records for each state and all of them have a rank within the state. I am trying to insert new customer records to each state with a rank higher than maximum rank within each state or move customer from one state to another with the same rank logic.

As you could see, when customer 101 moves from OH to CA it got rank 1 since CA does not have any records, at the same time when another customer (201) got added to CA it got rank 2 since rank 1 is already occupied by customer 101. 

Similarly for customer 112, it got added to OH and since its max rank is 10, this new customer will get a rank of 11 and so on.

What I tried: I have designated customer ID as primary key in the base table and tried to load the Update/Insert records using insert/update logic. However, I am unable to find a logic to get the Rank working. I have attached the data file I am using as well. Thank you

Base Table

Customer ID	State	Region	Rank	Revenue	Margin	Profit
101	OH	East	1	1400	10	140
102	OH	East	2	1250	15	188
103	OH	East	3	500	8	400
104	OH	East	4	600	8	40
105	OH	East	5	900	12	100
106	OH	East	6	850	15	80
107	OH	East	7	7000	10	700
108	OH	East	8	6300	20	1200
109	OH	East	9	3300	12	300
110	OH	East	10	3100	11	320

Update/Insert Records

Customer ID	State	Region
101	CA	West
201	CA	West
112	OH	East

Desired State (Expected Output)

Customer ID	State	Region	Rank	Revenue	Margin	Profit	Comment
102	OH	East	2	1250	15	188
103	OH	East	3	500	8	400
104	OH	East	4	600	8	40
105	OH	East	5	900	12	100
106	OH	East	6	850	15	80
107	OH	East	7	7000	10	700
108	OH	East	8	6300	20	1200
109	OH	East	9	3300	12	300
110	OH	East	10	3100	11	320
112	OH	East	11				New Customer added to  OH
101	CA	West	1	1400	10	140	Customer moved from OH to CA
201	CA	West	2				New Customer added to CA

[Yoshiro_Fujimori]

@AlterIT ,

I hope this works for your case.