CASE expression ent returning ELSE value for every account even when condition is True-CodePudding

The below query returns two rows for every account with both the 'Active' and 'Inactive' labels even when the CASE expression logic is True.

ex. If an account has 'revenue' greater than 0 and the fiscal_qtr = '2022-Q1' instead of returning just 'Active' it also returns a row with the value 'Inactive'.


SELECT 
geo,
account,
subsegment,
forecast_group,
CASE 
WHEN revenue > 0 and fiscal_qtr = '2022-Q1' then 'Active'
ELSE 'Inactive'
END AS "Active Acct"
FROM rev_planning.ace_global
WHERE
(fiscal_year_num between 2018 and 2021 or fiscal_qtr = '2022-Q1')
and revenue > 0
GROUP BY geo, account, subsegment, forecast_group

I have tried putting the case expression in parenthesis but it still returns two rows for all of the accounts that should just be returning 'Active'

(WHEN revenue > 0 and fiscal_qtr = '2022-Q1' then 'Active' ELSE 'Inactive'END) AS "Active Acct"

Current Output

geo	account	subsegment	forecast group	acitveacct
APAC	brothers neilsen	australia	dbiq	Active
APAC	brothers neilsen	australia	dbiq	Inactive

Expected Output (because brothers neilsen should be "Active" based on the case expression

geo	account	subsegment	forecast group	acitveacct
APAC	brothers neilsen	australia	dbiq	Active

CodePudding user response：

I have tried putting the case expression in parenthesis but it still returns two rows for all of the accounts that should just be returning 'Active'

That's what should happen if the SELECT returns rows for multiple years and quarters. Due to the CASE the current year and quarter (2022-Q1) would be considered 'Active' and prior years would be 'Inactive'.

It sounds like you only want to return the most recent year. Try using ROW_NUMBER() to sort and rank the fiscal quarters for each account. Then use where RowNum = 1 to grab the most recent one. The query below partitions results by geo, account, subsegment, but you can adjust it as needed:

WITH cte AS (
   SELECT * 
          , ROW_NUMBER() OVER(
             PARTITION BY geo, account, subsegment 
             ORDER BY fiscal_year_num DESC, fiscal_qtr DESC
          ) AS RowNum
   FROM   ace_global
   WHERE  revenue > 0
   OR   ( fiscal_year_num BETWEEN 2018 AND 2021
           OR fiscal_qtr = '2022-Q1'
        )
)
SELECT geo
      , account
      , subsegment
      , forecast_group
      , CASE WHEN fiscal_qtr = '2022-Q1' THEN 'Active' ELSE 'Inactive' END AS "Active Acct" 
FROM   cte 
WHERE  RowNum = 1

Sample Data

geo | account     | subsegment | forecast_group | revenue | fiscal_year_num | fiscal_qtr
:-- | :---------- | :--------- | :------------- | ------: | --------------: | :---------
ABC | abc company | australia  | dbiq           |   50000 |            2018 | 2018-Q1   
ABC | abc company | australia  | dbiq           | 1000000 |            2022 | 2022-Q1   
EFG | efg company | australia  | dbiq           |   75000 |            2020 | 2020-Q2   
HIJ | hij company | australia  | dbiq           |  787000 |            2021 | 2021-Q3   
HIJ | hij company | australia  | dbiq           | 2000000 |            2022 | 2022-Q1

Results:

geo | account     | subsegment | forecast_group | Active Acct
:-- | :---------- | :--------- | :------------- | :----------
ABC | abc company | australia  | dbiq           | Active     
EFG | efg company | australia  | dbiq           | Inactive   
HIJ | hij company | australia  | dbiq           | Active

db<>fiddle here