How to add rows

Transact-SQL
1

Hello guys,

I struggle with a problem that i try to solve but after a couple of attempts i realised that nothing works properly and i dont have the necessary skills to do it. I am not using SQL frequently so i couldnt improve my skills. Maybe someone who is more advanced and likes this tool can help me.
In this picture i have the first table which i need to turn it to the second table

image
image.png737×220 5.36 KB

I need to multiply the rows based only on two teritory ID's : A_A and B_B. But in order for me to do that i need to do some calculation and also i need to take into consideration the country.
for example: i have Albania who has 4 teritory ID's. The only rows that i need to add is for teriory ID A_A and B_B.
To calculate the values for these 2 teritory ID, i need take into consideration only the sum of the rest of the teritory ID's that dont incluse A_A or B_B. I put the formula in the "Value" column. These are sort of ratios to split the A_A and B_B between the rest of the ID's. Also i need to rename those 2 ID's to have at the end the naming of teh ID's based on which i calculated their final values.

I really hope that someone could give me an idea.
thank you

2

Need usable sample data, that is, a CREATE TABLE and INSERT statements rather than a picture of data. The picture's ok to explain, but not for us to create SQL code for you.

3

Ah ok sorry for that, i thought that giving a simpler example would be easier to understand. My real data is different from what i showed above. I wilMyl give an example of what i really have and explain again:

create table My_table
( [Opty ID] nvarchar (20) null,
[Product Category] nvarcar (50) null,
[Product] nvarchar (5) null,
[Value] float null)

insert into My_table ( [Opty ID],[Product Category],[Product],[Value])
values
("A0004764376", ' ',"WE",100),
("A0004764376",' ', "UU", 200),
("A0004764376","Hyper", "AE", 120),
("A0004764376","Voyo", "G4", 300),
("A0004759217", ' ', "WE", 200),
("A0004759217", "Edge","7A", 400)

So, i need to multiply the rows based only the Products in (WE and UU). I need to take also in consideration the Opry ID to make those calculations.
So for the ID=A0004764376, it has 4 type of products but i need to add rows only for the products = WE and UU based on the other products (Hyper and Voyo). To calcuate the value of these 2 peoducts that are mandatory for me, i need to calculate the sum of the rest of the products that are included only in thisn Opty ID.

image
image.png906×236 7.7 KB

the product category of the blank lines should be filled with the product category of teh value that divides teh sum. For example where the Product is WE, in the first line i populated with Hyper because the value of the Hyper=120 divides the sum between the Hyper and Voyo.

I hope that i explained better this time :slight_smile:

4 

Declare  @My_table as table
( [Opty ID] nvarchar (20) null,
[Product Category] nvarchar (50) null,
[Product] nvarchar (5) null,
[Value] float null)

insert into @My_table ( [Opty ID],[Product Category],[Product],[Value])
values
('A0004764376', ' ','WE',100),
('A0004764376',' ', 'UU', 200),
('A0004764376','Hyper', 'AE', 120),
('A0004764376','Voyo', 'G4', 300),
('A0004759217', ' ', 'WE', 200),
('A0004759217', 'Edge','7A', 400),
('A0004759217', 'Hyper','AE', 70)
;


select a.[Opty ID] 
,bTable.[Product Category] 
,a.Product  
, a.Value * (bTable.Value/(


select sum(c.Value)
from @My_table c
where c.[Opty ID] = a.[Opty ID] 
and Rtrim(c.[Product Category]) <> ''


)) [value]

from @My_table a
outer apply
(
select b.[Product Category] , b.Value
from @My_table b
where b.[Opty ID] = a.[Opty ID] 
and Rtrim(b.[Product Category]) <> ''


) bTable

where Rtrim(a.[Product Category]) = ''

union all

select *
from @My_table a
where Rtrim(a.[Product Category]) <> ''



order by 1;
1 Like
5

thank you