Case Statement query help

Question

Is it possible to use case more than once in an expression?
I have a query that calculates the cost of insurance.
but i need to see if i can add a *Case statement to my query. like so -----(SUM(ROUND(mnyInsurance,2)*CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END),0) Insurance

SELECT
'Cost of Insurance' AS strType,
ISNULL(SUM(CASE WHEN dtReport = @EndDate THEN ROUND(mnyInsurance,2) ELSE 0 END),0) AS intDT,
ISNULL(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN ROUND(mnyInsurance,2) ELSE 0 END),0) AS intMTD,
ISNUll(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN ROUND(mnyInsurance,2) ELSE 0 END),0) AS intQTD,
IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN ROUND(mnyInsurance,2) ELSE 0 END),0) AS intYTD,
-----(SUM(ROUND(mnyInsurance,2)*CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END),0) Insurance

3 as intOrder
FROM
tblUBMReport WITH (NOLOCK)
LEFT JOIN tblUBMInsuranceShare ON tblUBMInsuranceShare.intProp = tblUBMReport.intProp
INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
WHERE
intProp IN (@Props)

Answer 1

Hi @Jannette Jones

if i can add a *Case statement to my query. like so -----(SUM(ROUND(mnyInsurance,2)*CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END),0) Insurance

Yes, you can use Case When statement as a multiplier in a multiplication expression, provided thatCaseWhen does not return a null value.
Check this sample:

CREATE TABLE TEST(ID INT,SCORE INT)  
INSERT INTO TEST VALUES  
(1,92),(2,90),(3,89),(4,94),(5,86),(6,83)  
  
SELECT ID  
      ,SUM(CASE WHEN SCORE=100 THEN SCORE ELSE 100 END*CASE WHEN SCORE>90 THEN SCORE ELSE 90 END)  
FROM TEST  
GROUP BY ID  

Best regards,
LiHong