August 19, 2019 at 1:27 pm
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
August 19, 2019 at 1:51 pm
What are the exact criteria for defining a row as 'Inactive'?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 19, 2019 at 2:10 pm
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
August 19, 2019 at 2:17 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply