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 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.
See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.
August 19, 2019 at 2:00 pm
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.
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.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply