none
CASE Statement in SELECT

    Question

  • declare @Migrationdate datetime,@MigrationIndicator int
    set @Migrationdate='6/13/2012'
    set @MigrationIndicator=0 

    declare @temp table (accountid int , accountdate datetime)
    insert into @temp (accountid, accountdate)
    select 123,'6/12/2012' union
    select 124,'6/13/2012' union
    select 125,'6/14/2012'

    (The @migrationindicator will be 0 for pre migration and 1 for post migration).

    I want to select rows from table, in this criteria.  Also, instead of using an IF, is it possible to do a CASE statement in the WHERE clause to achieve this.  Because, i want to hook up this piece to an existing stored procedure.

    if @migrationindicator=0 then
    select * from tablename where field1<=@migrationdate
    if @migrationindicator=1 then
    select * from tablename where field1>@migrationdate

    Thanks.


    NSG12

    Thursday, June 20, 2013 9:44 PM

Answers

  • Not efficient, but the question was to achieve this using CASE :)

    declare @Migrationdate datetime,@MigrationIndicator int
    set @Migrationdate='6/13/2012'
    set @MigrationIndicator=0
    
    declare @temp table (accountid int , accountdate datetime)
    insert into @temp (accountid, accountdate)
    select 123,'6/12/2012' union
    select 124,'6/13/2012' union
    select 125,'6/14/2012'
    
    select * from @temp
    where 
    	accountdate <= CASE WHEN @migrationindicator = 0 THEN @migrationdate ELSE accountdate END
    	AND
    	accountdate > CASE WHEN @migrationindicator = 1 THEN @migrationdate ELSE 0 END



    Alexander Karavaev, PMP, MCITP, MCTS
    IT Consulting in Sydney (Australia)

    Thursday, June 20, 2013 10:18 PM

