none
Help in query of comparision

    Question

  • Hi, I have some data and I want to figure it out that which combination (A,B and C column) has not been terminated. Status-'1' is ACTIVE and Status-'2' is TERMINATED. There are rows in the following data which are not terminated. How I can figure it out from query. Please help.

    create table #tmp (A char(10), B char(10), C char(10), edate datetime,status numeric)

    insert into #tmp values ('10','SADR','B','2010-01-01',1)  --This combination record is ACTIVE

    insert into #tmp values ('10','SADR','B','2012-01-01',2) ----This combination record is TERMINATED

    insert into #tmp values ('20','SADR','B','2010-01-01',1) --This combination record is ACTIVE

    insert into #tmp values ('20','SADR','B','2012-01-01',2) ----This combination record is TERMINATED

    insert into #tmp values ('30','SADR','B','2010-01-01',1) --This combination record is ACTIVE

    insert into #tmp values ('30','SADR','B','2012-01-01',2) ----This combination record is TERMINATED

    insert into #tmp values ('40','SADR','B','2010-01-01',1) --This combination record is ACTIVE and no other record for TERM

    insert into #tmp values ('50','SADR','B','2012-01-01',1) --This combination record is ACTIVE and no other record for TERM

    --Expected result from the query

    A      B           C    edate             status

    40    SADR     B    2010-01-01     1

    50    SADR     B    2010-01-01     1

    Friday, February 14, 2014 4:12 AM

Answers

  • create table #tmp (A char(10), B char(10), C char(10), edate datetime,status numeric)
    
    insert into #tmp values ('10','SADR','B','2010-01-01',1)  --This combination record is ACTIVE
    insert into #tmp values ('10','SADR','B','2012-01-01',2) ----This combination record is TERMINATED
    insert into #tmp values ('20','SADR','B','2010-01-01',1) --This combination record is ACTIVE
    insert into #tmp values ('20','SADR','B','2012-01-01',2) ----This combination record is TERMINATED
    insert into #tmp values ('30','SADR','B','2010-01-01',1) --This combination record is ACTIVE
    insert into #tmp values ('30','SADR','B','2012-01-01',2) ----This combination record is TERMINATED
    insert into #tmp values ('40','SADR','B','2010-01-01',1) --This combination record is ACTIVE and no other record for TERM
    insert into #tmp values ('50','SADR','B','2012-01-01',1) --This combination record is ACTIVE and no other record for TERM
    
    ;with mycte as
    (select *, max(status) Over(Partition by A) val from #tmp)
    Select A,B,C, edate from mycte WHERE val=1
    drop table #tmp

    • Marked as answer by Sultana_K Friday, February 14, 2014 4:27 AM
    Friday, February 14, 2014 4:23 AM

All replies

  • create table #tmp (A char(10), B char(10), C char(10), edate datetime,status numeric)
    
    insert into #tmp values ('10','SADR','B','2010-01-01',1)  --This combination record is ACTIVE
    insert into #tmp values ('10','SADR','B','2012-01-01',2) ----This combination record is TERMINATED
    insert into #tmp values ('20','SADR','B','2010-01-01',1) --This combination record is ACTIVE
    insert into #tmp values ('20','SADR','B','2012-01-01',2) ----This combination record is TERMINATED
    insert into #tmp values ('30','SADR','B','2010-01-01',1) --This combination record is ACTIVE
    insert into #tmp values ('30','SADR','B','2012-01-01',2) ----This combination record is TERMINATED
    insert into #tmp values ('40','SADR','B','2010-01-01',1) --This combination record is ACTIVE and no other record for TERM
    insert into #tmp values ('50','SADR','B','2012-01-01',1) --This combination record is ACTIVE and no other record for TERM
    
    ;with mycte as
    (select *, max(status) Over(Partition by A) val from #tmp)
    Select A,B,C, edate from mycte WHERE val=1
    drop table #tmp

    • Marked as answer by Sultana_K Friday, February 14, 2014 4:27 AM
    Friday, February 14, 2014 4:23 AM
  • Thanks a lot.
    Friday, February 14, 2014 4:27 AM