Use CASE in a WHERE clause

Question

I need to use a condition within a filter parameter in a report. Basically the rule is determined by a vachar field that the value of it is’T' or 'P'. I tried a solution like below but it did not work very well.

The rule is, if the field is’T' filter by the Dt_cad field, if the value is 'P' filter by the Dt_output field. Someone knows if this is possible?

Down with my attempt at solution

Thanks for all your help

WHERE   (CASE 
        WHEN NotFis.Tp_Nota='P' then
             NotFis.DT_Emissao between '2018-01-01'   and GETDATE()  
        ELSE NotFis.dt_CAD     between '2018-01-01'   and GETDATE()  
        END)

Answer 1

An alternative solution that does not involve the CASE WHEN is to test Tp_note on WHERE, thus:

WHERE ( 
        (NotFis.Tp_Nota='P' AND (NotFis.DT_Emissao between '2018-01-01' AND GETDATE()))
        OR
        (NotFis.Tp_Nota='T' AND (NotFis.dt_CAD between '2018-01-01' AND GETDATE()))
       )

The CASE WHEN should be part of the logical expression and not "be the logical expression". It would work well with something like this:

WHERE NotFis.DT_Emissao BETWEEN  '2018-01-01' AND 
       (CASE  WHEN NotFis.Tp_Nota='P' THEN GETDATE()
              ELSE NotFis.dt_CAD    GETDATE()-30
        END)

In this example above, it "forms part" of the expression (the final value of the BETWEEN).