asked
Use CASE Statement to Calculate Best Value of Separate Values
Hi there, I have a couple of tables, one acts as a product condition lookup while the other is a customer table containing a couple of values for a products condition and a column to record the overall condition of the customers product. I am trying to write a CASE statement that will update the overall condition column with the best possible condition of both values. For example the tables are: CREATE TABLE ConditionLookup( ConditionID INT, Condition VARCHAR(10)) INSERT INTO ConditionLookup SELECT 1,'RISK' UNION SELECT 2,'POOR' UNION SELECT 3,'REASONABLE' UNION SELECT 4,'NEW' CREATE TABLE CustomerCondition( ConditionID INT IDENTITY(1,1), CustID INT, Condition1 VARCHAR(10), Condition2 VARCHAR(10), OverallCondition VARCHAR(10)) INSERT INTO CustomerCondition(CustID,Condition1,Condition2) SELECT 2386,'NEW','NEW' UNION SELECT 2391,'REASONABLE','REASONABLE' UNION SELECT 2474,'REASONABLE','NULL' UNION SELECT 2546,NULL,'REASONABLE' UNION SELECT 2451,'POOR','RISK' UNION SELECT 2469,'REASONABLE','NEW' UNION SELECT 2345,NULL,NULL UNION SELECT 5432,'RISK','NEW' My brain is a bit fried and I know that there is a much more eloquent method to update this table but so far what I have doesn't use the lookup table to get the best values: UPDATE CustomerCondition SET OverallCondition = (SELECT CASE WHEN Condition1 = Condition2 THEN Condition2 WHEN Condition1 IS NULL OR Condition1 IN ('','null','n/a') THEN Condition2 WHEN Condition2 IS NULL OR Condition2 IN ('','null','n/a') THEN Condition1 ELSE 'Calculate best condition using ConditionLookup table' END FROM CustomerCondition cc WHERE cc.CustID = CustomerCondition.CustID) Any advise greatly appreciated. TIA
The column name ConditionID in the CustomerCondition table is pretty unfortunate, I am not sure why it is named like this when it does not really correspond to the ConditionID column of the lookup table. Also, because the Condition1 and Condition2 columns are character based, the join to the lookup table needs to be done by Condition rather than by more desirable ConditionID of the lookup. The solution below is based on the join of the CustomerCondition table with the lookup twice, once for each column. Once the ConditionID values for both columns are available, the case statement is used to simply pick the greater value. This greater value is the ID of the OverallCondition. However, because the OverallCondition needs to be the text, not the ID of the Condition from the lookup, the table is joined once again (outside of CTE now) so that the CustomerCondition table can be updated for all matching rows. It is OK to use inner join (rather than LEFT) in the update because if both conditions (Condition1 and Condition2) were null then there is no way to match them anyway and so there is no reason to update the row with NULL value in the OverallCondition back to NULL value. Here is the script: ;with records as ( select cc.ConditionID, cc.CustID, OverallCondition, -- the name Condition1 in cc table is unfortunate :) -- cc.Condition1, cc.Condition2, -- isnull(cl_1.ConditionID, 0) Condition1_ID, isnull(cl_2.ConditionID, 0) Condition2_ID, case when isnull(cl_1.ConditionID, 0) > isnull(cl_2.ConditionID, 0) then isnull(cl_1.ConditionID, 0) else isnull(cl_2.ConditionID, 0) end OverallConditionID from CustomerCondition cc left join ConditionLookup cl_1 on cc.Condition1 = cl_1.Condition left join ConditionLookup cl_2 on cc.Condition2 = cl_2.Condition ) update r set OverallCondition = cl.Condition from records r inner join ConditionLookup cl on r.OverallConditionID = cl.ConditionID; go Based on the sample data in question, this update affects 7 out of 8 rows in the table. The row with CustID = 2345 has NULL in both Condition1 and Condition2 and so cannot be matched from lookup. Other rows are updated based on the "best" value of the ConditionID of the lookup. After update is executed, selecting all from CustomerCondition produces the following results: ConditionID CustID Condition1 Condition2 OverallCondition ----------- ----------- ---------- ---------- ---------------- 1 2345 NULL NULL NULL 2 2386 NEW NEW NEW 3 2391 REASONABLE REASONABLE REASONABLE 4 2451 POOR RISK POOR 5 2469 REASONABLE NEW NEW 6 2474 REASONABLE NULL REASONABLE 7 2546 NULL REASONABLE REASONABLE 8 5432 RISK NEW NEW Hope this helps. Oleg
@Oleg yesterday my brain was fried and today it is blown away. It's literally amazing what you can script, so many thanks. Apologies for the bad example data. I always try to illustrate a quick and simple test example of the issue I am trying to solve. Have a great weekend and thanks again. :)
@Oleg I am wondering if I can seek your advice again on the above SQL? The above executes fantastic for 2 columns however I've a new requirement to calculate the worst condition across 12 columns, and no matter what I try I cannot get the SQL to successfully update all rows. :( For example, in my new sample data it only updates 3 out of the 8 rows. I don't know if I should post in here or start a new question regarding this? Any pointers would be greatly appreciated.
@Oleg I've just worked out a great solution to my problem so no worries. I'm amazed I manged to work this out and it works great across multiple columns. I'm posting the SQL here for others. :) SELECT x.*, CASE WHEN x.[Lowest Rating] = 1 THEN 'Fail' WHEN x.[Lowest Rating] = 2 THEN 'Investigate' WHEN x.[Lowest Rating] = 3 THEN 'Pass' END AS [Lowest Rating] FROM ( SELECT *, (SELECT MIN(CASE WHEN v = 'Fail' THEN 1 WHEN v = 'Investigate' THEN 2 WHEN v = 'Pass' THEN 3 END) FROM (VALUES (Condition1), (Condition2), (Condition3), (Condition4), (Condition5), (Condition6), (Condition7), (Condition8), (Condition9), (Condition10), (Condition11), (Condition12)) AS value(v)) AS [Lowest Rating] FROM CustomerCondition) x
18 People are following this question.
