Get the inactive records in sqlserver

Question

Post reply

Get the inactive records in sqlserver

kbhanu15

SSCarpal Tunnel

Points: 4498
More actions
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

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

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

Phil Parkin SSC Guru Points: 247172 More actions · Answer 1

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

Thom A SSC Guru Points: 99143 More actions · Answer 2

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.
Larnu.uk

kbhanu15 SSCarpal Tunnel Points: 4498 More actions · Answer 3

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: 99143 More actions · Answer 4

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.
Larnu.uk

Get the inactive records in sqlserver

Cookies on SQLServerCentral