CASE statement inside OR inside AND in SQL

Question

RES as 
(
SELECT code , payement,
sum(A.ALLPAYMENT) as ACTUAL_PAYMENT,
A.NAME
FROM FINANCES A 
WHERE payement= '6396'   
and (ENDDATE>=  CURRENT_TIMESTAMP) 
and (BILLREFRENCE<> '' or 

(case when (    CONVERT (int, (select BILLTIME from MYCALENDAR CL
                                where CL.code = A.code and CL.NAME= 
A.NAME
                              )  
                         )
               > CONVERT (INT, REPLACE( CONVERT(VARCHAR(8), GETDATE(), 108), 
               ':', '' ) )  
            ) then LEVEL in ('300', '100', '404')
              else
                LEVEL in ('300','404')
)


 )
GROUP BY code, payement, A.NAME)

the OR condition needs to be either " OR LEVEL in ('300', '100', '404') " or " OR LEVEL in ('300', '404') " if the current systemtime is greater than the BILLTIME it's LEVEL in ('300','404') if not, it's LEVEL in ('300', '100', '404')

PS : BILLTIME is in the format of military time "130000", hence the conversions.

Answer 1

Since the result of an expression inside WHERE clause is boolean, you can always rewrite it as a boolean expression without using a CASE expression. In some cases, the logic can be simplified, too.

Since the IN list from the first case differs from the IN list in the second case by only one member, '100', you can restructure your condition to avoid IN list inside a CASE expression:

SELECT
    ...
WHERE LEVEL in ('300','404')
   OR LEVEL = '100' AND CONVERT(...) > CONVERT(...)