Performing Select Calculations on records with conditions

Hello Experts,

*** I posted this question previously: ***

I have a table that looks like this

ID      Batch   Type  Weight
1      1          A           2
2      1          B           3
3      1          C          10
4      1          D           4
5      1          E          12

I would like to perform calculations on specified fields based on the relationships between other fields for each batch.

For Example this is what I would like to do:
1.      Find the proportion of Type A and type B
2.      Apply those % to Type C
3.      Add Type A original weight  + the weight derived from step 2. And update Type A (Do the same for type B)
4.      Zero out Type C

1.      Find the proportion of Type A Weight and Type B Weight to Type A + Type B only

Total Type A + Type B weight: 2 + 3 = 5
% Type A = 2/5 = .4
% Type B = 3/5 = .6

2. (Type A % X Type C)  .4 * 10 = 4
(Type B % X Type C)  .6 * 10 = 6

3. New Type A weight:  2+4 = 6
New Type B weight: 3+6 = 9
4. Update Type C to = 0

ID      Batch   Type  Weight
1      1          A           6
2      1          B           9
3      1          C           0
4      1          D           4
5      1          E          12

Note. Only Types A, B and C should be altered for each Batch.
I obviously would like this to happen for all Batch records in the table.
Does anyone know how to do this?

Thanks!

*** scottpletcher gave this solution that works great except it always sets C to 0 (WHEN 'C' THEN 0
):***

UPDATE t1
SET
Weight = CASE Type
WHEN 'A' THEN t1.Weight + ( Weight_Type_A * 1.0 / ( Weight_Type_A + Weight_Type_B ) * Weight_Type_C )
WHEN 'B' THEN t1.Weight + ( Weight_Type_B * 1.0 / ( Weight_Type_A + Weight_Type_B ) * Weight_Type_C )
WHEN 'C' THEN 0
END
FROM dbo.table1 t1
INNER JOIN (
SELECT
Batch,
MAX(CASE WHEN Type = 'A' THEN Weight END) AS Weight_Type_A,
MAX(CASE WHEN Type = 'B' THEN Weight END) AS Weight_Type_B,
MAX(CASE WHEN Type = 'C' THEN Weight END) AS Weight_Type_C
FROM dbo.table1
WHERE
Type IN ( 'A', 'B', 'C' )
GROUP BY
Batch
HAVING
MAX(CASE WHEN Type = 'C' THEN Weight END) > 0
) AS t1_combined_values ON
t1_combined_values.Batch = t1.Batch AND
t1.Type IN ( 'A', 'B', 'C' )

****** Now I would like to get slightly different results ******
Now I would like ONLY set C = 0 on each batch ONLY when there are A and/or B records present for the same Batch.

In other words if table1 looks like this:

ID      Batch   Type  Weight
1      1          A           2
2      1          B           3
3      1          C           10
4      1          D           4
5      1          E          12
6      2          B           5
7      2          C           15
8      2          D           5
9      2          E          13
10    3          C           25
11    3          D           6
12    3          E          14

The Output should look like this:
ID      Batch   Type  Weight
1      1          A           9
2      1          B           6
3      1          C           0
4      1          D           4
5      1          E          12
6      2          B           20
7      2          C           0
8      2          D           5
9      2          E          13
10    3          C           25
11    3          D           6
12    3          E          14

Does Anyone know how to do this?
Are you out there ScottPletcher?

Thanks!
Scott Pletcher

UPDATE t1
SET
Weight = CASE Type
WHEN 'A' THEN t1.Weight + ( Weight_Type_A * 1.0 / ( Weight_Type_A + Weight_Type_B ) * Weight_Type_C )
WHEN 'B' THEN t1.Weight + ( Weight_Type_B * 1.0 / ( Weight_Type_A + Weight_Type_B ) * Weight_Type_C )
WHEN 'C' THEN CASE WHEN Weight_Type_A IS NULL AND Weight_Type_B IS NULL THEN Weight ELSE 0 END
END
FROM dbo.table1 t1
INNER JOIN (
SELECT
Batch,
MAX(CASE WHEN Type = 'A' THEN Weight END) AS Weight_Type_A,
MAX(CASE WHEN Type = 'B' THEN Weight END) AS Weight_Type_B,
MAX(CASE WHEN Type = 'C' THEN Weight END) AS Weight_Type_C
FROM dbo.table1
WHERE
Type IN ( 'A', 'B', 'C' )
GROUP BY
Batch
HAVING
MAX(CASE WHEN Type = 'C' THEN Weight END) > 0
) AS t1_combined_values ON
t1_combined_values.Batch = t1.Batch AND
t1.Type IN ( 'A', 'B', 'C' )

That is not quite it...

The Output is:

ID      Batch   Type  Weight
1      1          A          6
2      1          B          9
3      1          C           0
4      1          D           4
5      1          E          12
6      2          B           NULL
7      2          C           0
8      2          D           5
9      2          E          13
10    3          C           25
11    3          D           6
12    3          E          14

C does not zero out when A and B are not present but when A is not present and B IS present, B is set to NULL (BATCH 2). B should equal 20 here. (15 + 5)

Can anyone see how to do this?

Thanks!