Get the inactive records in sqlserver

• kbhanu15

  SSCarpal Tunnel

  Points: 4407

  August 19, 2019 at 1:27 pm

  #3672310

  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

  August 19, 2019 at 1:51 pm

  #3672317

  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

  August 19, 2019 at 2:00 pm

  #3672319

  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

  August 19, 2019 at 2:10 pm

  #3672320

  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

  August 19, 2019 at 2:17 pm

  #3672321

  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.