Link to home
Start Free TrialLog in
Avatar of John Porter
John PorterFlag for United States of America

asked on

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!
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

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' )
Avatar of John Porter

ASKER

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!
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry for delayed response - That is it - Thanks!