How to loop and merge data from one table to another

Question

Need a SQL query logic to loop and merge data from [dba].[Table].[City] into [dba].[Table].[Clients] to get the desired output.

[dba].[Table].[City] has the allocation % with city name
[dba].[Table].[Clients] has the client list with area, country name and Plan. City is defaulted to unassigned
Desired output is to get the City breakup for each record with Plan$ allocated based on the allocation % available in [dba].[Table].[City]

Answer 1

The following will include the Unassigned results when the percentages do not add to 100.

Create Table City(Area varchar(20), Country varchar(20), City varchar(20), Allocation int);  
Insert City Values  
('America', 'USA', 'New York', 65)  
,('America', 'USA', 'Los Angelos', 35)  
,('America', 'Mexico', 'Mexico City', 100)  
,('Europe', 'Italy', 'Rome', 75)  
,('Europe', 'France', 'Paris', 25);  
Create Table Clients(Client char(1), Area varchar(20), Country varchar(20), City varchar(20), [Plan] decimal(9,2));  
Insert Clients Values  
('A', 'America', 'USA', 'Unassigned', 10000)  
,('A', 'America', 'Mexico', 'Unassigned', 5000)  
,('A', 'Europe', 'Italy', 'Unassigned', 5000)  
,('A', 'Europe', 'France', 'Unassigned', 10000);  
  
;With cte As  
(Select cl.Client, cl.Area, cl.Country, 'Unassigned' As City, 100 - Sum(c.Allocation) As Allocation, Cast((cl.[Plan] * (100 - Sum(Allocation)))/100.00 As decimal(9,2)) as [Plan $]   
From Clients cl   
INNER JOIN city c on cl.Area = c.Area and cl.Country = c.Country  
Group By cl.Client, cl.Area, cl.Country, cl.[Plan]  
Union All  
Select cl.Client, cl.Area, cl.Country, c.City, c.Allocation, Cast((cl.[Plan] * Allocation)/100.00 As decimal(9,2)) as [Plan $]   
From Clients cl   
INNER JOIN city c on cl.Area = c.Area and cl.Country = c.Country)  
Select Client, Area, Country, City, Allocation, [Plan $]   
From cte  
Where City <> 'Unassigned' Or [Plan $] <> 0  
Order By Client, Area, Country, City;  
  

P.S., when asking a question, please give us your sample data in the form a CREATE TABLE (or DECLARE TABLE) statements and INSERT statements (as I did in the answer above). Do not give us the sample data as images. Giving us the result you want as an image is fine.

Tom

Answer 2

Select cl.Client, cl.Area, cl.Country, c.City, c.Allocation, (c.Plan * Allocation)/100.00 as [Plan $] from Clients cl INNER JOIN city c on cl.Area = c.Area and cl.Country = c.Country