troubleshooting Question

# Performing Select Calculations on records with conditions

asked on
Microsoft SQL Server 2005
4 Comments1 Solution216 ViewsLast Modified:
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!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
###### Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.