John Porter
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!
*** 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!
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for delayed response - That is it - Thanks!
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' )