All replies

  • Alternate Query of your scenario without IF/Case clauses

     select * from @temp
     where (@MigrationIndicator=1 and accountdate<=@Migrationdate)
     or (@MigrationIndicator=0) and accountdate>@Migrationdate

    Cheers,


    Amar Deep Singh


    • Edited by Amar Deep Singh Thursday, June 20, 2013 10:48 PM Previous answer was for the Case clause
    • Proposed as answer by Amar Deep Singh Thursday, June 20, 2013 10:49 PM
    Thursday, June 20, 2013 9:58 PM
  • The following is gives you the same result (as long as @MigrationIndicator is NOT NULL) but having an IF statement is better for performance, I think. But just test it.

    /* Your code slightly changed */
    IF @MigrationIndicator = 0 THEN
    BEGIN
        SELECT <Never use *, but spell out the column list please>
        FROM <TableName>
        WHERE <Field1> <= @MigrationDate;
    END
    ELSE
    BEGIN
        SELECT <Never use *, but spell out the column list please>
        FROM <TableName>
        WHERE <Field1> > @MigrationDate;
    END;
    
    /* An alternative */
    SELECT <Never use *, but spell out the column list please>
    FROM <TableName>
    WHERE ((@MigrationIndicator = 0) AND (<Field1> <= @MigrationDate))
    OR ((@MigrationIndicator = 1) AND (<Field1> > @MigrationDate));
    

    Thursday, June 20, 2013 10:00 PM
  • Hi,

    I don't like this kind of conditions, but, were we go:

    declare @Migrationdate datetime,@MigrationIndicator int
    set @Migrationdate='20120613'
    set @MigrationIndicator=0;
    
    declare @temp table (accountid int , accountdate datetime)
    insert into @temp (accountid, accountdate)
    select 123,'20120612' union
    select 124,'20120613' union
    select 125,'20120614'
    
    SELECT * FROM @temp WHERE 
    	1 = CASE WHEN @MigrationIndicator = 0 AND accountdate <= @MigrationDate THEN 1 ELSE 0 END
    	OR
    	1 = CASE WHEN @MigrationIndicator = 1 AND accountdate > @MigrationDate THEN 1 ELSE 0 END

    PS: I had to change date time formats because I'm in another country with a different date time format.

    Hope this helps,


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Pedro Martins
    Portugal
    https://www.linkedin.com/in/rechousa

    Thursday, June 20, 2013 10:13 PM
  • Not efficient, but the question was to achieve this using CASE :)

    declare @Migrationdate datetime,@MigrationIndicator int
    set @Migrationdate='6/13/2012'
    set @MigrationIndicator=0
    
    declare @temp table (accountid int , accountdate datetime)
    insert into @temp (accountid, accountdate)
    select 123,'6/12/2012' union
    select 124,'6/13/2012' union
    select 125,'6/14/2012'
    
    select * from @temp
    where 
    	accountdate <= CASE WHEN @migrationindicator = 0 THEN @migrationdate ELSE accountdate END
    	AND
    	accountdate > CASE WHEN @migrationindicator = 1 THEN @migrationdate ELSE 0 END



    Alexander Karavaev, PMP, MCITP, MCTS
    IT Consulting in Sydney (Australia)

    Thursday, June 20, 2013 10:18 PM
  • Did you know that we have the DATE data type? Did you know that the only display format in ANSI/ISO Standard SQL is ISO-8601? 

    DECLARE @migration_date DATE,
     @non_rdbms_migration_flg INTEGER; -- a flag about a date, not the date itself??

    SET @migration_date = '2012-06-13';
    SET @non_rdbms_migration_flg = 0;

    We do not post temp tables; we need to see the keys, constraints, DRI actions, etc. We also do not use assembly language flags in RDBMS! But if you get stuck with non-RDBMS code, then give it a name that warns the programmers who maintain it that it is garbage. 

    CREATE TABLE Accounts
    (account_id INTEGER NOT NULL PRIMARY KEY,
     account_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);

    Please learn ANSI/ISO syntax for insertion: 

    INSERT INTO Accounts
    VALUES
    (123, '2012-06-12'),
    (124, '2012-06-13'),
    (125, '2012-06-14');

    >> (The @non_rdbms_migration_flg will be 0 for pre migration and 1 for post migration). <<

    Ugh! This is the worst way to write SQL. Why not have the date of the migration and preserve information? That way this query is always right. 

    SELECT *, 
           CASE WHEN migration_date < CAST(CURRENT_TIMESTAMP AS DATE)
                THEM 'migrated' ELSE 'not migrated' END
           as current_migration_status
      FROM Foobar; 

    To answer my own question, because you still think in procedural code with IF-THEN-ELSE control flow logic and not SQL yet. You even confused columns with fields! You are still writing BASIC or COBOL, jut doing it with T-SQL dialect that looks the most like your old language.  

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, June 21, 2013 3:12 AM
  • Thank you all.  Alexander's was exactly the one i was looking for.  Thank you all for your time on this.


    NSG12

    Friday, June 21, 2013 5:08 PM
  • SELECT * FROM Table WHERE Field1 <= MigrationDate AND @MigrationIndicator = 0

    UNION

    SELECT * FROM Table WHERE Field1 > MigrationDate AND @MigrationIndicator = 1


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, June 21, 2013 5:15 PM
  • declare @Migrationdate datetime,@MigrationIndicator int
    set @Migrationdate='6/13/2012'
    set @MigrationIndicator=0  
    declare @temp table (accountid int , accountdate datetime)
    insert into @temp (accountid, accountdate)
    select 123,'6/12/2012' union
    select 124,'6/13/2012' union
    select 125,'6/14/2012'
    select accountid, accountdate, convert(date, @Migrationdate, 102)
    		, case when accountdate <=convert(date, @Migrationdate, 102) then 0 else 1 
    			end indicator 
    from @temp 


    Best Luck, Shenoy

    Friday, June 21, 2013 5:31 PM