locked
CASE in a WHERE Clause RRS feed

  • Question

  • User1536465747 posted

    I need to add an additional where clause in the query but it should depend on the value of the parameter e.g.

    AND
        CASE @Period
             WHEN 1 THEN DueDate BETWEEN DATEADD(day, -30, GETDATE()) AND GETDATE()
    	 WHEN 2 THEN DueDate BETWEEN DATEADD(day, -60, GETDATE()) AND DATEADD(day, -30, GETDATE())
             WHEN NULL THEN -- do nothing
         END

    Obviously that this is not working (pseudo code)

    Thank you

    Wednesday, August 1, 2018 10:51 AM

Answers

  • User-2146987983 posted
    CREATE TABLE TAB (
    	DueDate DATETIME,
    	Balance INT
    )
    
    DECLARE @dt DATETIME = GETDATE()
    DECLARE @Period INT = 2
    
    INSERT INTO TAB
    SELECT DATEADD(day, -25, @dt), 100 UNION ALL
    SELECT DATEADD(day, -45, @dt), 200
    
    SELECT * FROM TAB
    WHERE 
        1 = CASE 
    			WHEN @Period = 1 AND DueDate BETWEEN DATEADD(day, -30, GETDATE()) AND GETDATE() THEN 1 
    			WHEN @Period = 2 AND DueDate BETWEEN DATEADD(day, -60, GETDATE()) AND DATEADD(day, -30, GETDATE()) THEN 1
    			WHEN @Period IS NULL THEN 1
    			ELSE 0
         END
    
    DROP TABLE TAB

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 1, 2018 11:07 AM
  • User77042963 posted

    You don't need a Case expression in your where clause:

     SELECT * FROM TAB
    WHERE 
       ( @Period = 1 AND DueDate BETWEEN DATEADD(day, -30, GETDATE()) AND GETDATE()) or
    	( @Period = 2 AND DueDate BETWEEN DATEADD(day, -60, GETDATE()) AND DATEADD(day, -30, GETDATE()) ) Or 
    	  @Period IS NULL 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 1, 2018 2:35 PM

All replies

  • User-2146987983 posted
    CREATE TABLE TAB (
    	DueDate DATETIME,
    	Balance INT
    )
    
    DECLARE @dt DATETIME = GETDATE()
    DECLARE @Period INT = 2
    
    INSERT INTO TAB
    SELECT DATEADD(day, -25, @dt), 100 UNION ALL
    SELECT DATEADD(day, -45, @dt), 200
    
    SELECT * FROM TAB
    WHERE 
        1 = CASE 
    			WHEN @Period = 1 AND DueDate BETWEEN DATEADD(day, -30, GETDATE()) AND GETDATE() THEN 1 
    			WHEN @Period = 2 AND DueDate BETWEEN DATEADD(day, -60, GETDATE()) AND DATEADD(day, -30, GETDATE()) THEN 1
    			WHEN @Period IS NULL THEN 1
    			ELSE 0
         END
    
    DROP TABLE TAB

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 1, 2018 11:07 AM
  • User1536465747 posted

    Seems to be working fine. Btw, what is the name of this trick/approach when the CASE is "practically" not part of the expression so you use 1 and 0 ???

    Thank you 

    P.S. Is it maybe a NESTED <g class="gr_ gr_37 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="37" data-gr-id="37">CASE ?</g>

    Wednesday, August 1, 2018 11:16 AM
  • User-2146987983 posted

    Seems to be working fine. Btw, what is the name of this trick/approach when the CASE is "practically" not part of the expression so you use 1 and 0 ???

    It doesn't have a name. 

    It's like SELECT * FROM TAB WHERE 1 = 1, and you get all rows from the table. I just used CASE statement to decide when to return 1 or 0, so you get desired rows.

    P.S. Is it maybe a NESTED CASE ?

    No, It's not.

    Wednesday, August 1, 2018 11:55 AM
  • User77042963 posted

    You don't need a Case expression in your where clause:

     SELECT * FROM TAB
    WHERE 
       ( @Period = 1 AND DueDate BETWEEN DATEADD(day, -30, GETDATE()) AND GETDATE()) or
    	( @Period = 2 AND DueDate BETWEEN DATEADD(day, -60, GETDATE()) AND DATEADD(day, -30, GETDATE()) ) Or 
    	  @Period IS NULL 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 1, 2018 2:35 PM