How to add rows in SQL Server

Please explain your used formulas. Cause they seem to be inconsistent. Why are you dividing the first four rows by 120? Where does the +300 comes from?

Much worse: Where do the values for A0004759217 come from? There are no UU rows..

The my normal approach would look like this:

DECLARE @Sample TABLE
    (
        [Opty ID] NVARCHAR(20) NULL ,
        [Product Category] NVARCHAR(50) NULL ,
        [Product] NVARCHAR(5) NULL ,
        [Value] FLOAT NULL
    );

INSERT INTO @Sample ( [Opty ID] ,
                      [Product Category] ,
                      [Product] ,
                      [Value] )
VALUES ( 'A0004764376', NULL, 'WE', 100 ) ,
       ( 'A0004764376', NULL, 'UU', 200 ) ,
       ( 'A0004764376', 'Hyper', 'AE', 120 ) ,
       ( 'A0004764376', 'Voyo', 'G4', 300 ) ,
       ( 'A0004759217', NULL, 'WE', 200 ) ,
       ( 'A0004759217', 'Edge', '7A', 400 );

WITH Filtered
AS ( SELECT * ,
            SUM(S.Value) OVER ( PARTITION BY S.[Opty ID] ) AS SumValue
     FROM   @Sample S
     WHERE  S.Product IN ( 'WE', 'UU' )
            AND S.[Product Category] IS NULL ) ,
     Remainder
AS ( SELECT S.[Opty ID] ,
            S.[Product Category] ,
            S.Product ,
            S.Value ,
            MIN(S.Value) OVER ( PARTITION BY S.Value ) AS MinValue
     FROM   @Sample S
     WHERE  NOT S.Product IN ( 'WE', 'UU' ))
SELECT F.[Opty ID] ,
       F.[Product Category] ,
       F.Product ,
       F.Value ,
       F.SumValue ,
       R.Value ,
       F.Value * ( R.Value / R.MinValue + F.SumValue )
FROM   Filtered F
       INNER JOIN Remainder R ON F.[Opty ID] = R.[Opty ID];

Select allOpen in new window

Sorry, i have reuploaded the excel file. I am dividing the rows to the sum 120+300.

So when i extract this data from a tool, i have ID's and for those ID's i have products. This A0004759217 is an ID that comes from a tool. The opty ID's dont have to include WE and UU. Some only have WE, some have only UU and some have both. I just gave you an example. But the thing is that wherev i have WE, UU or both I will need to calculate the value and Prod Category. I need to update the column Product Category and  Value columns for the lines that have Product category blank. Based on a business logic that was given to me, it was easy for me to populate the product category  for all the Product except the WE and UU. These 2 product have other logic to calculate the category and their value based on the rest of the Products that are included in the same Opty ID.

So for example i have the Opty ID=A0004759217. For this ID i have 3 Products: WE, 7A and AE. For 7A and AE I knew how to update the Product category that are inluded in that Opty ID and the value but for the WE i have to apply other logic. I have to make a sort of ratio that splits the total value of the WE by 7A and AE. The WE has the value=200 because this is how it comes from the tool but i have to split it in order for me to find the product categories based on 7A and AE.  So for WE i have to calculate the product category based first on 7A. (thats why i took the 7A's product category first).

I divided the 7A's value to the sum of 7A and AE and and after multiplied it with his initials value that is 200 like this: 200*(400/(400+70)).

First thing to do i suppose is to add those rows for the products that dont have a product category.
For the Opty ID=A0004764376 i have WE and UU for which i dont know how to calculate the Prod category so thats why i need to add a row in which i calculate the value based on the AE and another row based on G4. And so on.....
I always have to take into consideration only the Porducts that are included in the Opty ID.

I hope that now its nore understandable my problem,

Nope. Cause you only describe your Excel. But not really the formulas.

For solving this in T-SQL we need a closed formula based on sets.

Again, it is not clear, for example, why you used +70 in these cases: 200*(400/(400+70)) and 200*(70/(400+70)). What is the criteria for selecting that value?

i have  to divide each value of the products that are not included in (WE , UU) to the sum of products that are not included into (WE,UU).

so to calculate the value for WE  inlcuded into the Opty ID=A0004759217 based on the first product <>(WE, UU) which is 7A, i need to  make the sum of all the values of the products that are included into that opty id and different from WE and UU, divide the 7A value to that sum and multiply it with the actual value of WE. After i took the next product which is diiferent from WE and UU, and is AE and do the same thing. etc

is this helpful now?

see if something like this produced what you need?

(I'm using a temp table for testing)

create table #My_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)


;with cte as
(
	select * from #My_table
	Where [Product Category] <> ''
), cte2 as
(
	select a.[Opty ID], b.[Product Category], a.Product, cast( a.Value*(b.value /x.Value) as float) Value
	from #My_table a
	inner join cte b on a.[Opty ID] = b.[Opty ID]
	cross apply
	(
		Select sum(value) Value from #My_table Where [Product Category] <> '' and [Opty ID] = a.[Opty ID]
	) x
	Where a.[Product Category] = ''
)
Select a.* from cte a
union
Select b.* from cte2 b
order by 1

Select allOpen in new window

hmm, be a little bit more precise the next time:

DECLARE @Sample TABLE
    (
        [Opty ID] NVARCHAR(20) NULL ,
        [Product Category] NVARCHAR(50) NULL ,
        [Product] NVARCHAR(5) NULL ,
        [Value] FLOAT NULL
    );

INSERT INTO @Sample ( [Opty ID] ,
                      [Product Category] ,
                      [Product] ,
                      [Value] )
VALUES ( 'A0004764376', NULL, 'WE', 100 ) ,
       ( 'A0004764376', NULL, 'UU', 200 ) ,
       ( 'A0004764376', 'Hyper', 'AE', 120 ) ,
       ( 'A0004764376', 'Voyo', 'G4', 300 ) ,
       ( 'A0004759217', NULL, 'WE', 200 ) ,
       ( 'A0004759217', 'Edge', '7A', 400 ) ,
       ( 'A0004759217', 'Edge', '--', 70 );

WITH Filtered
AS ( SELECT *
     FROM   @Sample S
     WHERE  S.Product IN ( 'WE', 'UU' )) ,
     Remainder
AS ( SELECT * ,
            SUM(S.Value) OVER ( PARTITION BY S.[Opty ID] ) AS SumValue
     FROM   @Sample S
     WHERE  NOT S.Product IN ( 'WE', 'UU' ))
SELECT F.[Opty ID] ,
       R.[Product Category] ,
       F.Product ,
       F.Value * ( R.Value / R.SumValue ) AS Value
FROM   Filtered F
       INNER JOIN Remainder R ON F.[Opty ID] = R.[Opty ID]
UNION
SELECT R.[Opty ID] ,
       R.[Product Category] ,
       R.Product ,
       R.Value
FROM   Remainder R;

Select allOpen in new window

Thank you. It worked :)