August 23, 2022 at 4:25 pm
Is it possible use case more than once in an expression?
I have a query tha calculates the cost of insurence.
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)
August 23, 2022 at 5:40 pm
The best way to answer questions like this is to try it. If it works, great! If it doesn't work, then you can post here to figure out why it doesn't work.
CASE expressions are simply scalar expressions, and can be used pretty much anywhere that you can have a scalar expression. There may be some interactions with other operators--such as GROUP BY--that may cause confusion with exactly how to specify the conditions.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 23, 2022 at 5:42 pm
i get an error that why i was asking help.
August 23, 2022 at 6:00 pm
i get an error that why i was asking help.
What is the error?
August 23, 2022 at 6:05 pm
Is it possible use case more than once in an expression? I have a query tha calculates the cost of insurence. 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)
Here you seem to be missing ISNULL
—–ISNULL(SUM(ROUND(mnyInsurance,2)*CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END),0) Insurance
And in your WHERE intProp IN (@Props) clause, does intProp belong to tblUBMInsuranceShare OR tblUBMReport?
August 23, 2022 at 6:08 pm
My question how to add that expression to my query.
ISNULL(SUM(ROUND(mnyInsurance,2)*CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END),0) Insurance
August 23, 2022 at 6:26 pm
I was able to solve the issue by adding "*SUM(CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END)"
August 23, 2022 at 6:26 pm
i get an error that why i was asking help.
We're not mind readers. You got an error message, but failed to mention that in your original post. How are we supposed to know that you got an error message if you don't tell us?
We're not mind readers. You got an error message, but failed to mention the text of the error message. How are we supposed to help you troubleshoot an error message if you don't tell us what it is?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 23, 2022 at 6:37 pm
I was able to resolve my issue.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply