I have searched this site extensively but cannot find a solution.
Here is the example of my query:
SELECT
ActivityID,
Hours = (CASE
WHEN ActivityTypeID <> 2 THEN
FieldName = (Some Aggregate Sub Query),
FieldName2 = (Some other aggregate sub query)
WHEN ActivityTypeID = 2 THEN
FieldName = (Some Aggregate Sub Query with diff result),
FieldName2 = (Some Other Aggregate Sub Query with diff result)
END)
obviously I'm leaving out a lot of the query, I just wanted to see if it's possible.
I know I probably could just do the "CASE" twice but figured I would ask...
The problem is that the CASE
statement won't work in the way you're trying to use it. You can only use it to switch the value of one field in a query. If I understand what you're trying to do, you might need this:
SELECT
ActivityID,
FieldName = CASE
WHEN ActivityTypeID <> 2 THEN
(Some Aggregate Sub Query)
ELSE
(Some Aggregate Sub Query with diff result)
END,
FieldName2 = CASE
WHEN ActivityTypeID <> 2 THEN
(Some Aggregate Sub Query)
ELSE
(Some Aggregate Sub Query with diff result)
END