troubleshooting Question

Performing Select Calculations on records with conditions

Avatar of Saxitalis
Saxitalis 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.
Join the Community
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.
Try for 7 days

”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.

-Mike Kapnisakis, Warner Bros