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!
SaxitalisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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' )
0
SaxitalisAuthor Commented:
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!
0
Scott PletcherSenior DBACommented:
Sorry, you didn't state before that A or B could be missing.  Naturally I don't know your data or its "rules" :-).


UPDATE t1
SET
    Weight = CASE Type
        WHEN 'A' THEN t1.Weight + ( Weight_Type_A * 1.0 / ( Weight_Type_A + ISNULL(Weight_Type_B, 0) ) * Weight_Type_C )
        WHEN 'B' THEN t1.Weight + ( Weight_Type_B * 1.0 / ( ISNULL(Weight_Type_A, 0) + 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' )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SaxitalisAuthor Commented:
Sorry for delayed response - That is it - Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.