Get the inactive records in sqlserver

  • kbhanu15

    SSCarpal Tunnel

    Points: 4407

    HI Team,

    please help to get the expected data.

    my Data like below:

    create table #comapny

    (

    companyid int,

    childname varchar(100),

    activeid int)

    insert into #comapny

    select 1,'aaa',0

    union ALL

    select 1,'bbb',0

    union ALL

    select 1,'ccc',1

    UNion ALL

    select 2,'ddd',0

    union ALL

    select 2,'eee',0

    union ALL

    select 1,'fff',0

    select *from #comapny

    Existing Data:

    companyid childname activeid

    1 aaa 0

    1 bbb 0

    1 ccc 1

    2 ddd 0

    2 eee 0

    1 fff 0

    Expected Data:

    companyid childname activeid

    2 ddd 0

    2 eee 0

    1 fff 0

     

    Thanks

    Bhanu

  • Phil Parkin

    SSC Guru

    Points: 243596

    What are the exact criteria for defining a row as 'Inactive'?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Thom A

    SSC Guru

    Points: 98300

    Why is company fff in the expected results, but not company ccc when the latter has a value of 1 for activeid and the former has a value of 0?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • kbhanu15

    SSCarpal Tunnel

    Points: 4407

    sorry updated the data.

     

    create table #comapny

    (

    companyid int,

    childname varchar(100),

    activeid int)

    insert into #comapny

    select 1,'aaa',0

    union ALL

    select 1,'bbb',0

    union ALL

    select 1,'ccc',1

    UNion ALL

    select 2,'ddd',0

    union ALL

    select 2,'eee',0

    union ALL

    select 2,'fff',0

    select *from #comapny

    --Existing Data:

    companyid childname activeid

    1 aaa 0

    1 bbb 0

    1 ccc 1

    2 ddd 0

    2 eee 0

    2 fff 0

    Expected Data:

    companyid childname activeid

    2 ddd 0

    2 eee 0

    2 fff 0

  • Thom A

    SSC Guru

    Points: 98300

    A couple of total blind guesses:

    SELECT c.companyid,
    c.childname,
    c.activeid
    FROM #comapny c
    WHERE NOT EXISTS (SELECT 1
    FROM #comapny e
    WHERE e.companyid = c.companyid
    AND e.activeid = 1);

    GO

    WITH CTE AS(
    SELECT c.companyid,
    c.childname,
    c.activeid,
    COUNT(CASE WHEN c.activeid > 0 THEN 1 END) OVER (PARTITION BY c.companyid) AS Active
    FROM #comapny c)
    SELECT companyid,
    childname,
    activeid
    FROM CTE
    WHERE Active = 0;

    If not, please do explain your logic and let us know what you've tried.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply