Reputation: 509

CASE in WHERE clause in T-SQL

Trying to use CASE for conditional parameter

I've already posted this before; but since I couldn't achieve the correct result, posting it again with different approach.

I have a complex code, where I only added a few elements, highlighted in yellow

My goal is:

When @Depreciation = 0
Need to display the values - without "Depreciation" (where DepreciationAcc =0)
When @Depreciation = 1
Need to display the values - with "Depreciation" (where DepreciationAcc is both 0 OR 1)

It works, but selects incorrect number of rows.

This is the part where I use CASE within WHERE:

 WHERE ( 
  (CASE WHEN (NOT (acct.[GLAccountCode] IN (N'9040',N'9047',N'4130')) AND 
  (@Depreciation = 0))THEN 0 ELSE trans.Amount END)=0 .............

Below is the full code. Part I am trying to modify - marked with /****/: (Mainly after the first WHERE)

DECLARE @FYMonthBOP date = '2017-07-01'
DECLARE @FYMonthEOP date = '2018-03-31'
DECLARE @GLProgramGroupsWHID int = -1
DECLARE @GLProgramsWHID_Groups int = -1
DECLARE @GLProgramGroupSetsWHID int = -1
DECLARE @Depreciation bit = 1          /****/

SELECT        
  gl0.GLGroupLevel0Name
, gl0.Ordinal AS Ordinal0
, [Dimension].GLGroupLevel1.GLGroupLevel1Name
, [Dimension].GLGroupLevel1.Ordinal AS Ordinal1
, gl2.GLGroupLevel2Name
, gl2.Ordinal AS Ordinal2
, acct.GLAccountCode
, acct.GLAccountName
         , pgm.GLProgramCode + N' ' + pgm.GLProgramName AS Program
, setname.[WHID] AS [WHIDGroupSetName]
         , grp.GroupName 
, grp.WHID AS WHIDGroupName
, grp.Ordinal AS OrdinalGroupName
, d.FYQuarterLabel AS FQ
 -- , trans.Amount AS Amount
 -- , CASE WHEN     ((acct.[GLAccountCode] IN (N'9040',N'9047',N'4130')) 
  AND (@Depreciation = 0)) THEN 0 ELSE trans.Amount END AS [Amount] 
, pgm.[WHID] AS WHIDProgram
, pgm.Ordinal AS OrdinalProgram
, d.FYMonthNumber
, d.FYMonthShortLabel
, d.FYQuarterNumber
, d.FYNumber
, d.FYLabel
, d.YearMonthSort
, IIF(EOMonth(mm.DateThru) < @FYMonthEOP, EOMonth(mm.DateThru), 
       @FYMonthEOP) AS [DateThru]
, IIF((acct.[GLAccountCode]) IN (N'9040',N'9047',N'4130'), 1, 0) AS 
      [DepreciationAcct]

 FROM            
(([Dimension].GLGroupLevel2 gl2 WITH (NoLock)
INNER JOIN [Dimension].GLAccount acct WITH (NoLock) ON gl2.[WHID] = 
                                     acct.GLGroupLevel2FK 
INNER JOIN [Dimension].GLGroupLevel0 gl0 WITH (NoLock)
INNER JOIN [Dimension].GLGroupLevel1 WITH (NoLock) ON gl0.[WHID] = 
               [Dimension].GLGroupLevel1.GLGroupLevel0FK ON    
               GLGroupLevel1FK = [Dimension].GLGroupLevel1.[WHID] 
INNER JOIN Fact.GLTransactionsMonthly trans WITH (NoLock) ON acct. 
                   [WHID] = trans.GLAccountFK 
INNER JOIN [Dimension].GLProgram pgm WITH (NoLock) ON trans. 
               [GLProgramFK] = pgm.[WHID] AND trans.[GLProgramFK] = 
               pgm.[WHID] AND trans.[GLProgramFK] = pgm.[WHID] 
INNER JOIN [Dimension].[Date] d WITH (NoLock) ON trans.EffectiveMonth = 
               d.[Date] AND trans.EffectiveMonth = d.[Date] 

                AND trans.EffectiveMonth = d.[Date] 
INNER JOIN [Reports].GLProgramGroupMapping map ON map.GLProgramFK = 
                         pgm.[WHID]
INNER JOIN [Reports].GLProgramGroup grp ON grp.WHID = 
                         map.GLProgramGroupFK
INNER JOIN [Reports].GLProgramGroupSet setname ON setname.WHID = 
                         grp.GLProgramGroupSetFK))

CROSS APPLY (
    SELECT MAX(t.EffectiveMonth) AS [DateThru] 
    FROM Fact.GLTransactionsMonthly t WITH (NoLock) 
) mm

  WHERE ( 
     (CASE WHEN (NOT (acct.[GLAccountCode] IN (N'9040',N'9047',N'4130')) 
                 AND (@Depreciation = 0)) 
            THEN 0 ELSE trans.Amount END)=0 AND    /****/

     (trans.EffectiveMonth BETWEEN @FYMonthBOP AND @FYMonthEOP)
AND ((setname.WHID IN (@GLProgramGroupSetsWHID)) OR (-1 IN 
         (@GLProgramGroupSetsWHID)))
AND ((grp.WHID IN (@GLProgramGroupsWHID)) OR (-1 IN 
         (@GLProgramGroupsWHID)))
AND ((pgm.[WHID] IN (@GLProgramsWHID_Groups)) OR (-1 IN 
         (@GLProgramsWHID_Groups)))

AND (
    (pgm.[WHID] IN (
    SELECT DISTINCT      
         f.[GLProgramFK]

    FROM            
        Fact.GLTransactionsMonthly f
        INNER JOIN [Dimension].[GLAccount] acct ON f. 
                    [GLAccountFK] = acct.[WHID]

    WHERE 
        (f.EffectiveMonth BETWEEN @FYMonthBOP AND @FYMonthEOP)
        AND (acct.[GLAccountCode] BETWEEN '5000' AND '9999')

    GROUP BY
         f.[GLProgramFK]

    HAVING 
        (SUM(f.[Amount]) <> 0))
    )
OR
    (pgm.[WHID] IN (
    SELECT DISTINCT      
         f.[GLProgramFK]

    FROM            
        Fact.GLTransactionsMonthly f
        INNER JOIN [Dimension].[GLAccount] acct ON f. 
                    [GLAccountFK] = acct.[WHID]

    WHERE 
        (f.EffectiveMonth BETWEEN @FYMonthBOP AND @FYMonthEOP)
        AND (acct.[GLAccountCode] BETWEEN '4000' AND '4999')

    GROUP BY
         f.[GLProgramFK]

    HAVING 
        (SUM(f.[Amount]) <> 0))
    )
OR 
    (pgm.[GLProgramCode] IN ('549'))

)   
    )

Upvotes: 1

Answers (2)

Hell-1931

Reputation: 509

Just recently learned about the right approach on this my problem -

CASE WHEN   (acct.[GLAccountCode] IN (N'9040',N'9047',N'4130')) AND 
            (@Depreciation = 0) THEN 0 ELSE 1 END)=1

No need to include trans.Amount field, and the value has to be equal 1, not 0 That way it'll show all values

Upvotes: 1

Paul Maxwell

Reputation: 35603

Consider the basic structure of your predicate:

(CASE 
    WHEN ... AND @Depreciation = 0 THEN 0 
    ELSE trans.Amount END 
) = 0

Everything coming out of the case expression is evaluated by the equal 0 so if trans.Amount <> 0 then it will not survive into the result

EDIT

I think you might want this:

WHERE ( 
         (@Depreciation = 0 and  [DepreciationAcct] = 0)
       OR
         (@Depreciation = 1 and  [DepreciationAcct] IN (0,1))
      )

Upvotes: 0

CASE in WHERE clause in T-SQL

Answers (2)

Related Questions