Avatar of Saxitalis
Saxitalis 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!
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Saxitalis

8/22/2022 - Mon
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' )
ASKER
Saxitalis

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
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Saxitalis

Sorry for delayed response - That is it - Thanks!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes