locked
Sql Query RRS feed

  • Question

  • Emp table:

    id name country JoiningYearMonth
    1  a         Ind      201105
    2  b         Ind      201111
    3  c          US       201103
    4  d          us       201112 
    5  e          Ind     201104

    Q: I want to restrict the rows whose country is Ind and Joiningyearmonth <=201105.
    Is there any alternative query other than below one?

    select * from emp
    except
    select * from emp
    where country='ind' and joiningyearmonth<=201105

    Monday, February 13, 2012 6:28 AM

Answers

  • select * from emp
    except
    select * from emp
    where country='ind' and joiningyearmonth<=201105

    is equal  to (provivded no nulls allowed)

    select * from emp
    where country<>'ind' OR  joiningyearmonth>201105


    Serg

    • Proposed as answer by Iric Wen Tuesday, February 14, 2012 9:23 AM
    • Marked as answer by Iric Wen Wednesday, February 22, 2012 1:11 AM
    Monday, February 13, 2012 7:41 AM
  • SELECT *
    FROM   emp
    WHERE  country <> 'ind'
           AND joiningyearmonth <= 201105

    Test this. Is theresomething else you are looking.

    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote helpful topics and Mark answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    • Proposed as answer by Elmozamil Elamir Monday, February 13, 2012 6:49 AM
    • Marked as answer by Iric Wen Wednesday, February 22, 2012 1:11 AM
    Monday, February 13, 2012 6:37 AM
  • create table #Emp (

    Id int,
    Name varchar(5),
    Country varchar(5),
    JoiningYearMonth int
    )


    insert into #Emp (
    Id,
    Name,
    Country,
    JoiningYearMonth
    )
    select 1, 'a', 'Ind', 201105 union all
    select 2, 'b', 'Ind', 201111 union all
    select 3, 'c', 'US', 201103 union all
    select 4, 'd', 'US', 201112 union all
    select 5, 'e', 'Ind', 201104


     select * from #Emp where Country = 'us'
     union
     select * from #Emp where Country = 'ind' and JoiningYearMonth > 201105

    you can use this query as an alternative which produces same result as yours and also little bit faster.

    your query is costing 0.0183403

    while with union it is costing 0.0179518


     
    • Proposed as answer by Ronit Roy Saturday, February 18, 2012 10:57 PM
    • Edited by Neal Khatri Saturday, February 18, 2012 11:10 PM
    • Marked as answer by Iric Wen Wednesday, February 22, 2012 1:11 AM
    Saturday, February 18, 2012 10:55 PM

All replies

  • SELECT *
    FROM   emp
    WHERE  country <> 'ind'
           AND joiningyearmonth <= 201105

    Test this. Is theresomething else you are looking.

    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote helpful topics and Mark answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    • Proposed as answer by Elmozamil Elamir Monday, February 13, 2012 6:49 AM
    • Marked as answer by Iric Wen Wednesday, February 22, 2012 1:11 AM
    Monday, February 13, 2012 6:37 AM
  • I want to restrict only Ind employees whose joiningyearmonth<=201105.
    Monday, February 13, 2012 7:00 AM
  • Hello Gangadhar,

    Your own query would work fine. Just remove the first select and the except clause. So the query would be

    select * from emp
    where country='ind' and Convert(int,joiningyearmonth) < 201105

    Murali


    • Edited by Murali_CHN Monday, February 13, 2012 7:30 AM Updated the joiningyearmonth condition
    Monday, February 13, 2012 7:07 AM
  • declare @temp1 table

    (id int identity(1,1) not null,

    name varchar(50) null,

    country varchar(20) null,

    JoiningYearMonth varchar(15) not null)

    insert into @temp1 (name,country,JoiningYearMonth) values('a','Ind','201105')

    insert into @temp1 (name,country,JoiningYearMonth) values('b','Ind','201111')

    insert into @temp1 (name,country,JoiningYearMonth) values('c','US','201103')

    insert into @temp1 (name,country,JoiningYearMonth) values('d','us','201112')

    insert into @temp1 (name,country,JoiningYearMonth) values('e','Ind','201104')

    select * from @temp1

    select * from @temp1

    where country = 'Ind'

    and CONVERT(int,JoiningYearMonth) <= 201105

    Try this


    Sharath



    • Edited by Sharath048 Monday, February 13, 2012 10:41 AM
    Monday, February 13, 2012 7:23 AM
  • select * from emp
    except
    select * from emp
    where country='ind' and joiningyearmonth<=201105

    is equal  to (provivded no nulls allowed)

    select * from emp
    where country<>'ind' OR  joiningyearmonth>201105


    Serg

    • Proposed as answer by Iric Wen Tuesday, February 14, 2012 9:23 AM
    • Marked as answer by Iric Wen Wednesday, February 22, 2012 1:11 AM
    Monday, February 13, 2012 7:41 AM
  • Any progress?

    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

    Saturday, February 18, 2012 4:07 PM
  • create table #Emp (

    Id int,
    Name varchar(5),
    Country varchar(5),
    JoiningYearMonth int
    )


    insert into #Emp (
    Id,
    Name,
    Country,
    JoiningYearMonth
    )
    select 1, 'a', 'Ind', 201105 union all
    select 2, 'b', 'Ind', 201111 union all
    select 3, 'c', 'US', 201103 union all
    select 4, 'd', 'US', 201112 union all
    select 5, 'e', 'Ind', 201104


     select * from #Emp where Country = 'us'
     union
     select * from #Emp where Country = 'ind' and JoiningYearMonth > 201105

    you can use this query as an alternative which produces same result as yours and also little bit faster.

    your query is costing 0.0183403

    while with union it is costing 0.0179518


     
    • Proposed as answer by Ronit Roy Saturday, February 18, 2012 10:57 PM
    • Edited by Neal Khatri Saturday, February 18, 2012 11:10 PM
    • Marked as answer by Iric Wen Wednesday, February 22, 2012 1:11 AM
    Saturday, February 18, 2012 10:55 PM
  • I want to restrict only Ind employees whose joiningyearmonth<=201105.

    To be clear on exactly what you want returned by the query, is it:

    1. Only rows with country = ind and  joiningyearmonth <= 201105
    2. All rows except where country = ind and  joiningyearmonth <= 201105
    3. all rows where country <> Ind and all rows where country = Ind  and  joiningyearmonth <= 201105

    Dave Frommer - BI Architect - Independent

    Saturday, February 18, 2012 11:05 PM