locked
query RRS feed

  • Question

  • hi

    select * from tabA where Quarter in (Replace((CASE

                                                                        WHEN @quarter = 'Q1' THEN 'Q1'

                                                                        WHEN @quarter = 'Q2' THEN 'Q1,Q2'

                                                                        WHEN @quarter = 'Q3' THEN 'Q1,Q2,Q3'

                                                                       END),',',''','''))

    i would like to get the records depending on the variable @quarter..

    The above query does  not give any results. Can some one explain y this does not work?

    what should i change in the query to get executed?

    Please dont use any variables.

    Tuesday, October 25, 2011 5:20 AM

Answers

  • Try something like this:

    select * from tabA 
    where Quarter between 'Q1' and @quarter
    
    

     

    • Edited by Kent Waldrop Tuesday, October 25, 2011 1:13 PM
    • Proposed as answer by Kent Waldrop Monday, October 31, 2011 2:05 PM
    • Marked as answer by KJian_ Tuesday, November 1, 2011 7:47 AM
    Tuesday, October 25, 2011 12:36 PM

All replies

  • If you think about your above query and what it's actually checking you'll see where you're going wrong. For example if @quarter is Q3, it's checking if Quarter is in the string "Q1','Q2','Q3". It's not checking if the Quarter is Q1 or Q2 or Q3.

    You could use the following T-SQL. This will discard any records without a valid quarter.

    SELECT
    	*
    FROM
    	TabA
    WHERE
    	CASE
    		WHEN ISNUMERIC(RIGHT(Quarter, 1)) = 1 THEN CAST(RIGHT(Quarter, 1) AS int)
    		ELSE 5
    	END
    	<=
    	CAST(RIGHT(@quarter, 1) AS int)
    



    David Stewart | My Microsoft BI Blog | @dstewartbi
    Tuesday, October 25, 2011 5:46 AM
  • Hi Sudeep,
    You can't write IN statement like this.
    You will not get desired output.

    You raised this thread couple of days back
    http://social.technet.microsoft.com/Forums/en-US/sqlgetstarted/thread/bc8a7a0b-1980-4481-a2df-6a5fde38f362



    Shatrughna.
    Tuesday, October 25, 2011 8:14 AM
  • Try something like this:

    select * from tabA 
    where Quarter between 'Q1' and @quarter
    
    

     

    • Edited by Kent Waldrop Tuesday, October 25, 2011 1:13 PM
    • Proposed as answer by Kent Waldrop Monday, October 31, 2011 2:05 PM
    • Marked as answer by KJian_ Tuesday, November 1, 2011 7:47 AM
    Tuesday, October 25, 2011 12:36 PM