I work on sql server 2012 I face issue I can't get partfamilyid that have familystatus active only or active and Null.
so if partfamily have familystatus active then it is ok i need it as 5200.
if partfamily have familystatus active and NULL then it is ok i need it as 3050.
SO partfamilyid 5200 has familystatus Active so it is ok.
and partfamilyid 3050 has familystatus Active and NULL so it is ok.
any thing exception active only or active and null I don't need it.
create table #partsFamily
(
PartFamilyId int,
FamilyStatus nvarchar(50),
CountStatus int,
FamilyStatusStuff nvarchar(2000)
)
insert into #partsFamily(PartFamilyId,FamilyStatus,CountStatusParts,FamilyStatusStuff)
values
(3000,'Obselete',5,NULL),
(3050,'Active',5,NULL),
(3050,NULL,2,NULL),
(3090,'Active',3,NULL),
(3090,'Obselete',4,NULL),
(4050,NULL,8,NULL),
(5200,'Active',2,NULL),
(5600,'Obselete',4,NULL),
(5600,'Pending',5,NULL)
Expected Result as following :
- PartFamilyId FamilyStatus
- 3050 Active|NULL
- 5200 Active